7.1.9 Retrieving Automatically Generated Keys

As explained in the section "Retrieving Automatically Generated Keys" on page 49 in the chapter on Statement objects, some DBMSs automatically generate unique keys for rows that are inserted into a table. As with Statement objects, keys automatically generated for rows that are inserted via PreparedStatement objects can be retrieved with methods added in the JDBC 3.0 API. First, the driver is notified that keys are to be made available for retrieval, and second, the keys are retrieved by calling the Satement method getGenerateddKeys. Because PreparedStatement objects are sent to the DBMS when they are created (so that they can be precompiled) , the driver gets its notification when a PreparedStatement object is created. This is in contrast to Statement objects, which are sent to the DBMS when they are executed.
The following code fragment creates the PreparedStatement object addAuthor and tells the driver to be prepared to return all keys that the DBMS generates automatically by passing the constant Statement.RETURN_GENERATED_KEYS to the method prepareStatement. The code then sets the three placeholder parameters with values and executes addAuthor, which returns a ResultSet object containing the generated keys, if there are any. Finally, it retrieves any keys in the ResultSet object and prints them as String objects.

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"(?, ?, ?, keyColumn)";
PreparedStatement addAuthor = con.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
addAuthor.setString(1, "Wordsworth");
addAuthor.setString(2, "William");
addAuthor.setString(3, "England");
int rows = addAuthor.executeUpdate();
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();
do {
for (int i = 1; i <= colCount; i++) {
String key = rs.getString(i);
System.out.println("key " + i + "is " + key);
}
}
while (rs.next();)
}
else {
System.out.println("There are no generated keys.");
}

Whereas the previous example shows how to notify the driver to return all automatically generated keys, the following lines of code show how to specify particular columns that the driver should be prepared to return. The first code fragment passes an array of column indexes to the method prepareStatement to specify which column(s) should be made available for retrieval.

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"?, ?, ?, keyColumn";
int [] colIndexes = {4};
PreparedStatement insertAuthor = con.prepareStatement(sql,
colIndexes);

The following code fragment passes an array of column names to the method prepareStatement to specify which column(s) should be made available for retrieval.

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"?, ?, ?, keyColumn";
String [] colNames = {"AUTHOR_ID"};
PreparedStatement insertAuthor = con.prepareStatement(sql,
colNames);