getGeneratedKeys() Problem

Here is a small write-up which should help those who still write plain Java JDBC code. I know we have some wonderful persistence frameworks like Hibernate that make ones life comfortable but the reality is we still have to deal with plain old JDBC apis. If you are poor chap like me, below code should make your life easy.

Problem statement:

I just inserted a record in Oracle database using Java JDBC. The primary key column was auto populated by a sequence value. How should I get the last inserted records auto generated primary key?

The solution should be getGeneratedKeys(). This method was added in JDBC 3.0 and it should be used to get last auto generated key value.

See code snippet below:

PreparedStatement prepareStatement = connection.prepareStatement("insert...",         new String[] { "your_primary_key_column_name" });   prepareStatement.executeUpdate();   ResultSet generatedKeys = prepareStatement.getGeneratedKeys(); if (null != generatedKeys && generatedKeys.next()) {      Long primaryKey = generatedKeys.getLong(1); }

Full solution

We have a database table called STUDENTS. We also have an oracle sequence calledSTUDENT_SEQ that we uses to generate primary key for STUDENTS table.

CREATE TABLE STUDENTS (    STUDENT_ID   NUMBER NOT NULL PRIMARY KEY,    NAME         VARCHAR2 (50 BYTE),    EMAIL        VARCHAR2 (50 BYTE),    BIRTH_DATE   DATE );     CREATE SEQUENCE STUDENT_SEQ    START WITH 0    MAXVALUE 9999999999999999999999999999    MINVALUE 0;

In Java, we use plain JDBC calls to insert a record in STUDENTS table. We uses sequenceSTUDENT_SEQ to generate primary key. Once the record is inserted, we want the last inserted primary value.

String QUERY = "INSERT INTO students "+                "  VALUES (student_seq.NEXTVAL,"+                "         'Harry', 'harry@hogwarts.edu', '31-July-1980')";   // load oracle driver Class.forName("oracle.jdbc.driver.OracleDriver");   // get database connection from connection string Connection connection = DriverManager.getConnection(         "jdbc:oracle:thin:@localhost:1521:sample", "scott", "tiger");   // prepare statement to execute insert query // note the 2nd argument passed to prepareStatement() method // pass name of primary key column, in this case student_id is // generated from sequence PreparedStatement ps = connection.prepareStatement(QUERY,         new String[] { "student_id" });   // local variable to hold auto generated student id Long studentId = null;   // execute the insert statement, if success get the primary key value if (ps.executeUpdate() > 0) {       // getGeneratedKeys() returns result set of keys that were auto     // generated     // in our case student_id column     ResultSet generatedKeys = ps.getGeneratedKeys();       // if resultset has data, get the primary key value     // of last inserted record     if (null != generatedKeys && generatedKeys.next()) {           // voila! we got student id which was generated from sequence         studentId = generatedKeys.getLong(1);     }   }

The above code is filled with comments and is pretty self explanatory. Finally we have last inserted value in studentId variable.