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