How to get generated primary key value?

[Pawe� Morgan]

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]

This has been answered about a frickin' zillion times. Search the forum for past posts.

[Pawe� Morgan]

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]

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]

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]

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:

/** HOME INTERFACE CREATE METHOD */
public MyBean create(String value);
 
/** YOUR BEAN CREATE METHOD */
public Integer ejbCreate(String value)
{
     setValue( value );
     return null;
}
 
//SETTER/GETTER
public abstract void setId(Integer id);
public abstract Integer getId();

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]

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]

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]

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!

//EXAMPLE
TestLocalHome home = ( TestLocalHome ) jndiContext.lookup( "TestLocalHome" );
 
TestLocal localBean = home.create( "MyValue" );
 
//TESTING FOR PRIMARY KEY VALUE
System.out.println( localBean.getPrimaryKey() );
 
//TESTING FINDING IT
TestLocal sameBean = home.findByPrimaryKey( ( Integer ) localBean.getPrimaryKey() );
  

It's just the regular EJB code!

[Robert Paris]

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]

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]

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]

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:

....
 * @ejb.bean name = "ProduktCMPEntity" display-name = "Product Entity EJB,
 *           container managed persistence" jndi-name = "ejb/ProductCMPEntity" local-jndi-name =
 *           "ejb/ProductCMPEntity" view-type = "both" type = "CMP" schema =
 *           "Product" cmp-version = "2.x" transaction-type = "Container"
 *           primkey-field = "productPk"
 * @ejb.pk generate="false" class = "java.lang.Long"
 * @ejb.finder signature = "java.util.Collection findAll()" description =
 *             "Zwraca wszystkie producty" query = "SELECT OBJECT(n) FROM
 *             Product n" transaction-type = "Required" view-type = "both"
 * @ejb.resource-ref jndi-name = "java   :D   efaultDS" res-ref-name =
 *                   "ShopDataSource" res-type = "javax.sql.DataSource"
 *                   res-auth = "Container" res-sharing-scope = "Shareable"
 * @jboss.entity-command name="hsqldb-fetch-key"
 *                       class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand"
 * @jboss.persistence remove-table = "false" post-table-create = "ALTER TABLE %%t ALTER COLUMN
 *    ProductPK BIGINT GENERATED BY DEFAULT AS IDENTITY(START
 *    WITH 0) NOT NULL"
....

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

....
/**
 * @return the generated primary key value
 * @ejb.create-method view-type = "both"
 * @ejb.transaction type = "Required"
 */
public java.lang.Long ejbCreate(String name, String description, Double price)
throws CreateException {
setName(name);
setDescription(description);
setPrice(price);
return getProductPk();
}
 
/* ***************** Akcesory pol ***************** */
 
/**
 * @ejb.interface-method view-type = "both"
 * @ejb.transaction type = "Required"
 * @ejb.persistence jdbc-type = "BIGINT"
 * @ejb.pk-field
 * @jboss.persistence auto-increment = "true" not-null = "true"
 * 
 */
public abstract java.lang.Long getProductPk();
....

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 ]

[Aleksander Adamowski]

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:

Could not create entity:java.sql.SQLException: Attempt to insert null into a non-nullable column: column: PRODUKTPK

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 ]