Forward only updatable result sets

A forward only updatable result set maintains a cursor which can only move in one direction (forward), and also update rows.

To create a forward only updatable result set, the statement has to be created with concurrency mode ResultSet.CONCUR_UPDATABLE and type ResultSet.TYPE_FORWARD_ONLY.

Note: The default type is ResultSet.TYPE_FORWARD_ONLY.

Example of using ResultSet.updateXXX() + ResultSet.updateRow() to update a row:

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

  while (uprs.next()) {
      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_FORWARD_ONLY, 
                                        ResultSet.CONCUR_UPDATABLE);
  ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNAME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE");

  while (uprs.next()) {
      if (uprs.getInt("WORKDEPT")==300) {
         uprs.deleteRow();
      }
  }

Visibility of changes

After an update or delete is made on a forward only result set, the result set's cursor is no longer on the row just updated or deleted, but immediately before the next row in the result set (it is necessary to move to the next row before any further row operations are allowed). This means that changes made by ResultSet.updateRow() and ResultSet.deleteRow() are never visible.
If a row has been inserted, i.e using ResultSet.insertRow() it may be visible in a forward only result set.

Conflicting operations

The current row of the result set cannot be changed by other transactions, since it will be locked with an update lock. Result sets held open after a commit have to move to the next row before allowing any operations on it.

Some conflicts may prevent the result set from doing updates/deletes:

If the current row is deleted by a statement in the same transaction, calls to ResultSet.updateRow() will cause an exception, since the cursor is no longer positioned on a valid row.