Monday, September 18, 2006

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:

5 comments:

  1. sql statement for creating sequence number used in ur code is wrong.the correct one is

    CREATE SEQUENCE customers_seq
    START WITH 1000
    INCREMENT BY 1

    ReplyDelete
  2. The "INCREMENT BY " clause is optional, and it defaults 1. There is no need to explicitly specify "INCREMENT BY". The Statement used here is correct.
    Refer to http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_6014.htm#sthref5327

    ReplyDelete
  3. how to display the value of nextval

    ReplyDelete
  4. Hi,

    what do you mean by "The Oracle server-side internal driver does not support the retrieval of auto-generated keys feature." I downloaded the "thin" driver ojdbc14.jar from Oracle and this does not work. So which one is the correct driver to use ? Thanks.

    ReplyDelete
  5. Blogs are so informative where we get lots of information on any topic. Nice job keep it up!!
    _____________________________

    Teaching Dissertation

    ReplyDelete