Autogenerated Keys

JDBC 3.0's autogenerated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. Derby supports the autoincrement feature, which allows users to create columns in tables for which the database system automatically assigns increasing integer values. (See the Derby Reference Manual for more information.) In JDBC 3.0, the method Statement.getGeneratedKeys can be called to retrieve the value of such a column. This method returns a ResultSet object with a column for each automatically generated key. Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that can be used to determine the number, type, and properties of the generated keys. A flag indicating that any auto-generated columns should be returned is passed to the methods execute, executeUpdate, or prepareStatement when the statement is executed or prepared.

Here's an example that returns a ResultSet with values for auto-generated columns in TABLE1:

Statement stmt = conn.createStatement();
int rows = stmt.executeUpdate("INSERT INTO TABLE1 (C11, C12) VALUES (1,1)", Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();

Additional methods allow you to specify the ordinals or names of the specific columns to be returned. An exception is thrown for invalid column or position names.

There are three ways of using Autogenerated Keys for insert statements. You can:

• Pass the flag Statement.RETURN_GENERATED_KEYS to execute or executeUpdate method.
• Send an array of column names to execute or executeUpdate, so only those column's values are returned by getGeneratedKeys() resultset.
• Send an array of column indexes to execute or executeUpdate. This array is an index of columns for the target table.

If the Statement.RETURN_GENERATED_KEYS flag is passed to the execute or executeUpdate method, rather than the column positions/names list, Derby returns a ResultSet containing columns with default values (this includes autoincrement column). To obtain a specific column, pass the column positions/names array. If Statement.getGeneratedKeys is executed for a non-insert statement, an exception is thrown.

The key indexes array in AutoGeneratedKey is an index of columns into the target table. You can send an array of column indexes to execute or executeUpdate. This array is an index of columns into the target table. For example:

create table t1(c11 int, c12 int);
int[ ] colIndexes = new int[1];
colIndexes[0] = 1;
statment.execute("insert into t1(c12, c11) values (2,1)",colIndexes);
s.getGeneratedKeys();
--- will return a resultset with column c11.

See the Derby Developer's Guide for more information about arrays.