• 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
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Auto Generate Primary Key

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
reply
    Bookmark Topic Watch Topic
  • New Topic