Overview

This section provides an overview of JDBC 2.0 result set functionality and categories, and some discussion of implementation requirements for the Oracle JDBC drivers.

Result Set Functionality and Result Set Categories Supported in JDBC 2.0

Result set functionality in JDBC 2.0 includes enhancements for scrollability and positioning, sensitivity to changes by others, and updatability.

• Scrollability, positioning, and sensitivity are determined by the result set type.

• Updatability is determined by the concurrency type.

Specify the desired result set type and concurrency type when you create the statement object that will produce the result set.

Together, the various result set types and concurrency types provide for six different categories of result set.

This section provides an overview of these enhancements, types, and categories.

Scrollability, Positioning, and Sensitivity

Scrollability refers to the ability to move backward as well as forward through a result set. Associated with scrollability is the ability to move to any particular position in the result set, through either relative positioning or absolute positioning.

Relative positioning allows you to move a specified number of rows forward or backward from the current row. Absolute positioning allows you to move to a specified row number, counting from either the beginning or the end of the result set.

Under JDBC 1.0 (in JDK 1.1.x) you can scroll only forward, using the next() method as described in "Process the Result Set", and there is no positioning functionality. You can start only at the beginning and iterate row-by-row until the end.

Under JDBC 2.0 (in JDK 1.2.x), scrollable/positionable result sets are also available.

When creating a scrollable/positionable result set, you must also specify sensitivity. This refers to the ability of a result set to detect and reveal changes made to the underlying database from outside the result set.

A sensitive result set can see changes made to the database while the result set is open, providing a dynamic view of the underlying data. Changes made to the underlying columns values of rows in the result set are visible.

An insensitive result set is not sensitive to changes made to the database while the result set is open, providing a static view of the underlying data. You would need to retrieve a new result set to see changes made to the database.

Sensitivity is not an option in a JDBC 1.0/non-scrollable result set.

Result Set Types for Scrollability and Sensitivity

When you create a result set under JDBC 2.0 functionality, you must choose a particular result set type to specify whether the result set is scrollable/positional and sensitive to underlying database changes.

If the JDBC 1.0 functionality is all you desire, JDBC 2.0 continues to support this through the forward-only result set type. A forward-only result set cannot be sensitive.

If you want a scrollable result set, you must also specify sensitivity. Specify the scroll-sensitive type for the result set to be scrollable and sensitive to underlying changes. Specify the scroll-insensitive type for the result set to be scrollable but not sensitive to underlying changes.

To summarize, the following three result set types are available with JDBC 2.0:

• forward-only (JDBC 1.0 functionality--not scrollable, not positionable, and not sensitive)

• scroll-sensitive (scrollable and positionable; also sensitive to underlying database changes)

• scroll-insensitive (scrollable and positionable but not sensitive to underlying database changes)

  Note: The sensitivity of a scroll-sensitive result set (how often it is updated to see external changes) is affected by fetch size. See Fetch Size and "Oracle Implementation of Scroll-Sensitive Result Sets".

Updatability

Updatability refers to the ability to update data in a result set and then (presumably) copy the changes to the database. This includes inserting new rows into the result set or deleting existing rows.

Updatability might also require database write locks to mediate access to the underlying database. Because you cannot have multiple write locks concurrently, updatability in a result set is associated with concurrency in database access.

Result sets can optionally be updatable under JDBC 2.0, but not under JDBC 1.0.

Note: Updatability is independent of scrollability and sensitivity, although it is typical for an updatable result set to also be scrollable so that you can position it to particular rows that you want to update or delete.

Concurrency Types for Updatability

The concurrency type of a result set determines whether it is updatable. Under JDBC 2.0, the following concurrency types are available:

• updatable (updates, inserts, and deletes can be performed on the result set and copied to the database)

• read-only (the result set cannot be modified in any way)

Summary of Result Set Categories

Because scrollability and sensitivity are independent of updatability, the three result set types and two concurrency types combine for a total of six result set categories:

• forward-only/read-only

• forward-only/updatable

• scroll-sensitive/read-only

• scroll-sensitive/updatable

• scroll-insensitive/read-only

• scroll-insensitive/updatable

  Note: A forward-only updatable result set has no positioning functionality. You can only update rows as you iterate through them with the next() method.

Oracle JDBC Implementation Overview for Result Set Enhancements

This section discusses key aspects of the Oracle JDBC implementation of result set enhancements for scrollability--through use of a client-side cache--and for updatability--through use of ROWIDs.

It is permissible for customers to implement their own client-side caching mechanism, and Oracle provides an interface to use in doing so.

Oracle JDBC Implementation for Result Set Scrollability

Because the underlying Oracle8i server does not support scrollable cursors, Oracle JDBC must implement scrollability in a separate layer.

It is important to be aware that this is accomplished by using a client-side memory cache to store rows of a scrollable result set.

Important: Because all rows of any scrollable result set are stored in the client-side cache, a situation where the result set contains many rows, many columns, or very large columns might cause the client-side Java virtual machine to fail. Do not specify scrollability for a large result set.

Scrollable cursors in the Oracle server, and therefore a server-side cache, will be supported in a future Oracle release.

Oracle JDBC Implementation for Result Set Updatability

To support updatability, Oracle JDBC uses ROWIDs to uniquely identify database rows that appear in a result set. For every query into an updatable result set, the Oracle JDBC driver automatically retrieves the ROWID along with the columns you select.

Note: Client-side caching is not required by updatability in and of itself. In particular, a forward-only updatable result set will not require a client-side cache.

Implementing a Custom Client-Side Cache for Scrollability

There is some flexibility in how to implement client-side caching in support of JDBC 2.0 scrollable result sets.

Although Oracle JDBC provides a complete implementation, it also supplies an interface, OracleResultSetCache, that you can implement as desired:

public interface OracleResultSetCache 
{ 
  /** 
   * Save the data in the i-th row and j-th column. 
   */ 
  public void put (int i, int j, Object value) throws IOException; 

  /** 
   * Return the data stored in the i-th row and j-th column. 
   */ 
  public Object get (int i, int j) throws IOException; 

  /** 
   * Remove the i-th row. 
   */ 
  public void remove (int i) throws IOException; 

  /** 
   * Remove the data stored in i-th row and j-th column 
   */ 
  public void remove (int i, int j) throws IOException; 

  /** 
   * Remove all data from the cache. 
   */ 
  public void clear () throws IOException; 

  /** 
   * Close the cache. 
   */ 
  public void close () throws IOException; 
} 

If you implement this interface with your own class, your application code must instantiate your class and then use the setResultSetCache() method of an OracleStatement, OraclePreparedStatement, or OracleCallableStatement object to set the caching mechanism to use your implementation. Following is the method signature:

• void setResultSetCache(OracleResultSetCache cache)
throws SQLException

Call this method prior to executing a query. The result set produced by the query will then use your specified caching mechanism.

---