Please help with JDBC/DataSource transactions and primary key!

Hello,

I'm trying to figure out the best way to safely handle the creation of new values for primary keys. In the database schema I have to work with, every table has a numerical primary key (an ID column). Whenever I insert a new row, the new ID must be an incrementation by one of the highest previous ID. The code does a JNDI lookup of a java.sql.DataSource which is linked to a connection pool. The current idea is that the Java object making the SQL calls will be called by an EJB SessionBean. We would like to avoid entity beans.

What would be the best way to generate values for the ID columns? Should I use a singleton KeyGenerator class for each table with synchronized blocks to ensure thread safety? Transactionalize the SessionBean's methods? Those might cause a problem if we forego the SessionBean idea or the application becomes distributed.

Or is there a way to use database transactions? To tell the database to lock a table for writing before asking for the current max ID, and release the lock after the insertion of the new row(s)?

Or perhaps there is a way to specify in the insert SQL statement itself that the value of the ID column should be 1+the current max value?

Thank you in advance,
Yuriy Zilbergleyt

Depending on the database you can use, you can get the database engine to generate unique keys.

For example, in SQL Server you do this with identities, and in Oracle you would do it with a sequence.

Thank you for the quick reply Dave!

The database we're using is MySQL, but I would prefer to avoid vendor tie in here. I take there's no standard SQL way to autogenerate keys?

Thank you,
Yuriy
[ September 16, 2005: Message edited by: Yuriy Zilbergleyt ]

There are patterns for generating primary keys, but I don't believe there is a standard way using plain JDBC. Theres an article on JDJ that discusses one of these patterns.

MySQL certainly provides sequence numbers, however I can't remember the name that MySQL calls it though - sorry.

If you use something like hibernate, it will generate the relevant sql for different vendors so you don't need to worry what database you are using.

Very interesting article, thanks!

Yuriy