aspose file tools*
The moose likes JDBC 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
Bookmark "using last_insert_id() mysql statement in tomcat" Watch "using last_insert_id() mysql statement in tomcat" New topic
Author

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.

Thanks
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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: http://www.selikoff.net/2008/09/03/database-key-generation-in-java-applications/


My Blog: Down Home Country Coding with Scott Selikoff
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5823
    
    7

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
last_insert_id()
insert second data record containing id from first record
close connection

JBoss In Action
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: using last_insert_id() mysql statement in tomcat