Java – last inserted value in DB

There is already an answer to this question: > how to get a value from the last inserted row? 14

When I insert a row, PK will increase automatically due to sequence creation. I want to get this sequence number Only PK is guaranteed to be unique in the table

I am using java with JDBC and Oracle

I forgot to add that I want to use the following result set to retrieve this value (I have tried this MySQL and it works successfully, but I have to switch to Oracle. Now I get a string ID instead of the actual serial 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 code snippet will return the column int value stored in the MySQL table However, since I have switched to Oracle, the returned value is now a strange string value

Solution

What you want to do is use the returning clause Let's set up a sample 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);