JDBC ResultSet Interface

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 type object, it may be a plain Statement or 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

Related Posts

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.