Getting the last inserted record id of a database table in java?

Question

I have a database table A which stores records, A has a primary key (recordid) with auto_increment, each time i insert a record in to A, i get the inserted recordid and store it in another masterTable.

I am using a select statement as soon i do an insert into A to get the recordid like this:

select recordid from A order by recordid DESC LIMIT 1;

But i ran into a problem today, where in two records were inserted(by different threads) at the same time and i ended up storing wrong recordid in the master id( the same recordid for both the txns)

I heard about Statement.getGeneratedKeys(), I would like to know if that really helps resolve the issue. Or what is the best way to handle this.

Answer 1

You can use the getGeneratedKeys method. This forum post will help.

May I also recommend that you use an ORM tool like Hibernate. In Hibernate you would do something like this:

myTable = new myTable();
myTable.prop1 = prop1;
myTable.prop2 = prop2;
int id = session.save(myTable);

Hibernate will issue the appropriate SQL commands (depending on the database selected) and return you the auto-generated id.

Answer 2

The MySQL JDBC driver does support the getGeneratedKey() method. Have a look at the section 20.3.5.1.4. Retrieving AUTO_INCREMENT Column Values of the MySQL manual where:

we demonstrates the use of the new JDBC-3.0 method getGeneratedKeys() which is now the preferred method to use if you need to retrieve AUTO_INCREMENT keys.

Answer 3

In databases that don't support generatedKeys you may be able to get the ID into a return parameter. Oracle for example provides the RETURNING xxx INTO ? syntax where xxx is your column name.