Retrieving auto-generated keys for an INSERT 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 an INSERT statement.

Procedure

To retrieve automatically generated keys that are generated by an INSERT statement, you need to perform these steps:

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 insert rows, invoke one of these forms of the Connection.prepareStatement method to create a PreparedStatement object:
  The following form is valid for a table on any data source that supports identity columns.
  
  Restriction: For IBM Data Server Driver for JDBC and SQLJ version 3.57 or later, the following form is not valid for inserting rows into a view on a Db2 for z/OS® data server.
```
Connection.prepareStatement(sql-statement, 
  Statement.RETURN_GENERATED_KEYS);
```
  If the data server is Db2 for z/OS, the following forms are valid only if the data server supports SELECT FROM INSERT statements. With the first form, you specify the names of the columns for which you want automatically generated keys. With the second form, you specify the positions in the table of the columns for which you want automatically generated keys.
```
Connection.prepareStatement(sql-statement, String [] columnNames);
Connection.prepareStatement(sql-statement, int [] columnIndexes);
```
- If you use the Statement.executeUpdate method to insert rows, invoke one of these forms of the Statement.executeUpdate method:
  The following form is valid for a table on any data source that supports identity columns.
  
  Restriction: For IBM Data Server Driver for JDBC and SQLJ version 3.57 or later, the following form is not valid for inserting rows into a view on a Db2 for z/OS data server.
```
Statement.executeUpdate(sql-statement, Statement.RETURN_GENERATED_KEYS);
```
  If the data server is Db2 for z/OS, the following forms are valid only if the data server supports SELECT FROM INSERT statements. With the first form, you specify the names of the columns for which you want automatically generated keys. With the second form, you specify the positions in the table of the columns for which you want automatically generated keys.
```
Statement.executeUpdate(sql-statement, String [] columnNames);
Statement.executeUpdate(sql-statement, int [] columnIndexes);
```
Invoke the PreparedStatement.getGeneratedKeys method or the Statement.getGeneratedKeys method to retrieve a ResultSet object that contains the automatically generated key values.
If you include the Statement.RETURN_GENERATED_KEYS parameter, the data type of the automatically generated keys in the ResultSet is DECIMAL, regardless of the data type of the corresponding column.

Example

The following code creates a table with an identity column, inserts a row into the table, and retrieves the automatically generated key value for the identity column. The numbers to the right of selected statements correspond to the previously described steps.

import java.sql.*;
import java.math.*;
import com.ibm.db2.jcc.*;

Connection con;
Statement stmt;
ResultSet rs;
java.math.BigDecimal iDColVar;
…
stmt = con.createStatement();         // Create a Statement object

stmt.executeUpdate( 
  "CREATE TABLE EMP_PHONE (EMPNO CHAR(6), PHONENO CHAR(4), " +
    "IDENTCOL INTEGER GENERATED ALWAYS AS IDENTITY)"); 
                                      // Create table with identity column
stmt.executeUpdate("INSERT INTO EMP_PHONE (EMPNO, PHONENO) " +            1 
  "VALUES ('000010', '5555')",        // Insert a row
  Statement.RETURN_GENERATED_KEYS);   // Indicate you want automatically 
                                      // generated keys
rs = stmt.getGeneratedKeys();         // Retrieve the automatically       2 
                                      // generated key value in a ResultSet.
                                      // Only one row is returned.
                                      // Create ResultSet for query
while (rs.next()) {
  java.math.BigDecimal idColVar = rs.getBigDecimal(1);     
                                      // Get automatically generated key 
                                      // value
  System.out.println("automatically generated key value = " + idColVar);
}
rs.close();                           // Close ResultSet
stmt.close();                         // Close Statement

With any version of the IBM Data Server Driver for JDBC and SQLJ, you can retrieve the most recently assigned value of an identity column by explicitly executing the IDENTITY_VAL_LOCAL built-in function. Execute code similar to this:

String idntVal;
Connection con;
Statement stmt;
ResultSet rs;
…
stmt = con.createStatement();     // Create a Statement object
rs = stmt.executeQuery("SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1");
                                  // Get the result table from the query.
                                  // This is a single row with the most
                                  // recent identity column value.
while (rs.next()) {               // Position the cursor                
 idntVal = rs.getString(1);       // Retrieve column value
 System.out.println("Identity column value = " + idntVal);
                                  // Print the column value
}
rs.close();                       // Close the ResultSet                
stmt.close();                     // Close the Statement