Value from last inserted row in DB

Asked 2009-12-29T19:57:31.047

Is there some way to get a value from the last inserted row?

I am inserting a row where the PK will automatically increase due to sequence created, and I would like to get this sequence number. Only the PK is guaranteed to be unique in the table.

I am using Java with a JDBC and Oracle.

I forgot to add that I would like to retrieve this value using the resultset below. (I have tried this with mysql and it worked successfully, but I had to switch over to Oracle and now I get a string representation of the ID and not the actually sequence number)

Statement stmt = conn.createStatement();
stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
stmt.RETURN_GENERATED_KEYS;
ResultSet rs = stmt.getGeneratedKeys();
if(rs.next()){
   log.info("Successful insert");
   id = rs.getString(1);
}

The above snippet would return the column int value stored in a mysql table. But since I have switched over to Oracle, the value returned is now a strange string value.

Edit Report

2 Answers

What you're trying to do is take advantage of the RETURNING clause. Let's setup an example table and sequence:

CREATE TABLE "TEST" 
( "ID" NUMBER NOT NULL ENABLE, 
 "NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, 
  CONSTRAINT "PK_TEST" PRIMARY KEY ("ID")
  );

CREATE SEQUENCE SEQ_TEST;

Now, your Java code should look like this:

String insertSql = "BEGIN INSERT INTO TEST (ID, NAME) VALUES (SEQ_TEST.NEXTVAL(), ?) RETURNING ID INTO ?; END;";
java.sql.CallableStatement stmt = conn.prepareCall(insertSql);
stmt.setString(1, "John Smith");
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.execute();
int id = stmt.getInt(2);

answered 2009-12-29T21:14:30.877

This is not consistent with other databases but, when using Oracle, getGeneratedKeys() returns the ROWID for the inserted row when using Statement.RETURN_GENERATEDKEYS. So you need to use the oracle.sql.ROWID proprietary type to "read" it:

Statement stmt = connection.createStatement();
stmt.executeUpdate(insertCmd, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
oracle.sql.ROWID rid = (oracle.sql.ROWID) rs.getObject(1);

But this won't give you the generated ID of the PK. When working with Oracle, you should either use the method executeUpdate(String sql, int[] columnIndexes) or executeUpdate(String sql, String[] columnNames) instead of executeUpdate(String sql, int autoGeneratedKeys) to get the generated sequence value. Something like this (adapt the value to match the index or the name of your primary key column):

stmt.executeUpdate(INSERT_SQL, new int[] {1});
ResultSet rs = stmt.getGeneratedKeys();

stmt.executeUpdate(INSERT_SQL, new String[] {"ID"});
ResultSet rs = stmt.getGeneratedKeys();

While digging a bit more on this, it appears that this approach is shown in the Spring documentation (as mentioned here) so, well

answered 2009-12-29T20:49:52.987

Value from last inserted row in DB

2 Answers

Your Answer