JDBC SELECT Query Example

In previous posts, we have learned about types of JDBC drivers and the how to make database connection using JDBC. Let’s move forward and start interacting with database. First example I am picking up is SQL SELECT queries.

SQL SELECT query are executed to fetch data stored in relational databases. It requires following steps:

1) Make a database connection
2) Execute the SQL Query
3) Fetch the data from result set

Pre-requisites include setting up a database schema and creating a table at least.

CREATE SCHEMA 'JDBCDemo' ;

CREATE  TABLE 'JDBCDemo'.'EMPLOYEE' 
(
  'ID' INT NOT NULL DEFAULT 0 ,
  'FIRST_NAME' VARCHAR(100) NOT NULL ,
  'LAST_NAME' VARCHAR(100) NULL ,
  'STAT_CD' TINYINT NOT NULL DEFAULT 0 
);

Let’s write above steps in code:

1) Make a database connection

Though we have already learned about it in making JDBC connection, lets recap with this simple code snippet.

	Class.forName("com.mysql.jdbc.Driver");
	connection = DriverManager
		.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");

2) Execute the SQL Query

This is the main step and core part in the post. It requires creating a Statement object and then using it’s executeQuery() method.

Statement selectStmt = connection.createStatement();
ResultSet rs = selectStmt
	.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10");

3) Fetch the data from result set

You can use various getXXX() methods available in ResultSet. But if you want to make it generic then use getString() method and parse the data as and when needed.

ResultSet rs = selectStmt
	.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10");
while(rs.next())
{
	System.out.println(rs.getString(1));	//First Column
	System.out.println(rs.getString(2));	//Second Column
	System.out.println(rs.getString(3));	//Third Column
	System.out.println(rs.getString(4));	//Fourth Column
}

Let’s see the whole code in working.

package com.howtodoinjava.jdbc.demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectDataDemo {
	public static void main(String[] args) {
		Connection connection = null;
		Statement insertStmt = null;
		Statement selectStmt = null;
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/JDBCDemo", "root", "password");
			
			/*insertStmt = connection.createStatement();
			insertStmt.execute("INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME,STAT_CD) VALUES (1,'Lokesh','Gupta',5)");
			insertStmt.execute("INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME,STAT_CD) VALUES (2,'howtodoinjava','com',5)");*/
			
			selectStmt = connection.createStatement();
			ResultSet rs = selectStmt.executeQuery("SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10");
			while(rs.next())
			{
				System.out.println(rs.getString(1));	//First Column
				System.out.println(rs.getString(2));	//Second Column
				System.out.println(rs.getString(3));	//Third Column
				System.out.println(rs.getString(4));	//Fourth Column
			}
		} 
		catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				selectStmt.close();
				insertStmt.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}

Output:

1
Lokesh
Gupta
5
2
howtodoinjava
com
5

That’s all in this post. Drop me a comment if something needs explanation.

Happy Leaning !!