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:
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();