This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes EJB and other Java EE Technologies and the fly likes Auto Generate Primary Key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Auto Generate Primary Key" Watch "Auto Generate Primary Key" New topic
Author

Auto Generate Primary Key

Raj Mehta
Greenhorn

Joined: Apr 26, 2004
Posts: 11
I am working on DB2 database, which doesnt have support for Auto Increment prirmary keys.

Is there a standard solution to this problem when inserting a new record in the DB using CMP EJB.
dimple sharma
Greenhorn

Joined: Feb 18, 2002
Posts: 14
Hi,

u can use Select query to get Max of that column and then add +1 in that that value to generate next id. As it is select query, u hv ejbSelect abstract method in bean if u r using CMP, in case BMP u have to write ur own method to select max. Select query is fast bcoz it is not running in pool and they never exposed to a client. They can returns entities and value of cmp fields.

I think it would solve ur problem.

regards
Eric Schumacher
Greenhorn

Joined: Jun 02, 2004
Posts: 20
A few DB's I have worked with have a separate table to keep other table 'sequences'. The sequence table has a row for each table with a column for the sequence or key. Each time a new record is added to one of the tables the application increments the sequence number for that table in the sequence table. I'm not sure if this is actually faster than doing a count, but I think it probably would be. The added benifit is that you don't mess up your keys when someone deletes some rows. If you use the count method you could have duplicates.
dimple sharma
Greenhorn

Joined: Feb 18, 2002
Posts: 14
Hi,

ejbSelect is always faster than ejbFind method. If you have separate table then you might have to be write ejbFind method and in your session bean first you have to look up that bean ond have to call find method. Instead ejbSelect give you fast result as it don't need to have look up and morevore it's not runnning in pool.

As far as duplication concern you have to use MAX keyword not COUNT.

Regards
Eric Schumacher
Greenhorn

Joined: Jun 02, 2004
Posts: 20
Yes, My bad. I still wonder about MAX() with regards to performance on very large tables. Additonally there could be concurency issues when using MAX() in this way. You will need to lock for the MAX() and any insert or you could have bad data..how much time between might cause issues.

Check out an example for using sequences:

http://dbforums.com/showthread.php?threadid=570125&highlight=SEQ_NAME
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Auto Generate Primary Key
 
Similar Threads
How does this compile?
Disabling a CSS attribute
storing date in mysql
Autoboxing / unboxing
passing a float to a double