Scrollable ResultSet and Moving Cursor Backward

JDBC Tutorials - Herong's Tutorial Examples

∟Derby (Java DB) - ResultSet Objects of Queries

∟Scrollable ResultSet and Moving Cursor Backward

This section describes how to set ResultSet to be scrollable and how to move cursor backward.

There are two options when setting ResultSet to be scrollable:

TYPE_SCROLL_INSENSITIVE - The result set is scrollable. Its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set generally does not show changes to the underlying database that are made while it is open. The membership, order, and column values of rows are typically fixed when the result set is created.
TYPE_SCROLL_SENSITIVE - The result set is scrollable. Its cursor can move forward or backward and can be moved to a particular row or to a row whose position is relative to its current position. The result set is sensitive to changes made while it is open. If the underlying column values are modified, the new values are visible, thus providing a dynamic view of the underlying data. The membership and ordering of rows in the result set may be fixed or not, depending on the implementation.

Generally, TYPE_SCROLL_INSENSITIVE is the preferred option. The data contained in the ResultSet object is fixed (a snapshot) when the object is created. Here is a sample program that shows you how to create a scrollable ResultSet and how to move the cursor backward:

/* DerbyScrollableResultSet.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class DerbyScrollableResultSet {
  public static void main(String [] args) {
    Connection con = null;
    try {
      con = DriverManager.getConnection("jdbc:derby://localhost/TestDB");

// Create a Statement for scrollable ResultSet
      Statement sta = con.createStatement(
        ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);

// Catch the ResultSet object
      ResultSet res = sta.executeQuery("SELECT * FROM Profile");

// Check ResultSet's scrollability
      if (res.getType() == ResultSet.TYPE_FORWARD_ONLY) {
        System.out.println("ResultSet non-scrollable.");
      } else {
        System.out.println("ResultSet scrollable.");
      }

      System.out.println("List of Profiles:");

// Move the cursor to the last row
      res.last();

// Stop the loop when the cursor is positioned before the first row
      while (!res.isBeforeFirst()) {
        String firstName = res.getString("FirstName");
        String lastName = res.getString("LastName");
        System.out.println("   "+firstName+" "+lastName);

// Move the cursor backward one row
        res.previous();
      }

// Close ResultSet and Statement
      res.close();
      sta.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
    }
  }
}

The output matches my expectation: profiles are listed backward.

ResultSet scrollable.
  List of Profiles:
  Keith Harris
  1ea2 480c7
  17e1 99a5d
  8c4 68909
  11bd 99552
  10cc ab361
  8ef 466ca
  256d aa3f5
  Janet Gates