|Using Result Sets and Cursors / Updatable Result Sets|
A scrollable, updatable result set maintains a cursor that can both scroll and update rows.
GemFire XD only supports Scrollable Insensitive Result Sets. To create a scrollable insensitive result set that is updatable, you create the statement 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 FOR UPDATE"); 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 FOR UPDATE"); uprs.last(); uprs.relative(-5); // moves to the 5th from the last row uprs.deleteRow();
Even if changes caused by others are not visible in the result set, SQL operations, including positioned updates, that access the current row will read and use the row data as it is in the database, and not as it is reflected in the result set.
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.
Because 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.
Conflicts may prevent the result set from doing updates/deletes. If a row is deleted after it was read into the result set, a scrollable insensitive result set gives a warning with SQLState 01001.
To avoid conflicts with other transactions, you may increase the transaction isolation level to repeatable read or serializable. This makes the transaction hold locks on the rows that have been read until the transaction commits.