5 Result Set Enhancements

5.1     Scrolling

5.2     Result Set types

A scroll-insensitive result set is generally not sensitive to changes that are made while it is open. A scroll-insensitive result set provides a static view of the underlying data it contains. The membership, order, and column values of rows in a scroll-insensitive result set are typically fixed when the result set is created.

On the other hand, a scroll-sensitive result set is sensitive to changes that are made while it is open, and provides a `dynamic' view of the underlying data. For example, when using a scroll-sensitive result set, changes in the underlying column values of rows are visible. The membership and ordering of rows in the result set may be fixed- this is implementation defined.

5.3     Concurrency types

A result set that uses read-only concurrency does not allow updates of its contents. This can increase the overall level of concurrency between transactions, since any number of read-only locks may be held on a data item simultaneously.

A result set that is updatable allows updates and may use database write locks to mediate access to the same data item by different transactions. Since only a single write lock may be held at a time on a data item, this can reduce concurrency. Alternatively, an optimistic concurrency control scheme may be used if it is thought that conflicting accesses to data will be rare. Optimistic concurrency control implementations typically compare rows either by value or by a version number to determine if an update conflict has occurred.

5.4     Performance

5.5     Creating a result set

Connection con = DriverManager.getConnection(
	"jdbc:my_subprotocol:my_subname");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
	"SELECT emp_no, salary FROM employees");

The next example creates a scrollable result set that is updatable and sensitive to updates. Rows of data are requested to be fetched twenty-five at-a-time from the database.

Connection con = DriverManager.getConnection(
	"jdbc:my_subprotocol:my_subname");

Statement stmt = con.createStatement(
	ResultSet.TYPE_SCROLL_SENSITIVE,
	ResultSet.CONCUR_UPDATABLE);
stmt.setFetchSize(25);

ResultSet rs = stmt.executeQuery(
	"SELECT emp_no, salary FROM employees");

PreparedStatement pstmt = con.prepareStatement(
	"SELECT emp_no, salary FROM employees where emp_no = ?",
	ResultSet.TYPE_SCROLL_SENSITIVE, 
	ResultSet.CONCUR_UPDATABLE);

pstmt.setFetchSize(25);
pstmt.setString(1, "100010");
ResultSet rs = pstmt.executeQuery();

1. If an application asks for a scrollable result set type the driver should use a scrollable type that it supports, even if this differs from the exact type requested by the application.
2. If the application asks for a scrollable result set type and the driver does not support scrolling, then the driver should use a forward-only result set type.

In some instances, a JDBC driver may need to choose an alternate result set type or concurrency type for a ResultSet at statement execution time. For example, a SELECT statement that contains a join over multiple tables may not produce a ResultSet that is updatable. The JDBC driver should issue an SQLWarning in this case on the Statement, PreparedStatement, or CallableStatement that produces the ResultSet and choose an appropriate result set type or concurrency type as described above. An application may determine the actual result set type and concurrency type of a ResultSet by calling the ResultSet.getType() and getConcurrency() methods, respectively.

5.6     Updates

rs.first();
rs.updateString(1, "100020");
rs.updateFloat("salary", 10000.0f);
rs.updateRow();

The updates that an application makes must be discarded by a JDBC driver if the application moves the cursor from the current row before calling updateRow(). In addition, an application can call the ResultSet.cancelRowUpdates() method to explicitly cancel the updates that have been made to a row. The cancelRowUpdates() method must be called after calling updateXXX() and before calling updateRow(), otherwise it has no effect.

The following example illustrates deleting a row. The fifth row in the result set is deleted from the database.

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

The example below shows how a new row may be inserted into a result set. The JDBC API defines the concept of an insert row that is associated with each result set and is used as a staging area for creating the contents of a new row before it is inserted into the result set itself. The ResultSet.moveToInsertRow() method is used to position the result set's cursor on the insert row. The ResultSet.updateXXX() and ResultSet.getXXX() methods are used to update and retrieve individual column values from the insert row. The contents of the insert row is undefined immediately after calling ResultSet.moveToInsertRow() . In other words, the value returned by calling a ResultSet.getXXX() method is undefined after moveToInsertRow() is called until the value is set by calling ResultSet.updateXXX().

Calling ResultSet.updateXXX() while on the insert row does not update the underlying database or the result set. Once all of the column values are set in the insert row, ResultSet.insertRow() is called to update the result set and the database simultaneously. If a column is not given a value by calling updateXXX() while on the insert row, or a column is missing from the result set, then that column must allow a null value. Otherwise, calling insertRow() throws an SQLException.

rs.moveToInsertRow();
rs.updateString(1, "100050");
rs.updateFloat(2, 1000000.0f);
rs.insertRow();
rs.first();

Due to differences in database implementations, the JDBC API does not specify an exact set of SQL queries which must yield an updatable result set for JDBC drivers that support updatability. Developers can, however, generally expect queries which meet the following criteria to produce an updatable result set:

1. The query references only a single table in the database.
2. The query does not contain any join operations.
3. The query selects the primary key of the table it references.

4. The query selects all of the non-nullable columns in the underlying table.
5. The query selects all columns that don't have a default value.

5.7     Cursor movement examples

Iterating forward through a result set is done by calling the ResultSet.next() method, as with the JDBC 1.0 API. In addition, scrollable result sets-any result set whose type is not forward only-implement the method, beforeFirst(), which may be called to position the cursor before the first row in the result set.

The example below positions the cursor before the first row and then iterates forward through the contents of the result set. The getXXX() methods, which are JDBC 1.0 API methods, are used to retrieve column values.

rs.beforeFirst();
while ( rs.next()) {
	System.out.println(rs.getString("emp_no") + 
			   " " + rs.getFloat("salary"));
}

Of course, one may iterate backward through a scrollable result set as well, as is shown below.

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

After examining the ResultSet interface, the reader will no doubt recognize that there is more than one way to iterate through the rows of a scrollable result set. It pays to be careful, however, as is illustrated by the following example, which shows one alternative that is incorrect.

// incorrect!!!
while (!rs.isAfterLast()) {
	rs.relative(1);
	System.out.println(rs.getString("emp_no") + 
		 " " + rs.getFloat("salary"));
}

The code sample below fixes the problems in the previous example. Here a call to ResultSet.first() is used to distinguish the case of an empty result set from one which contains data. Since ResultSet.isAfterLast() is only called when the result set is non-empty the loop control works correctly, and ResultSet.relative(1) steps through the rows of the result set since ResultSet.first() initially positions the cursor on the first row.

if (rs.first()) {
	while (!rs.isAfterLast()) {
		System.out.println(rs.getString("emp_no") + 
			" " + rs.getFloat("salary"));
	    	rs.relative(1);
	}
}

5.8     Detecting and viewing changes

The different result set types-forward-only, scroll-insensitive, and scroll-sensitive- provided by the JDBC API vary greatly in their ability to make changes in the underlying data visible to an application. This aspect of result sets is particularly interesting for the result set types which support scrolling, since they allow a particular row to be visited multiple times while a result set is open.

5.8.1 Visibility of changes

con.setTransactionIsolation(TRANSACTION_READ_COMMITTED);

But what about changes made while a result set is open? Are they visible through the result set by, for example, calling ResultSet.getXXX()? Whether a particular result set exposes changes to its underlying data made by other transactions, other result sets that are part of the same transaction (We refer to these two types of changes collectively as `other's changes'.), or itself while the result set is open depends on the result set type.

5.8.2 Other's changes

Scroll-sensitive result sets lie at the opposite end of the spectrum. A scroll-sensitive result set makes all of the updates made by others that are visible to its enclosing transaction visible. Inserts and deletes may not be visible, however.

Let us define carefully what it means for updates to be visible. If an update made by another transaction affects where a row should appear in the result set-this is in effect a delete followed by an insert-the row may not move until the result set is reopened. If an update causes a row to fail to qualify for membership in a result set-this is in effect a delete-the row may remain visible until the result set is reopened. If a row is explicitly deleted by another transaction, a scroll-sensitive result set may maintain a placeholder for the row to permit logical fetching of rows by absolute position. Updated column values are always visible, however.

The DatabaseMetaData interface provides a way to determine the exact capabilities that are supported by a result set. For example, the new methods: othersUpdatesAreVisible , othersDeletesAreVisible, and othersInsertsAreVisible may be used for this purpose.

A forward-only result set is really a degenerate case of either a scroll-insensitive or scroll-sensitive result set- depending on how the DBMS evaluates the query that produces the result set. Most DBMSs have the ability to materialize query results incrementally for some queries. If a query result is materialized incrementally, then data values aren't actually retrieved until they are needed from the DBMS and the result set will behave like a sensitive result set. For some queries, however, incremental materialization isn't possible. For example, if the result set is sorted, the entire result set may need to be produced a priori before the first row in the result set is returned to the application by the DBMS. In this case a forward-only result set will behave like an insensitive result set.

For a TYPE_FORWARD_ONLY result set the othersUpdatesAreVisible, othersDeletesAreVisible , and othersInsertsAreVisible methods determine whether inserts, updates, and deletes are visible when the result set is materialized incrementally by the DBMS. If the result of a query is sorted then incremental materialization may not be possible and changes will not be visible, even if the methods above return true.

5.8.3 A result set's own changes

One's own updates are visible if an updated column value can be retrieved by calling getXXX() following a call to updateXXX(). Updates are not visible if getXXX() still returns the initial column value after updateXXX() is called. Similarly, an inserted row is visible if it appears in the result set following a call to insertRow(). An inserted row is not visible if it does not appear in the result set immediately after insertRow() is called-without closing and reopening the result set. Deletions are visible if deleted rows are either removed from the result set or if deleted rows leave a hole in the result set.

The following example, shows how an application may determine whether a TYPE_SCROLL_SENSITIVE result set can see its own updates.

DatabaseMetaData dmd;
...
if (dmd.ownUpdatesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE)) 
{
	// changes are visible
}

5.8.4 Detecting changes

The DatabaseMetaData interface provides methods that allow an application to determine whether a JDBC driver can detect changes for a particular result set type. For example,

boolean bool = dmd.deletesAreDetected(
	ResultSet.TYPE_SCROLL_SENSITIVE);

If deletesAreDetected returns true, then ResultSet.wasDeleted() can be used to detect `holes' in a TYPE_SCROLL_SENSITIVE result set.

5.9     Refetching a row

5.10     JDBC API compliance