Value from last inserted row in DB

Question

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.

Answer 1

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);

Answer 2

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();

Or

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, I guess it can't be totally wrong. But, unfortunately, it is not really portable and it may not work on other platforms.

Answer 3

You should use ResultSet#getLong() instead. If in vain, try ResultSet#getRowId() and eventually cast it to oracle.sql.ROWID. If the returned hex string is actually the ID in hexadecimal flavor, then you can try converting it to decimal by Long#valueOf() or Integer#valueOf().

Long id = Long.valueOf(hexId, 16);

That said, Oracle's JDBC driver didn't support ResultSet#getGeneratedKeys() for a long time and is still somewhat troublesome with it. If you can't get that right, then you need to execute a SELECT CURRVAL(sequencename) on the same statement as you did the insert, or a new statement inside the same transaction, if it was a PreparedStatement. Basic example:

public void create(User user) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement statement = null;
    ResultSet generatedKeys = null;

    try {
        connection = daoFactory.getConnection();
        preparedStatement = connection.prepareStatement(SQL_INSERT);
        preparedStatement.setValue(1, user.getName());
        // Set more values here.
        int affectedRows = preparedStatement.executeUpdate();
        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }
        statement = connection.createStatement();
        generatedKeys = statement.executeQuery(SQL_CURRVAL);
        if (generatedKeys.next()) {
            user.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
    } finally {
        close(generatedKeys);
        close(statement);
        close(preparedStatement);
        close(connection);
    }
}

Oh, from your code example, the following line

stmt.RETURN_GENERATED_KEYS;

is entirely superfluous. Remove it.

You can find here another example which I posted before about getting the generated keys, it uses the normal getGeneratedKeys() approach.