File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes using last_insert_id() mysql statement in tomcat 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 "using last_insert_id() mysql statement in tomcat" Watch "using last_insert_id() mysql statement in tomcat" New topic

using last_insert_id() mysql statement in tomcat

Fabio Piergentili
Ranch Hand

Joined: Sep 04, 2009
Posts: 57
I need to get an ID of a new record in a mysql table to be used to insert another record into another table. I am using last_insert_id() to get it. Since tomcat is multi client/ threaded it feels as if it is not safe even though mysql states

"For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client."

I think that tomcat is the client in this case so this is not safe as many clients connect to tomcat. Does anyone have any suggestions on how to accomplish this in a safe way if in fact this way is not safe as i suspect.

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

Generate the key outside the database. In a multi-threaded environment features like LAST_INSERT_ID() can be tricky to use. Read my article on the subject for tips and alternatives:

[OCA 8 Book] [Blog]
Peter Johnson

Joined: May 14, 2008
Posts: 5852

Tomcat maintains a connection pool (or rather, you can configure Tomcat to do so). If you ask for a connection from the pool then that connection is yours until you release it. Thus you can use LAST_INSERT_ID() to obtain the id. Example work flow:

get connection
insert first data record
insert second data record containing id from first record
close connection

JBoss In Action
I agree. Here's the link:
subject: using last_insert_id() mysql statement in tomcat
jQuery in Action, 3rd edition