JDBC Tutorials - Herong's Tutorial Examples - Version 3.02, by Dr. Herong Yang

JDBC Tutorials - Herong's Tutorial Examples

∟Java DB (Derby) - ResultSet Objects of Queries

∟Scrollable ResultSet and Moving 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) 2015, 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
   19c2 8770b
   1090 3fb07
   13ba 88356
   1352 17d9
   11bd 58ad0
   1002 e3873
   21d6 efd17
   Janet Gates

Last update: 2015.

Table of Contents

 About This Book

 JDBC (Java Database Connectivity) Introduction

 JDK (Java SE) Installation

 Installing and Running Java DB - Derby

 Derby (Java DB) JDBC Driver

 Derby (Java DB) JDBC DataSource Objects

 Java DB (Derby) - DML Statements

►Java DB (Derby) - ResultSet Objects of Queries

 What Is ResultSet?

 ResultSet Cursor and Scrollability

 ResultSet Cursor Initial Position: Before First Row

 Retrieving Column Values with getXXX() Methods

 ResultSet Default Type: Forward-only

►Scrollable ResultSet and Moving Cursor Backward

 ResultSet Objects with Update Capability

 insertRow() - Inserting New Rows through ResultSet Objects

 updateXXX() - Updating Column Values for Row Update or Insert

 deleteRow() - Deleting Rows through ResultSet Objects

 Java DB (Derby) - PreparedStatement

 MySQL Installation on Windows

 MySQL JDBC Driver (MySQL Connector/J)

 MySQL - PreparedStatement

 MySQL - Reference Implementation of JdbcRowSet

 MySQL - JBDC CallableStatement

 MySQL CLOB (Character Large Object) - TEXT

 MySQL BLOB (Binary Large Object) - BLOB

 Oracle Express Edition Installation on Windows

 Oracle JDBC Drivers

 Oracle - Reference Implementation of JdbcRowSet

 Oracle - PreparedStatement

 Oracle - JBDC CallableStatement

 Oracle CLOB (Character Large Object) - TEXT

 Oracle BLOB (Binary Large Object) - BLOB

 Microsoft SQL Server 2005 Express Edition

 Microsoft JDBC Driver for SQL Server - sqljdbc42.jar

 Microsoft JDBC Driver - Query Statements and Result Sets

 Microsoft JDBC Driver - DatabaseMetaData Object

 Microsoft JDBC Driver - DDL Statements

 Microsoft JDBC Driver - DML Statements

 SQL Server - PreparedStatement

 SQL Server CLOB (Character Large Object) - TEXT

 SQL Server BLOB (Binary Large Object) - BLOB

 JDBC-ODBC Bridge Driver - sun.jdbc.odbc.JdbcOdbcDriver

 JDBC-ODBC Bridge Driver - Flat Text Files

 JDBC-ODBC Bridge Driver - MS Access

 JDBC-ODBC Bridge Driver - MS SQL Server

 Summary of JDBC Drivers and Database Servers

 Additional Tutorial Notes to Be Added

 Outdated Tutorials

 References

 PDF Printing Version

Scrollable ResultSet and Moving Cursor Backward - Updated in 2017, by Dr. Herong Yang