How to determine Primary Key of New Record

[Wayne Turner]

I'm looking for advice. I have a table whose primary key is an auto-incrementing field. I can use "INSERT INTO tablename (Field1, Field2) VALUES (123, 456);" and that works, but how do I determine the primary key of that record I just entered?
I tried playing with updatable recordsets, but they really don't work. (See 'Error When Entering Data')
And, yes, this is a multi-user enviornment. My code is not the only point of entry into this database.
What is your wisdom?

[Blake Minghelli]

Ah yes, serial (auto-increment) fields. There so nice because of their simplicity, but they do have that one major flaw.
Since you can't control inserts into the db (because of other code), the only other think I can think of is to have another unique, composite index built into your table design. For example, even though you use the serial key for your pk (for single-column simplicity), your table may have 2 or 3 other columns that when put together create a unique index. Then after you insert the new row, you can do a query using the unique index as your where clause. I've used that strategy before. Since the query is on a unique index it is very fast.

[Hartmut Ludwig]

MySQL offers the function LAST_INSERT_ID() for this purpose:
INSERT INTO foo (auto,text) VALUES(NULL,'text');
SELECT LAST_INSERT_ID();
But if your DBMS does not support this feature you have to do a select after the insert.
Select for the inserted values together with the maximum from the autoincremented attribute.
sl
Hartmut

[Wayne Turner]

Tip o' the hat to you again, Blake.
I was afraid this was would be the answer. It just seems awkward. In addition to having an auto-incrementing number field as the primary key (I love numbers), now I have to make sure the combination of two (or three) fields is unique. Big Bummer. Those fields could change in the future, but at least during the initial insert, they are unique and identify your record correctly.
Okay, I'll come clean. I have a hidden agenda here. I have been laying the groundwork (JDBC code, beans, servlets, etc.) that prepares me to create my first EJB Entity bean. An Entity Bean has to have a primary key. My questions about this are the subject of another topic thread, but you see where I'm heading.

[Jamie Robertson]

The easy way: via the new jdbc 3.0 statement.executeUpdate( String sql, int autoGeneratedKeys) method. Unfortunately I haven't seen any any drivers supporting the jdbc 3.0 functionality yet.
Other options:
Database specific:
The Oracle way of doing this is to use a sequence. Generate a new autonumber value and read it using the sequence_name.NEXTVAL function (Select sequence_name.NEXTVAL from dual). Read the new sequence number into your java program using resultset.getString(). Then do a sql update, inserting the new sequence value as the id.
For other databases you might want to look into how they deal with auto-number and such mechanisms. As you can see, this approach is anything but portable.
Another suggestion is to use System.currentTimeMillis() function to return to you a unique number based on the date/time. Just have some error handling code to deal with simultaneous inserts that may result in duplicate values ( primary key violation exceptions ). This is probably the easiest way to implement such a feature.
Jamie
[ September 16, 2002: Message edited by: Jamie Robertson ]

[Jamie Robertson]

Hartmut,
Just wondering if LAST_INSERT_ID() retrieves the last id inserted into the database ( could have been inserted simultaneously by any user, and not necessarily the id the one we inserted ) or is it the last id inserted through the connection?
just wondering,
Jamie

[Hartmut Ludwig]

Hi, Jamie,
the function returns the last inserted id of the connection the user used to insert.
Otherwise it would not be very worthwile, indeed.
See Chapter 8.4.6.3 How Can I Get the Unique ID for the Last Inserted Row? in the MySQL manual:
The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value
sl
Hartmut

[Ken Robinson]

This is more my preference, but it provides a few benifits and will provide you with the functionality you require.
I wrap all of my inserts in Stored Procedures. The main reason is that since the Database (Model) is responsible for the data, I leave the validation and key assignment to the database. The stored proc will only take the known info as input parameters. The proc will validate those fields that require validation, get the next key value from a sequence, do the insert and (as an output parameter), return the primary key.
By using a CallableStatement, you can register the output parameter, set all other parameters as you would in a PreparedStatement, execute the insert and get the return value (Primary Key) as you would from a ResultSet.
This allows all clients (not just java) to only worry about the non-key data while still being able to get the PK without much work. It also allows you to put all validation in one place (the stored proc) for all clients (java or non-java) to make use of. Since a stored proc is an object in the database, you can simply grant permission to the stored proc to those users you want to insert data, revoke insert on the underlying table and thus force every one to use the same proc to insert data, thus forcing everyone to pass through the same validation process.

[David O'Meara]

"turncom",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp.
We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please edit your profile and select a new name which meets the requirements.
Thanks.
Dave