Primary key from inserted row jdbc?

Question

Is there a cross database platform way to get the primary key of the record you have just inserted?

I noted that this answer says that you can get it by Calling SELECT LAST_INSERT_ID() and I think that you can call SELECT @@IDENTITY AS 'Identity'; is there a common way to do this accross databases in jdbc?

If not how would you suggest I implement this for a piece of code that could access any of SQL Server, MySQL and Oracle?

Answer 1

Copied from my code:

pInsertOid = connection.prepareStatement(INSERT_OID_SQL, Statement.RETURN_GENERATED_KEYS);

where pInsertOid is a prepared statement.

you can then obtain the key:

// fill in the prepared statement and
pInsertOid.executeUpdate();
ResultSet rs = pInsertOid.getGeneratedKeys();
if (rs.next()) {
  int newId = rs.getInt(1);
  oid.setId(newId);
}

Hope this gives you a good starting point.

Answer 2

for oracle, Hibernate uses NEXT_VALUE from a sequence if you have mapped a sequence for PKEY value generation.

Not sure what it does for MySQL or MS SQL server

Answer 3

Have you tried the Statement.executeUpdate() and Statement.getGeneratedKeys() methods? There is a developerWorks article that mentions the approach.

Also, in JDBC 4.0 Sun added the row_id feature that allows you to get a unique handle on a row. The feature is supported by Oracle and DB2. For sql server you will probably need a third party driver such as this one.

Good luck!

Answer 4

Spring provides some useful support for this operation and the reference guide seems to answer your question:

There is not a standard single way to create an appropriate PreparedStatement (which explains why the method signature is the way it is). An example that works on Oracle and may not work on other platforms is...

I've tested this example on MySQL and it works there too, but I can't speak for other platforms.