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.
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 ]