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 !!
Leave a Reply