2

Possible Duplicate:
PLSQL JDBC: How to get last row ID?

I have implemented a trigger and a sequence for auto incrementing PK, I'm using Oracle 10g as database. Now I want to INSERT something using JAVA, but I need to save the incremented PK in a variable right after the INSERT. I tried this:

PreparedStatement pstmt = connection.prepareStatement("INSERT INTO sometable 
                                                       VALUES(?, ?)", 
                                                      Statement.RETURN_GENERATED_KEYS);
pstmt.setInt(1, 5);
pstmt.setString(2, "Username");
pstmt.executeUpdate();
ResultSet resultSet = pstmt.getGeneratedKeys();

But it doesn't work.

Community
  • 1
  • 1
  • i'm pretty sure you'll need to execute that statement before trying to get the generated keys. – Mat May 21 '11 at 18:37
  • Sorry it was mistake, I forgot to write the line in my question here. In my code the statement is already executed and it doesn't work. –  May 21 '11 at 18:44

2 Answers2

1

You need to specify the possible generated keys that you want to retrieve.

PreparedStatement  pstmt = conn.prepareStatement(sql,new String [] {"ID_ORDER"});

Note that the column name is case-sensitive. Finally a JDBC 3.0 driver and Oracle 10g R2 or better are required.

You can check if your current installation supports this mechanism by examining the DatabaseMetaData :

DatabaseMetaData metaData =  conn.getMetaData();
log("SupportsGetGeneratedKeys?="+metaData.supportsGetGeneratedKeys());
sideshowbarker
  • 81,827
  • 26
  • 193
  • 197
RealHowTo
  • 34,977
  • 11
  • 70
  • 85
0

Is customer.getID() a candidate key of the table? If so, you can run a SELECT after the INSERT to find the value of the generated PK. Alternatively, you can get rid of the trigger, get the next value of the SEQUENCE from the DB in Java, and use that as the PK for the INSERT. The drawback of this approach is that inserts from other applications will have to do the same too.

Binil Thomas
  • 13,699
  • 10
  • 57
  • 70