java getting SQL next sequence number of the record before insertion

Question

I have TestingID variable and a sql string as follows in my java code. the sql string will later be used for prepareStatement.

 int TestingID; 

 String sqlInsert = "INSERT INTO TESTING VALUES(TESTING_SEQ.NEXTVAL, ?, ?)";  
 ...  

 MethodA(TestingID);     //passing TestingID to MethodA

I need to get the next sequence value of the newly inserted record into the TestingID so that I can use it in another method as shown above.

Answer 1

By using that approach, you should query first for the new identity value (I see you're using sequences),. This can be done by issuing a select.

// This example is for Oracle

String sqlIdentifier = "select TESTING_SEQ.NEXTVAL from dual";
PreparedStatement pst = conn.prepareStatement(sqlIdentifier);
synchronized( this ) {
   ResultSet rs = pst.executeQuery();
   if(rs.next())
     long myId = rs.getLong(1);

After that, pass it to the preparedStatement as an argument.

...
String sqlInsert = "INSERT INTO TESTING VALUES(?, ?, ?)";
PreparedStatement pst = conn.prepareStaetment(sqlInsert);
pst.setLong(1, myId);
...

From that point, you will always have your sequence number.

These are not functional examples (no catch or finally, etc), but will give you an idea of how to do it ;)