Retrieving auto-generated keys for an UPDATE, DELETE, or MERGE statement

With the IBM® Data Server Driver for JDBC and SQLJ, you can use JDBC methods to retrieve the keys that are automatically generated when you execute a searched UPDATE, searched DELETE, or MERGE statement.

Procedure

To retrieve automatically generated keys that are generated by an UPDATE, DELETE, or MERGE statement, you need to perform these steps:

  1. Construct a String array that contains the names of the columns from which you want to return automatically generated keys.

    The array must be an array of column names, and not column indexes.

  2. Set the autocommit mode for the connection to false.
  3. Use one of the following methods to indicate that you want to return automatically generated keys:
    • If you plan to use the PreparedStatement.executeUpdate method to update, delete, or merge rows, invoke this form of the Connection.prepareStatement method to create a PreparedStatement object: 
      Connection.prepareStatement(sql-statement, String [] columnNames);
    • If you use the Statement.executeUpdate method to update, delete, or merge rows, invoke this form of the Statement.executeUpdate method:
      Statement.executeUpdate(sql-statement, String [] columnNames);
  4. Invoke the PreparedStatement.getGeneratedKeys method or the Statement.getGeneratedKeys method to retrieve a ResultSet object that contains the automatically generated key values.

Example

Suppose that a table is defined like this and has thirty rows:

CREATE TABLE EMP_BONUS 
  (EMPNO CHAR(6), 
   BONUS DECIMAL(9,2))
The following code names the EMPNO column as an automatically generated key, updates the thirty rows in the EMP_BONUS table, and retrieves the values of EMPNO for the updated rows. The numbers to the right of selected statements correspond to the previously described steps. 
import java.sql.*;
…
Connection conn;
…
String[] agkNames = {"EMPNO"};               1 
int updateCount = 0;
conn.setAutoCommit(false);                   2 
PreparedStatement ps =                       3 
 conn.prepareStatement(“UPDATE EMP_BONUS SET BONUS = " +
 “ BONUS + 300.00”,agkNames);
updateCount = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();        4 
while (rs.next()) {
 String agkEmpNo = rs.getString(1);
                // Get automatically generated key value
 System.out.println("Automatically generated key value = " + agkEmpNo);
}
ps.close();
conn.close();