java.sql.ResultSet interface represents the data you get by executing a database query. Once you get the ResultSet you
can iterate it to go over the records. For facilitating iteration, ResultSet object maintains a cursor pointing to its
current row of data. Initially the cursor is positioned before the first row.
There is a next() method in ResultSet that moves the cursor to the next row, returning false when there are no more rows in the ResultSet object.
Creating and iterating a ResultSet
You can obtain an object of ResultSet from a Statement object, it may be a plain Statement, PreparedStatement or a CallableStatement.
// From Statement
ResultSet rs = statement.executeQuery("Select * from Employee");
// From PreparedStatement
String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
PreparedStatement prepStmt = connection.prepareStatement(selectSQL);
ResultSet rs = prepStmt.executeQuery();
// From CallableStatement
CallableStatement callableStatement = connection.prepareCall(
"{call select_all_employee()}");
ResultSet rs = callableStatement.executeQuery();
Once you have the ResultSet object having the records you can iterate through it by using next method in a while loop.
while(rs.next()){
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}
ResultSet Java example
In the example Records are fetched from an Employee table and then the ResultSet is iterated to go through the records. DB used is MySql, schema is knpcode and table used is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.
As already mentioned ResultSet is iterated using next method.
For getting column values from a row there are various getters for different types where you can pass either the column index or column name. Note that column index starts from 1, for example if you have executed the query Select id, first_name, last_name, department from Employee then first_name can be retrieved either using column index
rs.getString(2);
Or by using column name
rs.getString("FIRST_NAME")
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetDemo {
public static void main(String[] args) {
Connection connection = null;
try {
// Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
// connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// create Statement object
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("Select * from Employee");
// Iterate through ResultSet
while(rs.next()){
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}
}catch(ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(connection != null){
//close connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
ResultSet Types in Java
By default ResultSet object is not updatable that is you can't update the record which is part of ResultSet, also by default ResultSet has a cursor that moves forward only that is you can iterate through it only once and only from the first row to the last row. But there are fields in ResultSet to change that default behavior and create a ResultSet that is scrollable and/or updatatble.
Fields in ResultSet for making it scrollable are as given below-
- ResultSet.TYPE_FORWARD_ONLY- The constant indicating the type for a ResultSet object whose cursor may move only in forward direction.
- ResultSet.TYPE_SCROLL_INSENSITIVE- The constant indicating the type for a ResultSet object that is scrollable in both forward and backward directions but generally not sensitive to changes to the data that underlies the ResultSet. If data in the DB is changed by any another process that change won’t be reflected in the data stored in the ResultSet.
- ResultSet.TYPE_SCROLL_SENSITIVE- The constant indicating the type for a ResultSet object that is scrollable in both forward and backward directions and generally sensitive to changes to the data that underlies the ResultSet. If data in the DB is changed by any another process that change is reflected in the data stored in the ResultSet.
ResultSet Concurrency Modes
Fields in ResultSet that specify whether the data in the ResultSet can be updated or is Readonly.
- ResultSet.CONCUR_READ_ONLY- The constant indicating the concurrency mode as read only for a ResultSet object. You can only read the data update operations are not allowed.
- ResultSet.CONCUR_UPDATABLE- The constant indicating the concurrency mode for a ResultSet object that may be updated.
If you want to change the default behavior then you can specify these fields while creating the Statement object. For example if you want a ResultSet that can be moved in both directions but insensitive to changes and is read only.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet Holdability
Apart from the above mentioned field you can also specify whether the ResultSet is closed with the commit operation or kept open.
- CLOSE_CURSORS_AT_COMMIT- This constant indicates that open ResultSet objects with this holdability will be closed when the current transaction is committed.
- HOLD_CURSORS_OVER_COMMIT- This constant indicates that open ResultSet objects with this holdability will remain open when the current transaction is committed.
Scrollable ResultSet Java example
Here is an example that creates a scrollable resultset.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetDemo {
public static void main(String[] args) {
Connection connection = null;
try {
// Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
// connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// create Statement object
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.executeQuery("Select * from Employee");
// Iterate through ResultSet
while(rs.next()){
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}
// moving to 5th row
rs.absolute(5);
System.out.println("Record at 5th Row");
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}catch(ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(connection != null){
//close connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
Output
id: 2 First Name: John Last Name: McClane Dept: Police id: 3 First Name: Luc Last Name: Deveraux Dept: Army id: 4 First Name: Harry Last Name: Callahan Dept: Police id: 5 First Name: Gordon Last Name: Gekko Dept: Finance id: 10 First Name: Jack Last Name: Cullinan Dept: Finance id: 15 First Name: Ravi Last Name: Upadhyay Dept: Finance id: 16 First Name: John Last Name: Trudaue Dept: Finance id: 17 First Name: Ranjeet Last Name: Sharma Dept: Police Record at 5th Row id: 10 First Name: Jack Last Name: Cullinan Dept: Finance
Methods in ResultSet for cursor movement
You can use any of the following methods to move the cursor with in the ResultSet.
- absolute(int row)- Moves the cursor to the given row number in this ResultSet object.
- afterLast()- Moves the cursor to the end of this ResultSet object, just after the last row.
- beforeFirst()- Moves the cursor to the front of this ResultSet object, just before the first row.
- first()- Moves the cursor to the first row in this ResultSet object.
- last()- Moves the cursor to the last row in this ResultSet object.
- moveToCurrentRow()- Moves the cursor to the remembered cursor position, usually the current row.
- next()- Moves the cursor forward one row from its current position.
- previous()- Moves the cursor to the previous row in this ResultSet object.
Updater methods in ResultSet
There are various updater methods in ResultSet for updating data of different types. These updater methods are used when the ResultSet is updatable. First you will use updateXXX() method to update the column value then you will call either updateRow() to update the data source table from which rs was derived or insertRow() to insert a new row into result set and ultimately into the data source table.
Updatable ResultSet Java example
Here is an example to both update and insert a row using ResultSet methods.import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetDemo {
public static void main(String[] args) {
Connection connection = null;
try {
// Load driver
Class.forName("com.mysql.cj.jdbc.Driver");
// connection object
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode",
"root", "admin");
// create Statement object
Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = statement.executeQuery("Select * from Employee");
// Iterate through ResultSet
while(rs.next()){
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
}
// moving to 5th row and updating a column
rs.absolute(5);
System.out.println("Record at 5th Row");
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
rs.updateString("LAST_NAME", "Reacher");
rs.updateRow();
System.out.println("Updated Record at 5th Row");
System.out.println("id: " + rs.getInt("id") +
" First Name: " + rs.getString("FIRST_NAME") +
" Last Name: " + rs.getString("LAST_NAME")+
" Dept: " + rs.getString("DEPARTMENT"));
// inserting a new row
rs.moveToInsertRow();
// Using column index
rs.updateString(2, "Dean");
rs.updateString(3, "Jones");
//Using Column name
rs.updateString("Department", "Sports");
rs.insertRow();
rs.moveToCurrentRow();
}catch(ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally{
if(connection != null){
//close connection
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
Output
id: 2 First Name: John Last Name: McClane Dept: Police id: 3 First Name: Luc Last Name: Deveraux Dept: Army id: 4 First Name: Harry Last Name: Callahan Dept: Police id: 5 First Name: Gordon Last Name: Gekko Dept: Finance id: 10 First Name: Jack Last Name: Cullinan Dept: Finance id: 15 First Name: Ravi Last Name: Upadhyay Dept: Finance id: 16 First Name: John Last Name: Trudaue Dept: Finance id: 17 First Name: Ranjeet Last Name: Sharma Dept: Police Record at 5th Row id: 10 First Name: Jack Last Name: Cullinan Dept: Finance Updated Record at 5th Row id: 10 First Name: Jack Last Name: Reacher Dept: Finance
That's all for the topic JDBC ResultSet Interface. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment