17 Result Set Enhancements

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.

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.

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; 
} 

Specifying Result Set Scrollability and Updatability

Under JDBC 2.0, Connection classes have createStatement(), prepareStatement(), and prepareCall() method signatures that take a result set type and a concurrency type as input:

• Statement createStatement (int resultSetType, int resultSetConcurrency)

• PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency)

• CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency)

The statement objects created will have the intelligence to produce the appropriate kind of result sets.

You can specify one of the following static constant values for result set type:

• ResultSet.TYPE_FORWARD_ONLY

• ResultSet.TYPE_SCROLL_INSENSITIVE

• ResultSet.TYPE_SCROLL_SENSITIVE

  
  

  Note: See "Oracle Implementation of Scroll-Sensitive Result Sets" for information about possible performance impact.

  
  

And you can specify one of the following static constant values for concurrency type:

• ResultSet.CONCUR_READ_ONLY

• ResultSet.CONCUR_UPDATABLE

After creating a Statement, PreparedStatement, or CallableStatement object, you can verify its result set type and concurrency type by calling the following methods on the statement object:

• int getResultSetType() throws SQLException

• int getResultSetConcurrency() throws SQLException

...
PreparedStatement pstmt = conn.prepareStatement
  ("SELECT empno, sal FROM emp WHERE empno = ?", 
  ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
 
pstmt.setString(1, "28959");
ResultSet rs = pstmt.executeQuery();
...

Result Set Limitations and Downgrade Rules

Some types of result sets are not feasible for certain kinds of queries. If you specify an unfeasible result set type or concurrency type for the query you execute, the JDBC driver follows a set of rules to determine the best feasible types to use instead.

The actual result set type and concurrency type are determined when the statement is executed, with the driver issuing a SQLWarning on the statement object if the desired result set type or concurrency type is not feasible. The SQLWarning object will contain the reason why the requested type was not feasible. Check for warnings to verify whether you received the type of result set that you requested, or call the methods described in "Verifying Result Set Type and Concurrency Type".

SELECT t.* FROM TABLE t ...

Positioning in a Scrollable Result Set

In a scrollable result set, you can use several result set methods to move to a desired position and to check the current position.

Processing a Scrollable Result Set

In a scrollable result set you can iterate backward instead of forward as you process the result set. The following methods are available:

• boolean next() throws SQLException

• boolean previous() throws SQLException

The previous() method works similarly to the next() method, in that it returns true as long as the new current row is valid, and false as soon as it runs out of rows (has passed the first row).

...
/* NOTE: The specified concurrency type, CONCUR_UPDATABLE, is not relevant to this example. */

Statement stmt = conn.createStatement 
         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

rs.afterLast();
while (rs.previous())
{
   System.out.println(rs.getString("empno") + " " + rs.getFloat("sal"));
}
...

Performing a DELETE Operation in a Result Set

The result set deleteRow() method will delete the current row. Following is the method signature:

• void deleteRow() throws SQLException

  
  

  Important: Unlike UPDATE and INSERT operations in a result set, which require a separate step to propagate the changes to the database, a DELETE operation in a result set is immediately executed in the corresponding row in the database as well. Once you call deleteRow(), the changes will be made permanent with the next transaction COMMIT operation. Remember also that by default, the auto-commit flag is set to true. Therefore, unless you override this default, any deleteRow() operation will be executed and committed immediately.

  
  

Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except beforeFirst() and afterLast(), which do not go to a valid current row), and then delete that row, as in the following example (presuming a result set rs):

...
rs.absolute(5);
rs.deleteRow();
...

See "Positioning in a Scrollable Result Set" for information about the positioning methods.

Performing an UPDATE Operation in a Result Set

Performing a result set UPDATE operation requires two separate steps to first update the data in the result set and then copy the changes to the database.

Presuming the result set is also scrollable, you can position to a row using any of the available positioning methods (except beforeFirst() and afterLast(), which do not go to a valid current row), and then update that row as desired.

See "Positioning in a Scrollable Result Set" for information about the positioning methods.

Here are the steps for updating a row in the result set and database:

1. Call the appropriate updateXXX() methods to update the data in the columns you want to change.

   With JDBC 2.0, a result set object has an updateXXX() method for each datatype, as with the setXXX() methods previously available for updating the database directly.

   Each of these methods takes an int for the column number or a string for the column name and then an item of the appropriate datatype to set the new value. Following are a couple of examples for a result set rs:

   rs.updateString(1, "mystring");
   rs.updateFloat(2, 10000.0f);
   
   

2. Call the updateRow() method to copy the changes to the database (or the cancelRowUpdates() method to cancel the changes).

   Once you call updateRow(), the changes are executed and will be made permanent with the next transaction COMMIT operation. Be aware that by default, the auto-commit flag is set to true so that any executed operation is committed immediately.

   If you choose to cancel the changes before copying them to the database, call the cancelRowUpdates() method instead. This will also revert to the original values for that row in the local result set object. Note that once you call the updateRow() method, the changes are written to the transaction and cannot be canceled unless you roll back the transaction (auto-commit must be disabled to allow a ROLLBACK operation).

   Positioning to a different row before calling updateRow() also cancels the changes and reverts to the original values in the result set.

   Before calling updateRow(), you can call the usual getXXX() methods to verify that the values have been updated correctly. These methods take an int column index or string column name as input. For example:

   float myfloat = rs.getFloat(2);
   ...process myfloat to see if it's appropriate...
   
   

   
   

   Note: Result set UPDATE operations are visible in the local result set object for all result set types (forward-only, scroll-sensitive, and scroll-insensitive). Refer to "Seeing Internal Changes" for more information.

   
   

Statement stmt = conn.createStatement
         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");
if (rs.absolute(10))        // (returns false if row does not exist)
{
   rs.updateString(1, "28959");
   rs.updateFloat("sal", 100000.0f);
   rs.updateRow();
}
// Changes are made permanent with the next COMMIT operation.

Performing an INSERT Operation in a Result Set

Result set INSERT operations use what is called the result set insert-row, which is a staging area that holds the data for the inserted row until it is copied to the database. You must explicitly move to this row to write the data that will be inserted.

As with UPDATE operations, result set INSERT operations require separate steps to first write the data to the insert-row and then copy it to the database.

Following are the steps in executing a result set INSERT operation.

1. Move to the insert-row by calling the result set moveToInsertRow() method.

   
   

   Note: The result set will remember the current position prior to the moveToInsertRow() call. Afterward, you can go back to it with a moveToCurrentRow() call.

   
   

2. As with UPDATE operations, use the appropriate updateXXX() methods to write data to the columns. For example:

   rs.updateString(1, "mystring");
   rs.updateFloat(2, 10000.0f);
   
   

   (Note that you can specify a string for column name, instead of an integer for column number.)

   
   

   Important: Each column value in the insert-row is undefined until you call the updateXXX() method for that column. You must call this method and specify a non-null value for all non-nullable columns, or else attempting to copy the row into the database will result in a SQLException. It is permissible, however, to not call updateXXX() for a nullable column. This will result in a value of null.

   
   

3. Copy the changes to the database by calling the result set insertRow() method.

   Once you call insertRow(), the insert is executed and will be made permanent with the next transaction COMMIT operation.

   Positioning to a different row before calling insertRow() cancels the insert and clears the insert-row.

   Before calling insertRow() you can call the usual getXXX() methods to verify that the values have been set correctly in the insert-row. These methods take an int column index or string column name as input. For example:

   float myfloat = rs.getFloat(2);
   ...process myfloat to see if it's appropriate...
   
   

   
   

   Note: No result set type (neither scroll-sensitive, scroll-insensitive, nor forward-only) can see a row inserted by a result set INSERT operation. Refer to "Seeing Internal Changes" for more information.

   
   

...
Statement stmt = conn.createStatement
         (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery("SELECT empno, sal FROM emp");

rs.moveToInsertRow();
rs.updateString(1, "28959");
rs.updateFloat("sal", 100000.0f);
rs.insertRow();
// Changes will be made permanent with the next COMMIT operation.
rs.moveToCurrentRow();  // Go back to where we came from...
...

Update Conflicts

It is important to be aware of the following facts regarding updatable result sets with the JDBC drivers:

• The drivers do not enforce write locks for an updatable result set.

• The drivers do not check for conflicts with a result set DELETE or UPDATE operation.

A conflict will occur if you try to perform a DELETE or UPDATE operation on a row updated by another committed transaction.

The Oracle JDBC drivers use the ROWID to uniquely identify a row in a database table. As long as the ROWID is still valid when a driver tries to send an UPDATE or DELETE operation to the database, the operation will be executed.

The driver will not report any changes made by another committed transaction. Any conflicts are silently ignored and your changes will overwrite the previous changes.

To avoid such conflicts, use the Oracle FOR UPDATE feature when executing the query that produces the result set. This will avoid conflicts, but will also prevent simultaneous access to the data. Only a single write lock can be held concurrently on a data item.

Setting the Fetch Size

The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

• void setFetchSize(int rows) throws SQLException

• int getFetchSize() throws SQLException

To set the fetch size for a query, call setFetchSize() on the statement object prior to executing the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

After you have executed the query, you can call setFetchSize() on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows. (See "Refetching Rows".)

Use of Standard Fetch Size versus Oracle Row-Prefetch Setting

Using the JDBC 2.0 fetch size is fundamentally similar to using the Oracle row-prefetch value, except that with the row-prefetch value you do not have the flexibility of distinct values in the statement object and result set object. The row prefetch value would be used everywhere.

Furthermore, JDBC 2.0 fetch size usage is portable and can be used with other JDBC drivers. Oracle row-prefetch usage is vendor-specific.

See "Oracle Row Prefetching" for a general discussion of this Oracle feature.

Seeing Internal Changes

The ability of an updatable result set to see its own changes depends on both the result set type and the kind of change (UPDATE, DELETE, or INSERT). This is discussed at various points throughout the "Updating Result Sets" section beginning on , and is summarized as follows:

• Internal DELETE operations are visible for scrollable result sets (scroll-sensitive or scroll-insensitive), but are not visible for forward-only result sets.

  After you delete a row in a scrollable result set, the preceding row becomes the new current row, and subsequent row numbers are updated accordingly.

• Internal UPDATE operations are always visible, regardless of the result set type (forward-only, scroll-sensitive, or scroll-insensitive).

• Internal INSERT operations are never visible, regardless of the result set type (neither forward-only, scroll-sensitive, nor scroll-insensitive).

An internal change being "visible" essentially means that a subsequent getXXX() call will see the data changed by a preceding updateXXX() call on the same data item.

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean ownDeletesAreVisible(int) throws SQLException

• boolean ownUpdatesAreVisible(int) throws SQLException

• boolean ownInsertsAreVisible(int) throws SQLException

  
  

  Note: When you make an internal change that causes a trigger to execute, the trigger changes are effectively external changes. However, if the trigger affects data in the row you are updating, you will see those changes for any scrollable/updatable result set, because an implicit row refetch occurs after the update.

  
  

Seeing External Changes

Only a scroll-sensitive result set can see external changes to the underlying database, and it can only see the changes from external UPDATE operations. Changes from external DELETE or INSERT operations are never visible.

For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean othersDeletesAreVisible(int) throws SQLException

• boolean othersUpdatesAreVisible(int) throws SQLException

• boolean othersInsertsAreVisible(int) throws SQLException

  
  

  Note: Explicit use of the refreshRow() method, described in "Refetching Rows", is distinct from this discussion of visibility. For example, even though external updates are "invisible" to a scroll-insensitive result set, you can explicitly refetch rows in a scroll-insensitive/updatable result set and retrieve external changes that have been made. "Visibility" refers only to the fact that the scroll-insensitive/updatable result set would not see such changes automatically and implicitly.

  
  

Visibility versus Detection of External Changes

Regarding changes made to the underlying database by external sources, there are two similar but distinct concepts with respect to visibility of the changes from your local result set:

• visibility of changes

• detection of changes

A change being "visible" means that when you look at a row in the result set, you can see new data values from changes made by external sources to the corresponding row in the database.

A change being "detected", however, means that the result set is aware that this is a new value since the result set was first populated.

Even when an Oracle result set sees new data (as with an external UPDATE in a scroll-sensitive result set), it has no awareness that this data has changed since the result set was populated. Such changes are not "detected".

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this. Each takes a result set type as input (ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• boolean deletesAreDetected(int) throws SQLException

• boolean updatesAreDetected(int) throws SQLException

• boolean insertsAreDetected(int) throws SQLException

It follows, then, that result set methods specified by JDBC 2.0 to detect changes—rowDeleted(), rowUpdated(), and rowInserted()—will always return false. There is no use in calling them.

Summary of Visibility of Internal and External Changes

Table 17-1 summarizes the discussion in the preceding sections regarding whether a result set object in the Oracle JDBC implementation can see changes made internally through the result set itself, and changes made externally to the underlying database from elsewhere in your transaction or from other committed transactions.

For implementation details of scroll-sensitive result sets, including exactly how and how soon external updates become visible, see "Oracle Implementation of Scroll-Sensitive Result Sets".

Oracle Implementation of Scroll-Sensitive Result Sets

The Oracle implementation of scroll-sensitive result sets involves the concept of a window, with a window size that is based on the fetch size. The window size affects how often rows are updated in the result set.

Once you establish a current row by moving to a specified row (as described in "Positioning in a Scrollable Result Set"), the window consists of the N rows in the result set starting with that row, where N is the fetch size being used by the result set (see "Fetch Size"). Note that there is no current row, and therefore no window, when a result set is first created. The default position is before the first row, which is not a valid current row.

As you move from row to row, the window remains unchanged as long as the current row stays within that window. However, once you move to a new current row outside the window, you redefine the window to be the N rows starting with the new current row.

Whenever the window is redefined, the N rows in the database corresponding to the rows in the new window are automatically refetched through an implicit call to the refreshRow() method (described in "Refetching Rows"), thereby updating the data throughout the new window.

So external updates are not instantaneously visible in a scroll-sensitive result set; they are only visible after the automatic refetches just described.

Modified Connection Methods

Following is an alphabetical summary of modified connection methods that allow you to specify result set and concurrency types when you create statement objects.

• Statement createStatement (int resultSetType, int resultSetConcurrency)

  This method now allows you to specify result set type and concurrency type when you create a generic Statement object.

• CallableStatement prepareCall (String sql, int resultSetType, int resultSetConcurrency)

  This method now allows you to specify result set type and concurrency type when you create a PreparedStatement object.

• PreparedStatement prepareStatement (String sql, int resultSetType, int resultSetConcurrency)

  This method now allows you to specify result set type and concurrency type when you create a CallableStatement object.

New Result Set Methods

Following is an alphabetical summary of new result set methods for JDBC 2.0 result set enhancements.

• boolean absolute(int row) throws SQLException

  Move to an absolute row position in the result set.

• void afterLast() throws SQLException

  Move to after the last row in the result set (you will not be at a valid current row after this call).

• void beforeFirst() throws SQLException

  Move to before the first row in the result set (you will not be at a valid current row after this call).

• void cancelRowUpdates() throws SQLException

  Cancel an UPDATE operation on the current row. (Call this after the updateXXX() calls but before the updateRow() call.)

• void deleteRow() throws SQLException

  Delete the current row.

• boolean first() throws SQLException

  Move to the first row in the result set.

• int getConcurrency() throws SQLException

  Returns an int value for the concurrency type used for the query (either ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE).

• int getFetchSize() throws SQLException

  Check the fetch size to determine how many rows are fetched in each database round trip (also available in statement objects).

• int getRow() throws SQLException

  Returns the row number of the current row. Returns 0 if there is no valid current row.

• int getType() throws SQLException

  Returns an int value for the result set type used for the query (either ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE).

• void insertRow() throws SQLException

  Write a result set INSERT operation to the database. Call this after calling updateXXX() methods to set the data values.

• boolean isAfterLast() throws SQLException

  Returns true if the position is after the last row.

• boolean isBeforeFirst() throws SQLException

  Returns true if the position is before the first row.

• boolean isFirst() throws SQLException

  Returns true if the position is at the first row.

• boolean isLast() throws SQLException

  Returns true if the position is at the last row.

• boolean last() throws SQLException

  Move to the last row in the result set.

• void moveToCurrentRow() throws SQLException

  Move from the insert-row staging area back to what had been the current row prior to the moveToInsertRow() call.

• void moveToInsertRow() throws SQLException

  Move to the insert-row staging area to set up a row to be inserted.

• boolean next() throws SQLException

  Iterate forward through the result set.

• boolean previous() throws SQLException

  Iterate backward through the result set.

• void refreshRow() throws SQLException

  Refetch the database rows corresponding to the current window in the result set, to update the data. This method is called implicitly for scroll-sensitive result sets.

• boolean relative(int row) throws SQLException

  Move to a relative row position, either forward or backward from the current row.

• void setFetchSize(int rows) throws SQLException

  Set the fetch size to determine how many rows are fetched in each database round trip when refetching (also available in statement objects).

• void updateRow() throws SQLException

  Write an UPDATE operation to the database after using updateXXX() methods to update the data values.

• void updateXXX() throws SQLException

  Set or update data values in a row to be updated or inserted. There is an updateXXX() method for each datatype. After calling all the appropriate updateXXX() methods for the columns to be updated or inserted, call updateRow() for an UPDATE operation or insertRow() for an INSERT operation.

Statement Methods

Following is an alphabetical summary of statement methods for JDBC 2.0 result set enhancements. These methods are available in generic statement, prepared statement, and callable statement objects.

• int getFetchSize() throws SQLException

  Check the fetch size to determine how many rows are fetched in each database round trip when executing a query (also available in result set objects).

• void setFetchSize(int rows) throws SQLException

  Set the fetch size to determine how many rows are fetched in each database round trip when executing a query (also available in result set objects).

• void setResultSetCache(OracleResultSetCache cache) throws SQLException

  Use your own client-side cache implementation for scrollable result sets. Create your own class that implements the OracleResultSetCache interface, then use the setResultSetCache() method to input an instance of this class to the statement object that will create the result set.

• int getResultSetType() throws SQLException

  Check the result set type of result sets produced by this statement object (which was specified when the statement object was created).

• int getResultSetConcurrency() throws SQLException

  Check the concurrency type of result sets produced by this statement object (which was specified when the statement object was created).

Database Meta Data Methods

Following is an alphabetical summary of database meta data methods for JDBC 2.0 result set enhancements.

• boolean ownDeletesAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of its own internal DELETE operations.

• boolean ownUpdatesAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of its own internal UPDATE operations.

• boolean ownInsertsAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of its own internal INSERT operations.

• boolean othersDeletesAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of an external DELETE operation in the database.

• boolean othersUpdatesAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of an external UPDATE operation in the database.

• boolean othersInsertsAreVisible(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can see the effect of an external INSERT operation in the database.

• boolean deletesAreDetected(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can detect when an external DELETE operation occurs in the database. This method always returns false.

• boolean updatesAreDetected(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can detect when an external UPDATE operation occurs in the database. This method always returns false.

• boolean insertsAreDetected(int) throws SQLException

  Returns true if, in this JDBC implementation, the specified result set type can detect when an external INSERT operation occurs in the database. This method always returns false.