ResultSet Cursor and Scrollability

JDBC Tutorials - Herong's Tutorial Examples

∟Derby (Java DB) - ResultSet Objects of Queries

∟ResultSet Cursor and Scrollability

This section describes what is ResultSet cursor and ResultSet scrollability.

Each ResultSet object maintains a cursor (pointer) to identify the current data row. The cursor of a newly created ResultSet object is positioned before the first row. How to move the cursor is depending on the scrollability of the ResultSet.

1. Non-scrollable ResultSet - The ResultSet object type is ResultSet.TYPE_FORWARD_ONLY, the default type. Non-scrollable ResultSet supports only cursor move method:

next() - Moves the cursor forward one row from its current position.

2. Scrollable ResultSet - The ResultSet object type is ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE. Scrollable ResultSet supports a number of cursor move methods:

first() - Moves the cursor to the first row in this ResultSet object.
last() - Moves the cursor to the last row in this ResultSet object.
next() - Moves the cursor forward one row from its current position.
previous() - Moves the cursor backward one row from its current position.
absolute(+/-n) - Moves the cursor to the given row number in this ResultSet object. Negative row number counts backward from the last row.
relative(+/-n) -Moves the cursor a relative number of rows, either positive or negative. Attempting to move beyond the first/last row in the result set positions the cursor before/after the first/last row. Calling relative(0) is valid, but does not change the cursor position.

Here is a sample program that loops through the ResultSet rows forward only with next() method:

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

// 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.");
      }

// Move cursor forward only with next()
      System.out.println("List of Profiles:");
      while (res.next()) {
        String firstName = res.getString("FirstName");
        String lastName = res.getString("LastName");
        System.out.println("   "+firstName+" "+lastName);
      }

// Close ResultSet
      res.close();

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

The output is listed below:

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