Result Sets::JAVA

The ResultSet interface provides methods for retrieving and manipulting the
results of executed queries.

Kinds of ResultSet Objects

ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability.

ResultSet Types

The type of a ResultSet object determines the level of its functionality in two main
areas: (1) the ways in which the cursor can be manipulated and (2) how concurrent
changes made to the underlying data source are reflected by the ResultSet object.
The latter is called the sensitivity of the ResultSet object.

The three different ResultSet types are described below.

1. TYPE_FORWARD_ONLY

• The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row.
• The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

2. TYPE_SCROLL_INSENSITIVE

• The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
• The result set is insensitive to changes made to the underlying data source while it is open. It contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

3. TYPE_SCROLL_SENSITIVE

• The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
• The result set reflects changes made to the underlying data source while the result set remains open.

The default ResultSet type is TYPE_FORWARD_ONLY. The method DatabaseMetaData.supportsResultSetType returns true if the specified type is supported by the driver and false otherwise. If the driver does not support the type supplied to the methods createStatement, prepareStatement, or prepareCall, it generates an SQLWarning on the
Connection object that is creating the statement. When the statement is executed, the driver returns a ResultSet object of a type that most closely matches the requested type. An application can find out the type of a ResultSet object by calling the method ResultSet.getType.

ResultSet Concurrency

The concurrency of a ResultSet object determines what level of update
functionality is supported.
The two concurrency levels are:

• n CONCUR_READ_ONLY The ResultSet object cannot be updated using the ResultSet interface.
• n CONCUR_UPDATABLE

The ResultSet object can be updated using the ResultSet interface. The default ResultSet concurrency is CONCUR_READ_ONLY. The method DatabaseMetaData.supportsResultSetConcurrency returns true if the specified concurrency level is supported by the driver and false otherwise. If the driver does not support the concurrency level supplied to the methods createStatement, prepareStatement, or prepareCall, it generates an SQLWarning on the Connection object that is creating the statement. An
application can find out the concurrency of a ResultSet object by calling the method ResultSet.getConcurrency. If the driver cannot return a ResultSet object at the requested type and concurrency, it determines the appropriate type before determining the concurrency.

ResultSet Holdability

Calling the method Connection.commit can close the ResultSet objects that
have been created during the current transaction. In some cases, however, this may
not be the desired behaviour. The ResultSet property holdability gives the
application control over whether ResultSet objects (cursors) are closed when
commit is called.

The following ResultSet constants may be supplied to the Connection methods
createStatement, prepareStatement, and prepareCall:

1.HOLD_CURSORS_OVER_COMMIT

• ResultSet objects (cursors) are not closed; they are held open when the method commit is called.

2. CLOSE_CURSORS_AT_COMMIT

• ResultSet objects (cursors) are closed when commit is called. Closing cursors at commit can result in better performance for some applications.The default holdability of ResultSet objects is implementation defined. The DatabaseMetaData method getResultSetHoldability can be called to determine the default holdability of result sets returned by the underlying data source.
  

Specifying ResultSet Type, Concurrency and Holdability

The parameters supplied to the methods Connection.createStatement,
Connection.prepareStatement, and Connection.prepareCall determine
the type, concurrency, and holdability of ResultSet objects that the statement
produces. CODE EXAMPLE 14-1 creates a Statement object that will return scrollable,
read-only ResultSet objects that are insensitive to updates made to the data source
and that will be closed when the transaction in which they were created is committed.

Connection conn = ds.getConnection(user, passwd);
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY,
ResultSet.CLOSE_CURSORS_AT_COMMIT);

CODE EXAMPLE Creating a scrollable, insensitive, read-only result set with a cursor that is not holdable The Statement, PreparedStatement and CallableStatement interfaces also
provide setter and getter methods for each of these properties.