Hi!
I have a problem with bean-managed persistence and primary key. The ejbCreate method must return the primary key and I don't know how to get it.

I want the database to get me the next available id in the table and put the row there. How do I return the key back to the bean to be able to set it in the object and then be able to return it?

Thanks,
/Fredrik

if you have a sequence trigger in the database, you can simply do a select nextval from <sequence name>. subtract 1 from this ifyou are incrementing by 1 and that will be your primary key.

however, this requires that you hit the db one time extra to get the PK. What you could do is, write the insert logic as a procedure and return the key from the procedures' return statement.

you might want to take a look at the PKstrategies pattern in the ejb patterns book on review on this site somewhere

YSK

Thanks for your answer.
The problem is that I want to have a database vendor independent application. I want my code to work with all
databases without any changes.
As I understood it, your suggestions does not work with
all databases?

Several solutions:
a) Go for the two field primary keys, with singletons written to ensure the keys are never duplicated through a combination of IP address, and time and....

See the threads of PK's elsewhere on this site. There were some massive threads on this issue.

b) Write the java util class and make your build process switch the code for the target DB. It's great as you can then use the underlying atomic sequence operations and not worry about duplicate PK's. It's bad because you code for each database - but how many do you have to worry about? 3 or 4. Its actually a tiny function. You can even soft code the db type into a 'app config table' and have the PK finder function configure itself the first time it is called.

I actually like sequence numbers, as opposed to purely random primary keys. But there is no logical explanation for that.

Also, I hear weblogic now lets you use sequence numbers, but I've not used it so can say no more.

Jonathan

To get a primary key value via a mechanism that is portable, create yourself a primary key table with an associated entity bean.

The primary key bean would only have one method which would be getNextIndex. getNextIndex would grab the current value from the table, add one to it, store it back and then return the new value.

Your bean's create method will call the primary key bean to get the next index value - simple.

Two issues: extra overhead as your create process will execute 2 more database calls (one to get the current value and one to store the next value). Also, you will need some way to ensure that those that access the database directly (ie: not via your EJB server) will also use the primary ey table.

Thoughts??

My problem with this is that you are reinventing the wheel. All db's support attomic operations for getting sequence numbers. Why should you have to discard them for J2EE... any architecture which means you must reinvent this stuff is blatently rubbish.

Jonathan

Before you say its 'blantantly rubbish', you should appreciate a couple of facts. We use the Primary Key bean in a Sybase / Weblogic environment. This is required for two reasons:

1. Sybase has 'identity' columns and not sequences. You can't access the value of the current identity without doing a max query on the table. You can't change the value of the column in, say, a pre-insert trigger.

2. Weblogic supports automatic primary key generation for Oracle but not Sybase.

So, with CMP in a Sybase environment, how else can you create a primary key that won't violate CMP creation rules??

Darren.