How do I get the AUTOINCREMENT value?

[Kaydell Leavitt]

If I INSERT a record into a database a using JDBC / SQL INSERT statement, how do I get the value of the AUTOINCREMENT field that I just inserted?

In single-user, I can know that no other thread has inserted into the same database table and do something like the following, where ID is my AUTOINCREMENT field and Items is my database table.

SELECT ID FROM Items ORDER BY ID DESC LIMIT 1

But, will this sort of thing work for multi-user? If I'm in a transaction, will I see the last ID that my transaction has inserted and not the IDs that other transactions may have inserted?

Kaydell

[Paul Clapham]

After you use a Statement (or PreparedStatement, which is a subclass) to do an INSERT, follow up by calling that Statement's getGeneratedKeys() methods. It returns a ResultSet containing the auto-generated keys, if any.

You might find that you have to do your INSERT like this:

stmt.execute(insertSQL, Statement.RETURN_GENERATED_KEYS);

although some database drivers don't have to be reminded like that.

[Kaydell Leavitt]

Thanks Paul,

The simplifies things quite a bit for me.

Kaydell
[ June 07, 2007: Message edited by: Kaydell Leavitt ]