wood burning stoves 2.0*
The moose likes JBoss/WildFly and the fly likes How to get generated primary key value? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "How to get generated primary key value?" Watch "How to get generated primary key value?" New topic
Author

How to get generated primary key value?

Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
I've been trying lately to make jboss cooperate with auto generated primary key.

My bean works quite well, but jboss doesn't seem to set primary key to value generated by database.
How to do it???

I create a CMP bean using LocalHomeInterface, so how can I get this value?
My method for creating a been looks similar to this:

public void ejbCreate (id, value)
id is identifier which is ignored inside ejbCreate, only value is inserted into a database.

Since the value returned is void, how can I get primary key value generated by the database???

Regards,
Pawel
Nathaniel Stoddard
Ranch Hand

Joined: May 29, 2003
Posts: 1258
This has been answered about a frickin' zillion times. Search the forum for past posts.


Nathaniel Stodard<br />SCJP, SCJD, SCWCD, SCBCD, SCDJWS, ICAD, ICSD, ICED
Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
I had read a zilion of posts about this topic before sending this message.
The problem is that it doesn't work.

I have:
@jboss.unknown-pk

*class="java.lang.Integer"
*column-name="id"
*jdbc-type="INTEGER"
*sql-type="INTEGER"
*auto-increment="true"
*
* @jboss.entity-command
* name="hsqldb-fetch-key"
* class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand"

And my primary key is not set after creating an instance of CMP bean
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Did you also pre-define the database table with:

CREATE TABLE(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL


When you do an auto-incremented id, you must pre-define the database table yourself. As far as I know, JBoss cannot create such a table. So that means in jbosscmp-jdbc.xml you need to add the nodes:

<create-table>false</create-table>
<remove-table>false</remove-table>

to your entity's definition.
Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
Thanks Robert.

My identity works quite well.
But how can I get the value of the primary key???

My bean constructor is:

public java.lang.Integer ejbCreate(Integer id, String value) throws javax.ejb.CreateException {
this.setId(new Integer("0"));
this.setValue(value);

System.out.println ("Id is: " + this.getId());
return null;
}

So when I execute my client I get:
"Id is: 0"

I know it's perfect. The INSERT INTO statement is not executed in ejbCreate.
So how can I get the value generated as a primary key in the database???
SELECT MAX(id) FROM test ??
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Originally posted by Pawe� N/A:
public java.lang.Integer ejbCreate(Integer id, String value) throws javax.ejb.CreateException {
this.setId(new Integer("0"));
this.setValue(value);

System.out.println ("Id is: " + this.getId());
return null;
}


OK, I can see where you started going wrong. I'll try putting together a whole tutorial later but for now a few comments:

1. In your ejbCreate do NOT set the id
2. In ejbCreate (and the remote/local home interface) do NOT include the id as a parameter

Your application code should NOT have anything remotely to do with the id EXCEPT for the abstract setter/getter. So basically:


I repeat, DO NOT set the id yourself. Let it be handled by the container. You should only refer to the setting of it in the descriptor. In your ejb-jar, you want to act as though it was a normal cmp-field (so create a cmp-field for it, set prim-key-class, and set it as primkey-field).

In jbosscmp-jdbc, you'll use the unknown-pk stuff. And in the database, you'll create a table:

CREATE TABLE MYBEAN(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL, VALUE VARHCAR(256), CONSTRAINT PK_MYBEAN PRIMARY KEY(ID))

(By the way, be careful here. The word Value may be interpreted as a keyword and cause you trouble. I know it shouldn't but I've had that type of thing happen with a dumb word that should never be a keyword.)
Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
Ok... I understand. I did as You adviced.

Record is successfully inserted into a database;
How can I get id?

I have a reference to TestLocalHome object, and it doesn't have getter / setter. The only method it has is called by me create (String value).

How can I get reference to TestHome directly from TestLocalHome?
If I call any finder method (which returns home interface) there's no guarantee however, that the object returned is the object just inserted....
If I can't (as I suppose) get TestHome directly from TestLocalHome, how can I call method getId - which hopely give me primary key value generated by db?

Best Regards and many thanks in advice :-),
Pawel
Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
I've almost forgotten.

JBoss Logfile is:
2005-05-06 16:49:41,323 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand.Test] Executing SQL: INSERT INTO TEST (value) VALUES (?)

I think it's OK, but I can't see "CALL IDENTITY()" there :-(.
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Originally posted by Pawe� N/A:
Ok... I understand. I did as You adviced.

Record is successfully inserted into a database;
How can I get id?



Why that's quite easy!




It's just the regular EJB code!
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Originally posted by Pawe� N/A:
I think it's OK, but I can't see "CALL IDENTITY()" there :-(.


Well, a couple things here.

1. Since this is HyperSonic SQL, it's just a small example DB product, so it's not like a production DB would be
2. They actually use "SELECT Count(*) FROM MYBEANTABLE" to get the "identity"

Now #2 is fine if the only access to the DB is through EJB's because the container will do a great job of (hopefully) controlling concurrent access to/reads of the data. If transactions are properly started (and at the right scope) you'll be safe with that call. However, if outside of EJB, that DB is contacted and the transactions aren't done carefully enough inside the DB, the check could do this:

EJB GETS IDENTITY VALUE --> SELECT Count(*) FROM MYBEANTABLE, returns "3"
SOME OTHER PROCESS GETS IDENTITY VALUE --> SELECT Count(*) FROM MYBEANTABLE, returns "3"
EJB INSERTS --> INSERT blah VALUES ( 3, 'blah' ) INTO MYBEANTABLE
SOME OTHER PROCESS INSERTS --> INSERT blah VALUES ( 3, 'other' ) INTO MYBEANTABLE

and we've got a problem. But that's not really relevant here, so ignore this.

(and BTW, this is why people really started getting so excited about EJB specs. No more of people having to manage transaction levels/scope who didn't really know what they were doing - hey, it IS complex)
Pawe� Morgan
Greenhorn

Joined: Mar 29, 2005
Posts: 14
Thanks Robert.

Whoa!! It really works;
My fault. I simply forgot what create() method of a LocalHome interface returns. Seems like I should start reading specification again :-). I'll do it.

I didn't event thought about putting HypersonicSql to production environment. I just hope that in MySql things looks quite similar if not the same, so I't just for a good start.

Many thanks to You Robert.
Best Regards,
Pawel
Robert Paris
Ranch Hand

Joined: Jul 28, 2002
Posts: 585
Hey no problem, glad you got it. Yeah, it should be almost exactly the same in MySQL. The only differences being the entity-command (in jbosscmp-jdbc.xml) and possible the actual SQL code (I forget what MySQL's syntax is).

And all I ask in return is that whenever someone else asks a question that you know the answer to on this site, you help them out too!
Aleksander Adamowski
Greenhorn

Joined: Jun 25, 2008
Posts: 3
Hi!

I've found out that one can use post-table-create element in jbosscmp-jdbc.xml to alter the primary key right after table creation to make it auto increment.

Here's the relevant fragment of my XDoclet specification for the entity EJB class:



And here's how the ejbCreate() methods and primary key (productPk) getter look like:




Notice that I've used the %%t placeholder in the SQL code that will be substituted with the actual table name that JBoss will choose when generating tables.

There's no placeholder for the primary key column name, so one either has to guess, or deploy the app first without the post-table-create, then see what column name gets generated.

This is for Hypersonic but this technique can obviously be adapted to any RDBMS.

BTW, it would be nice if that trick would be documented in http://www.redhat.com/docs/manuals/jboss/jboss-eap-4.2/doc/Server_Configuration_Guide/Entity_Commands_and_Primary_Key_Generation-Existing_Entity_Commands.html...
[ June 25, 2008: Message edited by: Aleksander Adamowski ]

--<br />Aleksander Adamowski<br /> <a href="http://olo.org.pl" target="_blank" rel="nofollow">http://olo.org.pl</a>
Aleksander Adamowski
Greenhorn

Joined: Jun 25, 2008
Posts: 3
What's intriguing, I've seen people claim they get automatically generated primary keys on Hypersonic even when JBoss CMP takes care of creating tables - just by using entity-command name="hsqldb-fetch-key" on the entity and auto-increment on the PK field.

Example:

http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3966842#3966842

In my case with similar configuration JBoss was creating a table where the PK was an ordinary BIGINT field; then in INSERTs it was omitting the PK field and as a result I were getting a SQL error:



Any idea what these people do differently so they don't have to create the PK column by hand and still get automatically incrementing primary keys?
[ June 25, 2008: Message edited by: Aleksander Adamowski ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to get generated primary key value?