Oracle JDBC: Automatic key generation and retrieval

Oracle provides the sequence utility to automatically generate unique primary keys. In your Oracle database, you must create a sequence table that will create the primary keys, as shown in the following example:

create sequence myOracleSequence
start with 1 
nomaxvalue;

This creates a sequences of primary key values, starting with 1, followed by 2, 3, and so forth. JDBC 3.0 introduces the retrieval of auto-generated keys feature that enables you to retrieve such generated values. In JDBC 3.0, the following interfaces are enhanced to support the retrieval of auto-generated keys feature:

• java.sql.DatabaseMetaData

  public boolean supportsGetGeneratedKeys();

  The method indicates whether retrieval of auto-generated keys is supported or not by the JDBC driver and the underlying data source.
• java.sql.Statement

  public boolean execute(String sql, int autoGeneratedKeys) throws SQLException;
  public boolean execute(String sql, int[] columnIndexes) throws SQLException;
  public boolean execute(String sql, String[] columnNames) throws SQLException;
  public boolean executeUpdate(String sql, int autoGeneratedKeys) throws SQLException;
  public boolean executeUpdate(String sql, int[] columnIndexes) throws SQLException;
  public boolean executeUpdate(String sql, String[] columnNames) throws SQLException;
  public ResultSet getGeneratedKeys() throws SQLException;

  These methods take a String object that contains a SQL statement. They also take either the flag, Statement.RETURN_GENERATED_KEYS, indicating whether any generated columns are to be returned, or an array of column names or indexes specifying the columns that should be returned. The getGeneratedKeys() method enables you to retrieve the auto-generated key fields. The auto-generated keys are returned as a ResultSet object.
  When the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns, since the column indices/names have not been specified. Therefore, when the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.
• java.sql.Connection

  public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException;
  public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException;
  public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException;

  These methods enable you to create a PreparedStatement object that is capable of returning auto-generated keys.

Note:

• This feature is supported only when INSERT statements are processed. Other data manipulation language (DML) statements are processed, but without retrieving auto-generated keys.
• The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature.
• This feature is only available since Oracle Database 10g Release 2.

Sample Code
The following code illustrates retrieval of auto-generated keys:

/** SQL statements for creating an ORDERS table and a sequence for generating the
  * ORDER_ID.
  *
  * CREATE TABLE ORDERS (ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ISBN NUMBER,
  * DESCRIPTION NCHAR(5))
  *
  * CREATE SEQUENCE SEQ01 INCREMENT BY 1 START WITH 1000
  */

...
String cols[] = {"ORDER_ID", "DESCRIPTION"};

// Create a PreparedStatement for inserting a row in to the ORDERS table.
OraclePreparedStatement pstmt = (OraclePreparedStatement) conn.prepareStatement
                                ("INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, ISBN,
                                DESCRIPTION) VALUES (SEQ01.NEXTVAL, 101, 966431502,
                                ?)", cols);

char c[] = {'a', '\u5185', 'b'};
String s = new String(c);
pstmt.setFormOfUse(1, OraclePreparedStatement.FORM_NCHAR);
pstmt.setString(1, s);
pstmt.executeUpdate();
ResultSet rset = pstmt.getGeneratedKeys();

References:

• Retrieval of Auto-Generated Keys
• Automatic Primary Key Generation