I working with CMP EJB and oracle. I have a table that has an trigger to autoincrement the primary key. In the create method of the EJB i am able to insert into the database.
Since i specify a dummy value for the primary key in the Create method for EJB the database actually assigns the next auto increment value. which for all purposes is fine with me.
Question ? .... How do i get the latest value for the primary key for the tuple that has been just inserted in the database. ?? Remember i put in a Dummy value but the real values is one inserted by database
is there any way i can get the values that i just stored in the DB in the create method.... in the same create call ???
No, there isn't. For some reason wiser men than me don't understand the sages at Sun decided that autoincrement values don't exist in real databases and therefore didn't allow for them in the EJB specification.
What I (and many others) do is create generators in my database which I call before inserting a new record, setting the primary key myself instead of using autoincrement fields. Of course that immediately puts a database dependency into your code as such things are not part of standard SQL and therefore need custom logic for each RDBMS that supports them (not all do).
For Firebird I do something like this: Create a generator and set it to initial value of 0.
In the EJB:
In the dbHelper (which is created for different generators as requested):
That at least concentrates all the engine specific code in a single place where it can be easily swapped out by instantiating another class from the factory. This last bit of code of course (and the DDL) are extremely DMBS dependent and will only work with Interbase and Firebird.
That's right you can rely only on vendor specific support.
Below you've got an example how to do this with JBoss and PostgreSQL (I've got a link with explanation how to achieve the same with JBoss and Oracle, but it's on another comp, if you need it I can look for it).
What you must do to compile it is to add a com.hicnar.library.Author class which is a simple transfer-object bean (you can infer from the last method how it's built).