Extended updatable result set example

The following code example shows how to program updatable result sets.

Connection conn = DriverManager.getConnection("jdbc:derby:sample");
conn.setAutoCommit(false);

// Create the statement with concurrency mode CONCUR_UPDATABLE
// to allow result sets to be updatable
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                                      ResultSet.CONCUR_UPDATABLE,
                                      ResultSet.CLOSE_CURSORS_AT_COMMIT);
// Updatable statements have some requirements
// for example, select must be on a single table
ResultSet uprs = stmt.executeQuery(
    "SELECT FIRSTNME, LASTNAME, WORKDEPT, BONUS " +
    "FROM EMPLOYEE FOR UPDATE of BONUS"); // Only bonus can be updated

String theDept="E21";

while (uprs.next()) {
    String firstnme = uprs.getString("FIRSTNME");
    String lastName = uprs.getString("LASTNAME");
    String workDept = uprs.getString("WORKDEPT");
    BigDecimal bonus = uprs.getBigDecimal("BONUS");
    if (workDept.equals(theDept)) {
        // if the current row meets our criteria,
        // update the updatable column in the row
        uprs.updateBigDecimal("BONUS", bonus.add(BigDecimal.valueOf(250L)));
        uprs.updateRow();
        System.out.println("Updating bonus for employee:" +
                           firstnme + lastName);
    } 
}
conn.commit(); // commit the transaction
// close object 
uprs.close();
stmt.close();
// Close connection if the application does not need it any more
conn.close();
Related concepts
Requirements for updatable result sets
Related tasks
Naming or accessing the name of a cursor