The value from the last inserted row in the database

Question

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

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

I use Java with JDBC and Oracle.

I forgot to add that I would like to get this value using the result set below. (I tried this with mysql and it worked successfully, but I had to switch to Oracle and now I get the string representation of the identifier, not the 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 returns the value of the int column stored in the mysql table. But since I switched to Oracle, the return value is now a weird string value.

Answer 1

What you are trying to do is use the RETURNING clause. Let me set up 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; 

Your Java code should now 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 . Therefore, you need to use your own oracle.sql.ROWID type to "read":

 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 will not give you the generated PC ID. When working with Oracle, you must either use the executeUpdate(String sql, int[] columnIndexes) 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 corresponding to the index or primary key column name):

 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 around a bit more, it seems like this approach is shown in the Spring documentation (as mentioned here ), so I think it can't be completely wrong. But, unfortunately, it is not very portable and may not work on other platforms.

Answer 3

Instead, use ResultSet#getLong() . If in vain, try ResultSet#getRowId() and finally drop it at oracle.sql.ROWID . If the returned hex string is actually a hex identifier, you can try converting it to decimal using Long#valueOf() or Integer#valueOf() .

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

However, the Oracle JDBC driver has not supported ResultSet#getGeneratedKeys() for a long time and is still somewhat troublesome with it. If you cannot get this right, you need to execute SELECT CURRVAL(sequencename) in the same statement as in insert , or in a new expression inside the same transaction if it was 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 next line

 stmt.RETURN_GENERATED_KEYS; 

completely redundant. Take it away.

You can find here another example, which I published earlier about receiving generated keys, it uses the usual getGeneratedKeys() approach.