This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
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.
Joined: Feb 18, 2002
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.
Joined: Jun 02, 2004
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.