Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83724-01





Go to previous page Go to beginning of chapter Go to next page

Creating Scrollable or Updatable Result Sets

Under JDBC 1.0, no special attention is required in creating and using a result set. A result set is produced automatically to store the results of a query, and no result set types or categories must be specified, because there is only one kind of result set available--forward-only/read-only. For example (given a connection object conn):

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

In using JDBC 2.0 result set enhancements, however, you may specify the result set type (for scrollability and sensitivity) and the concurrency type (for updatability) when you create a generic statement or prepare a prepared statement or callable statement that will execute a query.

(Note, however, that callable statements are intended to execute stored procedures and functions and rarely return a result set. Still, the callable statement class is a subclass of the prepared statement class and so inherits this functionality.)

This section discusses the creation of result sets to use JDBC 2.0 enhancements.

Specifying Result Set Scrollability and Updatability

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

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:

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

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:


Following is an example of a prepared statement object that specifies a scroll-sensitive and updatable result set for queries executed through that statement (where conn is a connection object):

PreparedStatement pstmt = conn.prepareStatement
         ("SELECT empno, sal FROM emp WHERE empno = ?", 

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

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:

To produce a scroll-sensitive result set:

In fact, you cannot use ORDER BY for any result set where you will want to refetch rows. This applies to scroll-insensitive/updatable result sets as well as scroll-sensitive result sets. (See "Summary of New Methods for Result Set Enhancements" for general information about refetching.)


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



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. (You can try this out using SQL*Plus, for example.)  

Result Set Downgrade Rules

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


Verifying Result Set Type and Concurrency Type

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

Go to previous page
Go to beginning of chapter
Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.