Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes retrieve autonumber value of last insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "retrieve autonumber value of last insert" Watch "retrieve autonumber value of last insert" New topic

retrieve autonumber value of last insert

Jon Dornback
Ranch Hand

Joined: Apr 24, 2002
Posts: 137
i am making inserts to multiple tables, the first of which generates an autonumbered id which the other tables use as a foreign key. to get the value of the autonumbered id, i am using the SQL server property: "select @@identity" which returns the last autonumber id inserted.
my question is: how do i ensure that the database (or table) is locked so that no other updates/inserts are performed? is this what the Connection.setTransactionIsolation(int level) method is for?
other questions: do autonumber columns *always* increment from the highest value? (eg, if i insert some rows that have numbers 1,2,3,4 then delete a row so that i am left with 1,2,4 then do another insert, will autonumber user 3 or 5 as the next value? so far from my experiments it always uses the highest value, but is this guaranteed?)
is @@identity a microsoft only implementation, or is it a sql standard?
thanks for any answers to these many (not-entirely-java) questions.

use the [CODE] tags - it makes it much easier for people to help you.
David O'Meara

Joined: Mar 06, 2001
Posts: 13459

A slightly different conversation, but some of what you want is dicussed in this thread.
Firstly, the syntax used to get the next number is vendor-specific, so @@identity is a MS thing.
Again I believe its vendor specific, but you can't be guaranteed that the next number returned is one more than the previous one. To make it more efficient, indicies are sometimes handed out in batches. This means person one may be getting numbers in the range 1-10 and person two is getting 11-20. If person one doesn't use all their numbers, they may just be discarded by the database.
Adam Hardy
Ranch Hand

Joined: Oct 09, 2001
Posts: 567
You also definitely want to setAutoCommit() to off, and then commit the transaction at the end, to make sure simultaneous processes don't collide.
Perhaps someone else can tell you what setTransactionIsolation(level) you need...... I've always used the default

I have seen things you people would not believe, attack ships on fire off the shoulder of Orion, c-beams sparkling in the dark near the Tennhauser Gate. All these moments will be lost in time, like tears in the rain.
I agree. Here's the link:
subject: retrieve autonumber value of last insert
It's not a secret anymore!