Result Sets::Modifying ResultSet Objects

ResultSet objects with concurrency CONCUR_UPDATABLE can be updated using ResultSet methods. Columns can be updated, new rows can be inserted, and rows can be deleted using methods defined in the ResultSet interface.

Updating a Row

Updating a row in a ResultSet object is a two-phase process. First, the new value
for each column being updated is set, and then the change is applied to the row. The
row in the underlying data source is not updated until the second phase is
completed.

The ResultSet interface contains two update methods for each JDBC type, one
specifying the column to be updated as an index and one specifying the column
name as it appears in the select list. Column names supplied to updater methods are case insensitive. If a select list contains the same column more than once, the first instance of the column will be updated.

The method updateRow is used to apply all column changes to the current row. The
changes are not made to the row until updateRow has been called. The method cancelUpdates can be used to back out changes made to the row before the method updateRow is called.
CODE EXAMPLE shows the current row being updated to change the value of the column “author” to “Zamyatin, Evgenii Ivanovich”:

Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery(“select author from booklist " +
"where isbn = 140185852”);
rs.next();
rs.updateString(“author”, “Zamyatin, Evgenii Ivanovich”);
rs.updateRow();

CODE EXAMPLE Updating a row in a ResultSet object
The method DatabaseMetaData.ownUpdatesAreVisible(int type) returns
true if a ResultSet object of the specified type is able to see its own updates and
false otherwise.

A ResultSet object may be able to use the method rowUpdated to detect rows
that have had the method updateRow called on them. The method
DatabaseMetaData.updatesAreDetected(int type) returns true if a
ResultSet object of the specified type can determine if a row is updated using the
method rowUpdated and false otherwise.

Deleting a Row

A row in a ResultSet object can be deleted using the method deleteRow.
CODE EXAMPLE shows the fourth row of the ResultSet rsbeing deleted.

rs.absolute(4);
rs.deleteRow();

CODE EXAMPLE Deleting a row in a ResultSet object After the method deleteRow has been called, the current row is deleted in the underlying data source. This deletion is visible as a change in the open ResultSet object if the row is either removed or replaced by an empty or invalid row. If the deleted row is removed or replaced by an empty row, the method
DatabaseMetaData.ownDeletesAreVisible(int type) will return true. It returns false if the ResultSet object still contains the deleted row, which means that the deletion is not visible as a change to ResultSet objects of the given type. The method DatabaseMetaData.othersDeletesAreVisible(int type) checks whether deletions made by others (another transaction or another ResultSet object in the same transaction) are visible to ResultSet objects of the specified type. This method returns true if a row deleted by others is visible and false if it is not. If a ResultSet object can detect deletions, the ResultSet method rowDeleted returns true when the current row has been deleted and false when it has not.
However, rowDeleted also returns false if the ResultSet object cannot detect deletions. The method DatabaseMetaData.deletesAreDetected(int type) can be called to see whether a ResultSet object of the specified type can call the method rowDeleted to detect a deletion that is visible. The method deletesAreDetected returns false if a row deleted from the ResultSet object
is removed from it and true if the deleted row is replaced by an empty or invalid row.

In CODE EXAMPLE application code uses metadata to process a ResultSet object that may contain deleted rows.

if (dbmd.ownDeletesAreVisible(ResultSet.TYPE_SCROLL_INSENSITIVE) &&
dbmd.deletesAreDetected(ResultSet.TYPE_SCROLL_INSENSITIVE)) {
while (rs.next) {
if (rs.rowDeleted()) {
continue;
} else {
// process row
...
}
}
} else {
// if up-to-date data is needed, it is better to close this
// ResultSet object and reexecute the query to get an updated
// ResultSet object
...
rs.close();
break;
}

CODE EXAMPLE Processing a ResultSet object containing deleted rowsNote –

CODE EXAMPLE does not cover the case where ownDeletesAreVisible returns true and deletesAreDetected returns false. This will cause anSQLException to be thrown when the cursor is positioned on a deleted row, so an implementation with these characteristics requires that an application handle the exception. Such an implementation does not appear to be a very likely.
After the method deleteRow has been called, the cursor will be positioned before the next valid row. If the deleted row is the last row, the cursor will be positioned after the last row.

Inserting a Row

New rows may be inserted using the ResultSet interface. New rows are
constructed in a special insert row. The steps to insert a new row are:
1. Move the cursor to the insert row
2. Set the values for the columns of the row using the ResultSet interface update
methods
3. Insert the new row into the ResultSet object

CODE EXAMPLE shows the steps necessary to insert a new row into the table
booklist.

// select all the columns from the table booklist
ResultSet rs = stmt.executeQuery(“select author, title, isbn " +
"from booklist”);
rs.moveToInsertRow();
// set values for each column
rs.updateString(1, “Huxley, Aldous”);
rs.updateString(2, “Doors of Perception and Heaven and Hell”);
rs.updateLong(3, 60900075);
// insert the row
rs.insertRow();
// move the cursor back to its position in the result set
Chapter 14 Result Sets 125
rs.moveToCurrentRow();

CODE EXAMPLE Inserting a new row into a ResultSet object Each column in the insert row that does not allow null as a value and does not have a default value must be given a value using the approriate update method. If this is not the case, the method insertRow will throw an SQLException. The method DatabaseMetaData.ownInsertsAreVisible(int type) will
return true if newly inserted rows can be seen in result sets of the specified type. If the ResultSet objects of the specified type can identify newly inserted rows, the method DatabaseMetaData.insertsAreDetected(int type) will return true. This indicates that the inserted rows are visible to the ResultSet object.

Positioned Updates and Deletes

JDBC drivers or DBMSs that do not support performing updates via the ResultSet
interface may support positioned updates and deletes via SQL commands. This
method of updating a row relies on using named cursors to allow multiple
statements to act on a single result set. CODE EXAMPLE 14-9 shows the use of the
method setCursorName to associate a cursor with a Statement object and then
the use of the method getCursorName to retrieve the name for use by a second
Statement object.

Statement stmt1 = conn.createStatement();
stmt1.setCursorName(“CURSOR1”);
ResultSet rs = stmt1.executeQuery(“select author, title, isbn " +
"from booklist for update of author”);
// move to the row we want to update
while ( ... ) {
rs.next()
}
String cursorName = rs.getCursorName();
Statement stmt2 = conn.createStatement();
// now update the row
int updateCount = stmt2.executeUpdate("update booklist " +
"set author = ’Zamyatin, Evgenii Ivanovich’ " +
"where current of “ + cursorName);

CODE EXAMPLE Updating a row using positioned updates The syntax of both the select statement and the update statement may vary among driver or DBMS implementations.
The method DatabaseMetaData.supportsPositionedUpdates returns true if the JDBC driver and DBMS support this facility.

Closing a ResultSet Object

A ResultSet object is automatically closed when the Statement object that
produced it is closed. The method close can be called explicitly to close a
ResultSet object, thereby releasing any external resources and making it
immediately available for garbage collection.