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 Tomcat 5 _ Mysql 5 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 "Tomcat 5 _ Mysql 5 " Watch "Tomcat 5 _ Mysql 5 " New topic

Tomcat 5 _ Mysql 5

Kalyani Nayak

Joined: Jan 01, 2006
Posts: 25
I am using Apache Tomcat5.0, And Mysql5 .
In my web application I want to generate the primary keys.
I am using jsp and servlets technologies.
Just now I have designed it as follow
While creating new record I am fetching the max(id) from the corresponding table
Incrementing the value by one and adding in into create query.
This entire code I am writting in a 'synchronized' block to avoid concurrency.

Please let me know any disadvantages of such method, and if there is any better solution for it without using any third party jars.
Or if no, then please suggest me the better third party jar to generate the keys from servlet.
Jan Cumps

Joined: Dec 20, 2006
Posts: 2565

The disadvantage are that:
- select max(id) might lock your table for a moment,
- does not guarantee a unique id if you request it on different places in your code (synchronized will not help you in that case),
- it's not very performant.

When I use MySQL, I define an auto-increment key, and ask my statement what id has been generated (pseudocode):

The insert statement should not insert a value into the auto-generated key. MySQL will take care of that.

Regards, Jan
[ February 15, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
I agree. Here's the link:
subject: Tomcat 5 _ Mysql 5
It's not a secret anymore!