Best way to generate PK in EJB

[Rashmi Banthia]

I am new to EJB. Here is my question, I would appreciate your responses :
I have a table Employee with the primary key : emp_id, I would like this to be generated by the entity bean. (I don't want user to have access-read/write to this column) What's the best option :
(1) Create a differnt Local interface /bean - with business method which basically returns "Select max(emp_id) + 1 from employee"
(2) In the home interface create a business method which returns max value from db.
(3) Create a plain java class use jdbc connection to connect to database and get max value.

or any other suggestions ??
Thanks,
Rashmi

[anjan]

the other option u can try is
check for u r container support for auto-generated primary key
if u r using weblogic8.1 then u can try this ,
dont expose set/get methods for this field in u r Local Interface of EntityBean
WebLogic Server supports automatic primary key generation feature for CMP entity beans. This
feature is supported for simple (non-compound) primary keys only.
WebLogic Server supports two methods of automatic primary key generation:
1) Native DMBS primary key generation�The database generates the primary key. To enable
this feature, specify the database and a generator name in the
<automatic-key-generation> stanza of weblogic-cmp-jar.xml. Based on the values
you configure, the container generates code that obtains the primary key from the database.
This feature is supported for Oracle and Microsoft SQL Server databases only.
2)Primary keys generated from a SEQUENCE table.
Specifying Automatic Key Generation for Oracle
Generated primary key support for Oracle databases uses a SEQUENCE entity in the Oracle
database to generate unique primary keys. The Oracle SEQUENCE is called when a new number is
needed. Specify automatic key generation in the automatic-key-generation element in
weblogic-cmp-jar.xml, as shown below.
<automatic-key-generation>
<generator-type>Oracle</generator-type>
<generator_name>test_sequence</generator-name>
<key-cache-size>10</key-cache-size>
</automatic-key-generation>
Generating Primary Keys with a Named Sequence Table
A sequence table is a database-neutral way to generate primary keys. The sequence table holds a
monotonically increasing integer sequence value that is used as the primary key value in bean
instances as they are created.
Create a table named SEQUENCE to hold the current primary key value. The table consists of a
single row with a single column, as defined by the following statement:
CREATE table_name (SEQUENCE int)
INSERT into table_name VALUES (0)
To use this feature, make sure that the underlying database supports a transaction isolation level
of Serializable. The Serializable value indicates that simultaneously executing a
transaction multiple times has the same effect as executing the transaction multiple times in a
serial fashion.
Specify automatic key generation in the weblogic-cmp-jar.xml file, as shown below.
<automatic-key-generation>
<generator-type>NamedSequenceTable</generator-type>
<generator_name>MY_SEQUENCE_TABLE_NAME</generator-name>
<key-cache-size>100</key-cache-size>
</automatic-key-generation>

[Rashmi Banthia]

Thank you for your reply. I am using Jboss - 3.0.4 , and mySQL - 4.0.17-nt.
If JBoss supports autogenerated primary keys, I'll use that.
Thanks,
Regards,
Rashmi

[Maulin Vasavada]

Hi Rashmi
In addition to what ideas you have in your mind I would recommend to read,
Chapter-5 "Primary Key Generation Strategies" from EJB Design Patterns book.
It might be helpful...atleast it gets our mind out of standard max+1 id generation logic you know...
Regarfds
Maulin

[Rashmi Banthia]

Thanks Maulin. That was really an insight.
Regards,
Rashmi

[Patrick Nolan]

I would suggest you look into using a UUID as your primary key. This means universally unique id - it can be generated in the application rather than the database and is generally a string, not a number.
So.. for example, let's say you have a cluster of ejb servers which comprises 2 app servers, each on its own physical machine. Obviously, each app server in the cluster must be able to generate a unique id for inserts (ie ejbCreate()). This can be done by concatenating the following: HOSTNAME + TIME IN MS + EMPLOYEE SSN. I'm sure you can come up with a better combination for the UUID, but you get my point.
Pat

[Michael D. Brown]

To be fully honest, you shouldn't be generating PKs in your Entity Beans at all. That is what the database is for. If you want to generate the PKs based on some business rules you can use a stored procedure to handle that.

[Seetesh Hindlekar]

Hi Patrick,
UUID is not normally used as a sequence key generator in a real-time development cos of one of its trade-offs (mentioned in the Design pattern).
USE OF 36 DIGIT STRINGS FOR PK.
This applies only if the IP address of the computer doesnt change unlike some companies ie depending on IP address assigned within the LAN.
What if the PK is set to be a Number instead of a String or if the PK is the combination of 2 or more columns in a database.
Hope my point is made.
Rgds,
Seetesh

[Seetesh Hindlekar]

Hi Rashmi,
As per as your requirement, depending on the datatype of the emp_id column in ur Employee table, u have to design the PK strategies for creating PK.
If ur emp_id is a "NUMBER" datatype preferred over "CHARACTER" datatype, then ur logic should be in the ejbCreate for creating a sequential number for the following reasons :=
1. U can't have duplicate employees having same emp_id nor can afford to have out-of-sync values created for this emp_id using the sequence generator.
2. Should take care when ejbRemove is used by ur program which will have the Container removing/deleting the respresentation of the entity in the database.
select max(emp_id) wont help in this case
Hope the question of a "CHARACTER" datatype for emp_id doesnt exist in ur application as this may not create the real meaning of having sequential emp_id's.
Rgds,
Seetesh

[Rashmi Banthia]

Thank you all for your inputs.
Just to make sure, I understand the implications involved in each of methods :
(1) The one mentioned by Patrick using UUID string
- could be good solution if there are cluster of servers involved.
- It makes the application portable if in future there is a possibility of using clustered servers.
(2) Seetesh, I have number datatype for emp_id. Well, you are right about : can't have duplicate employees having same emp_id because it is a PK. As far as out-of-sync values how can that be avoided ? Do you mean a sequenceentity bean with a session bean having a block of integer values which as cached locally as explained in the book 'EJB Design Patterns'.
If you have 10 rows with 1 to 10 emp_id , and let's say emp_id 5,7 are deleted. It is out sync.
I think using a Sequence entity bean as explained in the book EJB Design Patterns works for my case as I don't have clustered servers/ weblogic generating a key.
Regards,
Rashmi

[Maulin Vasavada]

Hi Rashmi
I guess that caching of the ids is not supposed to work that way. By caching we want to mean, "we will cache X number of future keys so that when asked by the code we don't have to run a method to generate new id". This is to save time in generation of new ids I believe.
Now, if we have 1-10 ids cached then it means there is no PK in DB that is having values 1-10. So, question of any ids like 5 or 7 getting deleted should not arise as we would be assigning the ids from the cache sequentially.
In this caching we have to make sure that we update cache as the ids are taken. So if we have 1-10 allocated and 1,2,3 are taken then we should now have 4-14 in our cache you know...BUT the problem for us here would be , "when to update cache with new values?" because we can't do that on each assignment of ids to a process as that would again cost us processing effort on each assignment of id, which is what essentially we are trying to avoid.
Thats all design issues if we want to go with this caching ids option.
Now, I am not sure what out-of-synch means here mentioned by Seetesh...so if he can give us some more description about what he means it would be helpful to me...
Regards
Maulin

[Patrick Nolan]

Rashmi, I like using UUIDs as PKs in complex enterprise scenarios. For example, you have 5 enterprise systems scattered around the country and these systems are integrated using EAI. Two of these systems are able to create employee objects. How do you guarantee that each system generates unique primary keys for the employee objects that they create? Answer: UUID. UUIDs are very handy in this way.. and remember they are always unique across space and time.
UUIDs generated in the application allow faster row creation in that it is not necessary to hit the database twice in order to insert a record.
There are caveates however. What if you want to run a script to insert records into the database (such as in data migrations).. how do you create the UUID dynamically in your db script? Now you have to duplicate the logic because the application only knows how to create the UUID. There are ways to handle this, like in some DBs (ie Oracle), support java in the database so you could hook into your UUID generator class from your db script (see: http://otn.oracle.com/tech/java/htdocs/9idb2_java.html).
Frankly, I don't agree that PK generation must stay in the database. DBAs will probably argue with you on this (so be prepared to justify your position
I have implemented several enterprise packaged solutions that use UUIDs and I can't say I saw any database performance issues due to their use. Also, FYI these were VERY high transaction volume systems.
Patrick J. Nolan, Jr.

[Seetesh Hindlekar]

Hi,
Normally emp_id for an Employee database (in India), for its datatype as NUMBER, has to be in a running sequential order only ie 1,2,3,4 ....3001,3002.
Its expected that employees who have resigned have their emp_id deleted or a flag set so that they are stated as "Resigned". Normally programmers use the ejbRemove method physically deleting the record from the database mapped to the EB.
This causes out-of-sync emp_id in the current database and there is a possibility that one of the deleted emp_id can be assigned as a Primary Key, which seems logical in our case as there is no way this can be prohibited.
For example 10 employees join one organization whose emp_id ranges from 1001 to 1010. After some time employee having 1003 and 1007 resign from the organization and while invoking ejbRemove via remove in home interface permanently deletes the 2 employees 1003 and 1007.
Now there is a remote possibility that 1003 and 1007 can be assigned to new employees joining.
Hence u have to be very careful when u use any select max(emp_id) etc logic while assigning a sequence to a running Emp_id field as mentioned by Rashmi.
Rashmi : Read the trade-offs part carefully on pg 136 of Design patterns pdf (Sequence Blocks). Its self explanatory.
Maulin : Ur right abt the Cache mechanism.
Patrick : I agree with u for UUID but there is also a limitation for the primary key being generated in UUID manner as required for the emp_id field. Remeber its a running sequential no.
I have tried using Singleton pattern too but appreciated the Sequence Key Generator method as eventually the Emp_id generated was stored in Oracle table after being rightfully validated.
Correct me if I am wrong.
Hope I have made things clear.
Rgds,
Seetesh

[Maulin Vasavada]

Hi Seetesh
Thanks for taking so much time.
Agree with you on your perspective and Patrick to point out a possible issue we would face if we use UUID things.
Hope it helps Rashmi to reach to concrete conclusion.
Regards
Maulin

[Rashmi Banthia]

Yeah, It certainly did help me. Now I am very well aware of different options and trade-offs. I am in the process of documenting some of these methods for my reference.
Thanks and Regards,
Rashmi