Scrollable updatable result sets

A scrollable updatable result set maintains a cursor which can both scroll and update rows.

Derby only supports scrollable insensitive result sets. To create a scrollable insensitive result set which is updatable, the statement has to be created with concurrency mode ResultSet.CONCUR_UPDATABLE and type ResultSet.TYPE_SCROLL_INSENSITIVE.

Example of using result set update methods to update a row:

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE");

  uprs.absolute(5); // update the fifth row
  int newBonus = uprs.getInt("BONUS") + 100;
  uprs.updateInt("BONUS", newBonus);
  uprs.updateRow();

Example of using ResultSet.deleteRow() to delete a row:

  Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE");
  
  uprs.last();
  uprs.relative(-5); // moves to the 5th from the last row
  uprs.deleteRow(); 

Visibility of changes

Please be aware that even if changes caused by others are not visible in the result set, SQL operations, including positioned updates, which access the current row will read and use the row data as it is in the database, not as it is reflected in the result set.

Conflicting operations

A conflict may occur in scrollable insensitive result sets if a row is updated/deleted by another committed transaction, or if a row is updated by another statement in the same transaction. The row which the cursor is positioned on is locked, however once it moves to another row, the lock may be released depending on transaction isolation level. This means that rows in the scrollable insensitive result set may have been updated/deleted by other transactions after they were fetched.

Since the result set is insensitive, it will not detect the changes made by others. When doing updates using the result set, conflicting changes on the columns being changed will be overwritten.

Some conflicts may prevent the result set from doing updates/deletes: To avoid conflicts with other transactions, you may increase the transaction isolation level to repeatable read or serializable. This will make the transaction hold locks on the rows which have been read until it commits.
Note: When you use holdable result sets, be aware that the locks will be released on commit, and conflicts may occur regardless of isolation level. You should probably avoid using holdable result sets if your application relies on transactional behavior for the result set.