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.

JDBC-Icon

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 !!

Was this post helpful?

Join 8000+ Awesome Developers, Like YOU!

7 thoughts on “JDBC SELECT Query Example”

  1. At what point a transaction will start?
    Like when Statement stmt = connection.createStatement(); // Will this will start a transaction or
    when ResultSet rs = selectStmt
    .executeQuery(“SELECT ID,FIRST_NAME,LAST_NAME,STAT_CD FROM EMPLOYEE WHERE ID <= 10");

    I know about the setAutoCommit(true) and it will commit the transaction after every executeUpdate method.
    But I want to know at exact what point the transaction will start.

    Reply

Leave a Comment

About HowToDoInJava

This blog provides tutorials and how-to guides on Java and related technologies.

It also shares the best practices, algorithms & solutions, and frequently asked interview questions.