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 ?
- java.sql.ResultSet is an interface
- ResultSet is a table of data which represent a database result set, result is generated by executing a statement that queries the database.
- ResultSet object maintains a cursor pointing to its current row of data. cursor initially is pointing before the first row.
- 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.
- 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>