Skip Headers
Oracle® Database JDBC Developer's Guide and Reference,
11g Release 1 (11.1)

B31224-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

17 Result Set

Standard Java Database Connectivity (JDBC) 2.0 features in Java Development Kit (JDK) 1.2.x include enhancements to result set functionality, such as processing forward or backward, positioning relatively or absolutely, seeing changes to the database made internally or externally, and updating result set data and then copying the changes to the database.

This chapter discusses these features, including the following topics:

Overview of JDBC 2.0 Result Set

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

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.

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 enables you to move a specified number of rows forward or backward from the current row. Absolute positioning enables you to move to a specified row number, counting from either the beginning or the end of the result set.

Under JDBC 2.0, 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.

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, then 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 result set types are available with JDBC 2.0:

  • Forward-only

    This is a JDBC 1.0 functionality. This type of result set is not scrollable, not positionable, and not sensitive.

  • Scroll-sensitive

    This type of result set is scrollable and positionable. It is also sensitive to underlying database changes.

  • Scroll-insensitive

    This type of result set is scrollable and positionable, but not sensitive to underlying database changes.

    Note:

    The sensitivity of a scroll-sensitive result set is affected by fetch size.

Updatability

Updatability refers to the ability to update data in a result set and then 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

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

    In this case, 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, as follows:

  • 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 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 (JVM) to fail. Do not specify scrollability for a large result set.

Oracle JDBC Implementation for Result Set Updatability

To support updatability, Oracle JDBC uses ROWID to uniquely identify database rows that appear in a result set. For every query into an updatable result set, 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, then 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 running a query. The result set produced by the query will then use your specified caching mechanism.

Creating Scrollable or Updatable Result Sets

In using JDBC 2.0 result set enhancements, you may specify the result set type and the concurrency type when you create a generic statement or prepare a prepared statement or callable statement that will run a query.

This section discusses the creation of result sets to use JDBC 2.0 enhancements. It covers the following topics:

Specifying Result Set Scrollability and Updatability

Under JDBC 2.0, the Connection class has the following methods 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

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

Example 17-1 Prepared Statement Object With Result Set

Following is an example of a prepared statement object that specifies a scroll-sensitive and updatable result set for queries run through that statement:

...
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 run, then 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 run, 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.

Result Set Limitations

The following limitations are placed on queries for enhanced result sets. Failure to follow these guidelines will result in the JDBC driver choosing an alternative result set type or concurrency type.

To produce an updatable result set:

  • A query can select from only a single table and cannot contain any join operations.

    In addition, for inserts to be feasible, the query must select all non-nullable columns and all columns that do not have a default value.

  • A query cannot use SELECT * .

    However, there is a workaround for this.

  • A query must select table columns only.

    It cannot select derived columns or aggregates, such as the SUM or MAX of a set of columns.

To produce a scroll-sensitive result set:

  • A query cannot use SELECT * .

    However, there is a workaround for this.

  • A query can select from only a single table.

Scrollable and updatable result sets cannot have any column as Stream. When the server has to fetch a Stream column, it reduces the fetch size to one and blocks all columns following the Stream column until the Stream column is read. As a result, columns cannot be fetched in bulk and scrolled through.

Workaround

As a workaround for the SELECT * limitation, you can use table aliases, as shown in the following example:

SELECT t.* FROM TABLE t ...

Note:

There is a simple way to determine if your query will probably produce a scroll-sensitive or updatable result set: If you can legally add a ROWID column to the query list, then the query is probably suitable for either a scroll-sensitive or an updatable result set.

Result Set Downgrade Rules

If the specified result set type or concurrency type is not feasible, then Oracle JDBC driver uses the following rules in choosing alternate types:

  • If the specified result set type is TYPE_SCROLL_SENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_SCROLL_INSENSITIVE.

  • If the specified or downgraded result set type is TYPE_SCROLL_INSENSITIVE, but the JDBC driver cannot fulfill that request, then the driver attempts a downgrade to TYPE_FORWARD_ONLY.

  • If the specified concurrency type is CONCUR_UPDATABLE, but the JDBC driver cannot fulfill that request, then the JDBC driver attempts a downgrade to CONCUR_READ_ONLY.

Note:

Any manipulations of the result set type and concurrency type by the JDBC driver are independent of each other.

Verifying Result Set Type and Concurrency Type

After a query has been run, you can verify the result set type and concurrency type that the JDBC driver actually used, by calling methods on the result set object.

  • int getType() throws SQLException

    This method returns an int value for the result set type used for the query. ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE are the possible values.

  • int getConcurrency() throws SQLException

    This method returns an int value for the concurrency type used for the query. ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE are the possible values.

Positioning and Processing in Scrollable Result Sets

Scrollable result sets enable you to iterate through them, either forward or backward, and to position the result set to any desired row.

This section discusses positioning within a scrollable result set and how to process a scrollable result set backward, instead of forward. It covers the following sections:

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.

Note:

You cannot position a forward-only result set. Any attempt to position it or to determine the current position will result in a SQLException exception.

Methods for Moving to a New Position

The following result set methods are available for moving to a new position in a scrollable result set:

  • void beforeFirst() throws SQLException

    Positions to before the first row of the result set, or has no effect if there are no rows in the result set. This is where you would typically start iterating through a result set to process it going forward and is the default initial position for any kind of result set.

    You are outside the result set bounds after a beforeFirst() call. There is no valid current row, and you cannot position relatively from this point.

  • void afterLast() throws SQLException

    Positions to after the last row of the result set, or has no effect if there are no rows in the result set. This is where you would typically start iterating through a result set to process it going backward.

    You are outside the result set bounds after an afterLast() call. There is no valid current row, and you cannot position relatively from this point.

  • boolean first() throws SQLException

    Positions to the first row of the result set, or returns false if there are no rows in the result set.

  • boolean last() throws SQLException

    Positions to the last row of the result set, or returns false if there are no rows in the result set.

  • boolean absolute(int row) throws SQLException

    Positions to an absolute row from either the beginning or end of the result set. If you enter a positive number, then it positions from the beginning. If you enter a negative number, then it positions from the end. This method returns false if there are no rows in the result set.

    Attempting to move forward beyond the last row, such as an absolute(11) call if there are 10 rows, will position to after the last row, having the same effect as an afterLast() call.

    Attempting to move backward beyond the first row, such as an absolute(-11) call if there are 10 rows, will position to before the first row, having the same effect as a beforeFirst() call.

    Note:

    Calling absolute(1) is equivalent to calling first(); calling absolute(-1) is equivalent to calling last().
  • boolean relative(int row) throws SQLException

    Moves to a position relative to the current row, forward if you enter a positive number or backward if you enter a negative number, or returns false if there are no rows in the result set.

    The result set must be at a valid current row for use of the relative method.

    Attempting to move forward beyond the last row will position to after the last row, having the same effect as an afterLast() call.

    Attempting to move backward beyond the first row will position to before the first row, having the same effect as a beforeFirst() call.

    A relative(0) call is valid but has no effect.

    Note:

    You cannot position relatively from before the first row, which is the default initial position, or after the last row. Attempting relative positioning from either of these positions would result in a SQLException exception.

Methods for Checking the Current Position

The following result set methods are available for checking the current position in a scrollable result set:

  • boolean isBeforeFirst() throws SQLException

    Returns true if the position is before the first row.

  • boolean isAfterLast() throws SQLException

    Returns true if the position is after the last 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.

  • int getRow() throws SQLException

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

    Note:

    The boolean methods, isFirst(), isLast(), isAfterFirst(), and isAfterLast(), all return false. Also, they do not throw an exception if there are no rows in the result set.

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, that is, has passed the first row.

Backward versus Forward Processing

You can process the entire result set going forward, using the next() method. The default initial position in the result set is before the first row, appropriately, but you can call the beforeFirst() method if you have moved elsewhere since the result set was created.

To process the entire result set going backward, call afterLast(), then use the previous() method. For example:

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

Unlike relative positioning, you can use next() from before the first row and previous() from after the last row. You do not have to be at a valid current row to use these methods.

Note:

In a non-scrollable result set, you can process only with the next() method. Attempting to use the previous() method will cause a SQLException exception.

Presetting the Fetch Direction

The JDBC 2.0 standard allows the ability to pre-specify the direction, known as the fetch direction, for use in processing a result set. This allows the JDBC driver to optimize its processing. The following result set methods are specified:

  • void setFetchDirection(int direction) throws SQLException

  • int getFetchDirection() throws SQLException

Oracle JDBC drivers support only the forward preset value, which you can specify by entering the ResultSet.FETCH_FORWARD static constant value.

The values ResultSet.FETCH_REVERSE and ResultSet.FETCH_UNKNOWN are not supported. Attempting to specify them causes a SQL warning, and the settings are ignored.

Updating Result Sets

A concurrency type of CONCUR_UPDATABLE enables you to update rows in the result set, delete rows from the result set, or insert rows into the result set.

After you perform an UPDATE or INSERT operation in a result set, you propagate the changes to the database in a separate step that you can skip if you want to cancel the changes.

However, a DELETE operation in a result set is immediately run, but not necessarily committed, in the database as well.

Note:

When using an updatable result set, it is typical to also make it scrollable. This enables you to position to any row that you want to change. With a forward-only updatable result set, you can change rows only as you iterate through them with the next() method.

This section covers the following topics:

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

Note:

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 run in the corresponding row in the database as well.

Once you call deleteRow method, the changes are 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 method operation is run 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 method and afterLast method, which do not go to a valid current row, and then delete that row, as in the following example:

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

Important:

The deleted row remains in the result set object even after it has been deleted from the database.

In a scrollable result set, by contrast, a DELETE operation is evident in the local result set object. The row would no longer be in the result set after the DELETE. The row preceding the deleted row becomes the current row, and row numbers of subsequent rows are changed accordingly.

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.

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 data type, 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 data type 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 the updateRow method, the changes are run and are made permanent with the next transaction COMMIT operation. Be aware that by default, the auto-commit flag is set to true so that any operation run is committed immediately.

    If you choose to cancel the changes before copying them to the database, then 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.

    Note:

    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.

Example

Following is an example of a result set UPDATE operation that is also copied to the database. The tenth row is updated. The column number is used to specify column 1, and the column name, sal, is used to specify column 2.

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 running 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);
    

    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 exception.

    However, it is permissible 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 processed 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 can see a row inserted by a result set INSERT operation.

Example

The following example performs a result set INSERT operation, moving to the insert-row, writing the data, copying the data into the database, and then returning to what was the current row prior to going to the insert-row. The column number is used to specify column 1, and the column name, sal, is used to specify column 2.

...
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...
...

Avoiding 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.

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

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 running 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.

Fetch Size

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

JDBC 2.0 also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

Note:

Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.

The result set fetch size, either set explicitly, or by default equal to the statement fetch size that was passed to it, determines the number of rows that are retrieved in any subsequent trips to the database for that result set. This includes any trips that are still required to complete the original query, as well as any refetching of data into the result set. Data can be refetched, either explicitly or implicitly, to update a scroll-sensitive or scroll-insensitive/updatable result set.

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 running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

After you have run 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.

Refetching Rows

The result set refreshRow method is supported for some types of result sets for refetching data. This consists of going back to the database to re-obtain the database rows that correspond to n rows in the result set, starting with the current row, where n is the fetch size. This lets you see the latest updates to the database that were made outside of your result set, subject to the isolation level of the enclosing transaction.

Because refetching re-obtains only rows that correspond to rows already in your result set, it does nothing about rows that have been inserted or deleted in the database since the original query. It ignores rows that have been inserted, and rows will remain in your result set even after the corresponding rows have been deleted from the database. When there is an attempt to refetch a row that has been deleted in the database, the corresponding row in the result set will maintain its original values.

Note:

If you declare a TYPE_SCROLL_SENSITIVE Result Set based on a query with certain criteria and then externally update the row so that the column values no longer match the query criteria, the driver behaves as if the row has been deleted from the database and the row is not retrieved by the query issued. So, you do not see the updates to the particular row when you call the refreshRow method.

Following is the signature of the refreshRow method:

void refreshRow() throws SQLException

You must be at a valid current row when you call this method, not outside the row bounds and not at the insert-row.

The refreshRow method is supported for the following result set categories:

Note:

Scroll-sensitive result set functionality is implemented through implicit calls to refreshRow.

Seeing Database Changes Made Internally and Externally

This section discusses the ability of a result set to see the following:

Note:

External changes are referred to as other's changes in the Sun Microsystems JDBC 2.0 specification.

This section covers the following topics:

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. This is summarized as follows:

  • Internal DELETE operations are visible for scrollable result sets, 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.

  • Internal INSERT operations are never visible, regardless of the result set type.

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:

  • boolean ownDeletesAreVisible(int) throws SQLException

  • boolean ownUpdatesAreVisible(int) throws SQLException

  • boolean ownInsertsAreVisible(int) throws SQLException

Each takes a result set type, ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE, as input.

Note:

When you make an internal change that causes a trigger to run, the trigger changes are effectively external changes. However, if the trigger affects data in the row you are updating, then 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.

Note:

Any discussion of seeing changes from outside the enclosing transaction presumes the transaction itself has an isolation level setting that allows the changes to be visible.

JDBC 2.0 DatabaseMetaData objects include the following methods to verify this:

  • boolean othersDeletesAreVisible(int) throws SQLException

  • boolean othersUpdatesAreVisible(int) throws SQLException

  • boolean othersInsertsAreVisible(int) throws SQLException

Each takes a result set type, ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE, as input.

Note:

Explicit use of the refreshRow method 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:

  • boolean deletesAreDetected(int) throws SQLException

  • boolean updatesAreDetected(int) throws SQLException

  • boolean insertsAreDetected(int) throws SQLException

Each takes a result set type, ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_SENSITIVE, or ResultSet.TYPE_SCROLL_INSENSITIVE, as input.

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.

Table 17-1 Visibility of Internal and External Changes for Oracle JDBC

Result Set Type Can See Internal DELETE? Can See Internal UPDATE? Can See Internal INSERT? Can See External DELETE? Can See External UPDATE? Can See External INSERT?

forward-only

no

yes

no

no

no

no

scroll-sensitive

yes

yes

no

no

yes

no

scroll-insensitive

yes

yes

no

no

no

no


Note:

  • Remember that explicit use of the refreshRow method, is distinct from the concept of visibility of external changes.

  • Remember that even when external changes are visible, as with UPDATE operations underlying a scroll-sensitive result set, they are not detected. The result set rowDeleted, rowUpdated, and rowInserted methods always return false.

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, 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. 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, 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.

Note:

Because this kind of refetching is not a highly efficient or optimized methodology, there are significant performance concerns. Consider carefully before using scroll-sensitive result sets as currently implemented. There is also a significant trade-off between sensitivity and performance. The most sensitive result set is one with a fetch size of 1, which would result in the new current row being refetched every time you move between rows. However, this would have a significant impact on the performance of your application.