Autogenerated Keys with Oracle JDBC Driver

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. 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 ResultSetobject 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 sample code –
First a simple account table for demonstration

CREATE TABLE acc( acc_id NUMBER(16),
                  acc_name VARCHAR2(30) NOT NULL,
                  acc_balance NUMBER DEFAULT 0 NOT NULL,
                  CONSTRAINT acc_pk PRIMARY KEY (acc_id));

Then we create a sequence and a trigger which will populate the primary key

CREATE SEQUENCE acc_id_seq START WITH 2000;
CREATE OR REPLACE TRIGGER acc_pk_trg
  BEFORE INSERT ON acc FOR EACH ROW
BEGIN
    IF :NEW.acc_id IS NULL THEN
      SELECT acc_id_seq.NEXTVAL INTO :NEW.acc_id FROM DUAL;
    END IF;
END;

and then a jdbc sample code which executes an insert in the accounts table

        statement = connection.createStatement();
                int rowCount = statement.executeUpdate("INSERT INTO ACCOUNT(ACC_NAME) VALUES('Amit')",
                             new String[] {"ACC_ID", "BALANCE"});
                logger.debug("Row Count : {}", rowCount);
                resultSet = statement.getGeneratedKeys();
                while(resultSet.next()) {
                    logger.debug("Account Id : {}", resultSet.getInt(1));
                    logger.debug("Account Balance : {}", resultSet.getInt(2));
                }

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, Oracle returns the ROWID pseudo column value. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns. ROWID is the fastest way to access a single row in a table because there is no searching involved when you directly specify the row address.

Note that the OCI/Thin jdbc driver does not support retrieving the column values from the result set using column names. This is an unsupported feature in oracle jdbc driver version 11.2.0.1.0.