Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Primary key from inserted row jdbc?

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?

like image 866
Omar Kooheji Avatar asked Oct 14 '08 16:10

Omar Kooheji


People also ask

How can we get primary key value from inserted query using JDBC?

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.

What is generated keys in JDBC?

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.

What is Return_generated_keys?

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.

How do you create a primary key in Java?

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.


2 Answers

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.

like image 132
extraneon Avatar answered Oct 01 '22 11:10

extraneon


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); } 
like image 31
atripathi Avatar answered Oct 01 '22 10:10

atripathi