Retrieving Automatically Generated Keys

Retrieving Automatically Generated Keys

Many DBMSs automatically generate a unique key field when a new row is inserted into a table. Methods and constants added in the JDBC 3.0 API make it possible to retrieve these keys, which is a two-step process. First the driver is alerted that it should make the keys available for retrieval. The second step is to access the generated keys by calling the Statement method getGeneratedKeys. The rest of this section explains these two steps more fully.

1. Step One - Tell the driver that it should make automatically generated keys available for retrieval. This is done when an SQL statement is sent to the DBMS, which for Statement objects, is when the statement is executed. Three new versions of the method executeUpdate and three new versions of the method execute signal the driver about making automatically generated keys available. These six new methods take two parameters, the first being in all cases an SQL INSERT statement. The second parameter is either a constant indicating whether to make all generated keys retrievable (Statement.RETURN_GENERATED_KEYS or Statement.NO_GENERATED_KEYS) or an array indicating which specific key columns should be made retrievable. The array elements are either the indexes of the columns to be returned or the names of the columns to be returned.
   
   Note that although it is possible to use the method execute for executing a DML (Data Manipulation Language) statement, this method is generally reserved for executing CallableStatement objects that produce multiple return values.
   
   • For a PreparedStatement object, the SQL statement is sent to the DBMS to be precompiled when the PreparedStatement object is created with one of the Connection.prepareStatement methods. Thus, the driver is notified about making automatically generated keys retrievable via these methods.
     
     
     
2. Step Two - After the driver has been notified about making automatically generated keys available for retrieval, the keys can be retrieved by calling the Statement method getGeneratedKeys. This method returns a ResultSet object, with each row being a generated key. If there are no automaticallty generated keys, the ResultSet object will be empty.
   The following code fragment creates a Statement object and signals the driver that it should be able to return any keys that are automatically generated as a result of executing the statement. The example then retrieves the keys that were generated and prints them out. If there are no generated keys, the printout says that there are none.
   

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";

int rows = stmt.executeUpdate(sql,
Statement.RETURN_GENERATED_KEYS);

ResultSet rs = stmt.getGeneratedKeys();


int rows = stmt.executeUpdate(sql,
Statement.RETURN_GENERATED_KEYS);

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."); }

Instead of telling the driver to make all automatically-generated keys available, it is possible to tell the driver to make particular columns retrievable. The following code fragment uses an array of column indexes (in this case, an array with one element) to indicate which columns with an automatically-generated key should be made available for retrieval.

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";
int [] indexes = {4};
int rows = stmt.executeUpdate(sql, indexes);

The following code fragment shows a third alternative-supplying an array of column names to indicate which ResultSet columns to make available. In this case, the driver is told to make the automatically-generated key in the column AUTHOR_ID retrievable.

String sql = "INSERT INTO AUTHORS (LAST, FIRST, HOME) VALUES " +
"'PARKER', 'DOROTHY', 'USA', keyColumn";
String [] keyColumn = {"AUTHOR_ID"};
int rows = stmt.executeUpdate(sql, keyColumn);

The above has been taken from http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/statement.html#1000569