JDBC- PreparedStatement example - Execute SELECT query(DML command) using PreparedStatement's executeQuery method and ResultSet in java


In this tutorial we will learn how to Execute SELECT query(DML command) using PreparedStatement's executeQuery method and ResultSet in java JDBC.

JDBC tutorial - What is java.sql.CallableStatement in java




In previous tutorial we read what is PreparedStatement. Now, we must know what is ResultSet because it stores the database table results.

What is ResultSet ?
  1. java.sql.ResultSet is an interface

  1. ResultSet is a table of data which represent a database result set, result is generated by executing a statement that queries the database.
  1. ResultSet object maintains a cursor pointing to its current row of data. cursor initially is pointing before the first row.

  1. The next method moves the cursor to the next row.
It returns false when there are no more rows in the ResultSet object that's the reason why
it can be used in a while loop to iterate through the ResultSet.

  1. If ResultSet object does not contain any row and is used in while loop then while loop will never be executed.


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;

--If table already exists then execute the DROP command >
drop table EMPLOYEE;


EMPLOYEE table will look like this >


Must read : java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, INSERT, UPDATE and DELETE


Example/ Programs JDBC- Execute SELECT query using PreparedStatement's executeQuery method and ResultSet in java

Example/ Full Program 1 (using column index in table)>
(using rs.getInt(1), here 1 is ID column in EMPLOYEE table) -
(using rs.getString(2), here 2 is NAME column in EMPLOYEE table) -
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementSelectExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                
                
                
                
                 //Executing PreparedStatement first time
                 prepStmt = con.prepareStatement("select * from EMPLOYEE where id=?");
                 prepStmt.setInt(1, 7); //substitute first occurrence of ? with 7
                 //execute select query
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt(1)+" ");
                       System.out.println(rs.getString(2));
                 }
                
                 //We must close ResultSet before assigning it new value
                 rs.close();
                
                 //Executing same PreparedStatement second time
                 prepStmt.setInt(1, 8); //substitute first occurrence of ? with 8
                 //execute select query
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt(1)+" ");
                       System.out.println(rs.getString(2));
                 }                
  
                
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
7 ankit
8 rohit
*/
In the above program we used rs.getInt(1) and rs.getInt(2), this might be bit confusing at time,

So to simplify things, we can mention database COLUMN_NAME like done in below program >

Example/ Full Program 2 (using column name in table) in java>
(using rs.getInt("ID"), here "ID" is ID column in EMPLOYEE table) -
(using rs.getString("NAME"), here "NAME" is NAME column in EMPLOYEE table) -

package d2_SelectResultSet_executeQuery_rsGetColumnName.copy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementExecuteQueryExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                
                 prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE where id=?");
                 prepStmt.setInt(1, 7);
                 //execute select query
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt("ID")+" ");
                       System.out.println(rs.getString("NAME"));
                 }
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
7 ankit
*/


Example/ Full Program 3 (using alias column name in table) in java>
(using rs.getInt("emp_id"), here "emp_id" is ID column in EMPLOYEE table) -
(using rs.getString("emp_name"), here "emp_name" is NAME column in EMPLOYEE table) -

package d3_SelectResultSet_executeQuery_rsGetColumnNameALIAS.copy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class PreparedStatementExecuteQueryExample {
   public static void main(String... arg) {
          Connection con = null;
          PreparedStatement prepStmt = null;
          ResultSet rs = null;                 
          try {
                 // registering Oracle driver class
                 Class.forName("oracle.jdbc.driver.OracleDriver");
                 // getting connection
                 con = DriverManager.getConnection(
                              "jdbc:oracle:thin:@localhost:1521:orcl",
                              "ankit", "Oracle123");
                 System.out.println("Connection established successfully!");
                
                 prepStmt = con.prepareStatement("select ID emp_id, NAME emp_name "
                              + "from EMPLOYEE "
                              + "where id=?");
                 prepStmt.setInt(1, 7);
                 // execute select query
                 rs = prepStmt.executeQuery();
                 while (rs.next()) {
                       System.out.print(rs.getInt("emp_id")+" ");
                       System.out.println(rs.getString("emp_name"));
                 }
          } catch (ClassNotFoundException e) {
                 e.printStackTrace();
          } catch (SQLException e) {
                 e.printStackTrace();
          }
          finally{
                 try {
                       if(rs!=null) rs.close(); //close resultSet
                       if(prepStmt!=null) prepStmt.close(); //close PreparedStatement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
7 ankit
*/


In this tutorial we learned how to Execute SELECT query(DML command) using PreparedStatement's executeQuery method and ResultSet in java JDBC.

RELATED LINKS>

12 Differences between Statement, PreparedStatement and CallableStatement in java


JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC- Calling Oracle database FUNCTION - CallableStatement example in java


JDBC- Insert/Store/save IMAGE in database using BLOB data type - in java


JDBC- Retrieve IMAGE from database using BLOB data type


No comments:

Post a Comment