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?
If you insert records into a table which contains auto-incremented column, using a Statement or, PreparedStatement objects. You can retrieve the values of that particular column, generated by them object using the getGeneratedKeys() method.
JDBC's auto-generated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. Derby supports the auto-increment feature, which allows users to create columns in tables for which the database system automatically assigns increasing integer values.
RETURN_GENERATED_KEYS parameter, the data type of the automatically generated keys in the ResultSet is DECIMAL, regardless of the data type of the corresponding column.
The @GeneratedValue annotation is used to specify that an entity will use a generated primary key. The @TableGenerator annotation is used in conjunction with @GeneratedValue 's strategy=TABLE element. That is, the strategy used to generate the primary keys is to use a table in the database.
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.
extraneon's answer, although correct, doesn't work for Oracle.
The way you do this for Oracle is:
String key[] = {"ID"}; //put the name of the primary key column ps = con.prepareStatement(insertQuery, key); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { generatedKey = rs.getLong(1); }
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With