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. sql-statement must be a single-row INSERT statement.
  
  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);
```
  The following forms are valid only if the data source supports SELECT FROM INSERT statements. sql-statement can be a single-row INSERT statement or a multiple-row INSERT statement. 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. sql-statement must be a single-row INSERT statement.
  
  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);
```
  The following forms are valid only if the data source supports SELECT FROM INSERT statements. sql-statement can be a single-row INSERT statement or a multiple-row INSERT statement. 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

The following code creates a table with an identity column, inserts two rows into the table using a multiple-row INSERT statement, and retrieves the automatically generated key values 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;
…
stmt = con.createStatement();

stmt.executeUpdate(                                                      
  "CREATE TABLE EMP_PHONE (EMPNO CHAR(6), PHONENO CHAR(4), " +
  "IDENTCOL INTEGER GENERATED ALWAYS AS IDENTITY)");
                                      // Create table with identity column
String[] id_col = {"IDENTCOL"};
int updateCount =                                                         1  
  stmt.executeUpdate("INSERT INTO EMP_PHONE (EMPNO, PHONENO)" +
  "VALUES ('000010', '5555'), ('000020', '5556')", id_col);
                                      // Insert two rows
                                      // Indicate you want automatically
                                      // generated keys
rs = stmt.getGeneratedKeys();         // Retrieve the automatically       2 
                                      // generated key values in a ResultSet.
                                      // Two rows are returned.
                                      // Create ResultSet for query
while (rs.next()) {
 int idColVar = rs.getInt(1);     
                                      // Get automatically generated key
                                      // values
 System.out.println("automatically generated key value = " + idColVar);
}
stmt.close();
con.close();