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.
解决方案
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.