• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Henry Wong
Saloon Keepers:
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Tim Moores
  • Mikalai Zaikin
Bartenders:
  • Frits Walraven

Please help with JDBC/DataSource transactions and primary key!

 
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 293
Mac OS X Netbeans IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Dave Salter
Ranch Hand
Posts: 293
Mac OS X Netbeans IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Yuriy Zilbergleyt
Ranch Hand
Posts: 429
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Very interesting article, thanks!

Yuriy
 
if you think brussel sprouts are yummy, you should try any other food. And this tiny ad:
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic