Autogenerated keys

JDBC's auto-generated 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 auto-increment feature, which allows users to create columns in tables for which the database system automatically assigns increasing integer values. Users can call the method Statement.getGeneratedKeys to retrieve the value of such a column. This method returns a ResultSet object with a column for the automatically generated key.

The Derby implementation of Statement.getGeneratedKeys returns meaningful results only if the last statement was a single-row insert statement. If it was a multi-row insert, Statement.getGeneratedKeys will return a result set with only a single row, even though it should return one row for each inserted row.

Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL.

Users can indicate that auto-generated columns should be made available for retrieval by passing one of the following values as a second argument to the Connection.prepareStatement, Statement.execute, or Statement.executeUpdate methods:

Example

Assume that we have a table TABLE1 defined as follows:

CREATE TABLE TABLE1 (C11 int, C12 int GENERATED ALWAYS AS IDENTITY)

The following three code fragments will all do the same thing: that is, they will create a ResultSet that contains the value of C12 that is inserted into TABLE1.

Code fragment 1:

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

Code fragment 2:

Statement stmt = conn.createStatement(); 
String [] colNames = new String [] { "C12" };
stmt.execute(
    "INSERT INTO TABLE1 (C11) VALUES (1)",
    colNames);
ResultSet rs = stmt.getGeneratedKeys();

Code fragment 3:

Statement stmt = conn.createStatement(); 
int [] colIndexes = new int [] { 2 };
stmt.execute(
    "INSERT INTO TABLE1 (C11) VALUES (1)",
    colIndexes);
ResultSet rs = stmt.getGeneratedKeys();

If there is no indication that auto-generated columns should be made available for retrieval, a call to Statement.getGeneratedKeys will return a null ResultSet.

Related reference
ResultSet objects