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


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



What is ResultSet in java JDBC?
  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 in java JDBC.

  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 in java JDBC.


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

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


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

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

Full Program 1 (using column index in table) in java JDBC>
(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.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementSelectExample {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = 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!");
                
                 stmt = con.createStatement();
                 //execute select query
                 rs = stmt.executeQuery("select * from EMPLOYEE");
                 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(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
1 ankit
2 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)>
(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 com.ankit;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementExecuteQueryExample {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = 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!");
                
                 stmt = con.createStatement();
                 //execute select query
                 rs = stmt.executeQuery("select ID, NAME from EMPLOYEE");
                 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(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
1 ankit
2 rohit
*/


Example/ Full Program 3 (using alias column name in table)>
(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 com.ankit;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class StatementExecuteQueryExample {
   public static void main(String... arg) {
          Connection con = null;
          Statement stmt = 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!");
                
                 stmt = con.createStatement();
                 //execute select query
                 rs = stmt.executeQuery("select ID emp_id, NAME emp_name from EMPLOYEE");
                 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(stmt!=null) stmt.close(); //close Statement
                       if(con!=null) con.close(); // close connection
                 } catch (SQLException e) {
                       e.printStackTrace();
                 }
          }
   }
}
/*OUTPUT
Connection established successfully!
1 ankit
2 rohit
*/



NOTE : Statement is suitable for executing DDL commands - create, drop, alter and truncate. This article demonstrates that Statement can also be used to serve the purpose.
We must prefer PreparedStatement, as it is suitable for executing DML commands - SELECT, INSERT, UPDATE and DELETE in java JDBC.


So, in this tutorial we learned how to execute SELECT query(DML command)  using Statement's executeQuery method and ResultSet in java JDBC.

RELATED LINKS>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


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


No comments:

Post a Comment