Oracle Java jdbc:get Primary Key of Inserted Record

Here are 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 this make ones life comfortable and the reality is we The still has to deal with the plain old JDBC APIs. If You is 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 is auto populated by a sequence value. How should I get the last inserted records auto generated primary key?

Solution:

The solution should be Getgeneratedkeys (). This method is added in JDBC 3.0 and it should is used to get the 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);}

The above code should give us auto generated primary key value. The one thing to note here is Method Preparestatement (). We passed arguments first the Insert query string and second an array of column name. The column name should is the primary key column name of table where you inserting the record.

Check below source code to see complete solution.

Full Solution

We have a database table called STUDENTS . We also has an Oracle sequence called, STUDENT_SEQ we uses to generate primary key for STUDENTS table.

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

In Java, we use the plain JDBC calls to insert a record in STUDENTS table. We uses sequence to STUDENT_SEQ 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‘, ‘[email protected]‘, ‘31-July-1980‘)";// load oracle driverClass.forName("oracle.jdbc.driver.OracleDriver");// get database connection from connection stringConnection 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 sequencePreparedStatement ps = connection.prepareStatement(QUERY,        new String[] { "student_id" });// local variable to hold auto generated student idLong studentId = null;// execute the insert statement, if success get the primary key valueif (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 are pretty self explanatory. Finally we have the last inserted value in studentId variable.

The getGeneratedKeys() method is key here. It gives us the result set of all auto generated key values. In the We case as we has only one auto generated value (for student_id column) we get a single record in this result set.

Oracle Java jdbc:get Primary Key of Inserted Record