• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

using last_insert_id() mysql statement in tomcat

 
Fabio Piergentili
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4020
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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/
 
Peter Johnson
author
Bartender
Posts: 5852
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic