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 2.0 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 2.0 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:

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 2.0 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 compliance