• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Junilu Lacar
  • Tim Cooke
Saloon Keepers:
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Peter Rooke
  • Himai Minh
Bartenders:
  • Piet Souris
  • Mikalai Zaikin

How to get generated primary key value?

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 1258
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This has been answered about a frickin' zillion times. Search the forum for past posts.
 
Pawe� Morgan
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 585
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 585
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 585
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 585
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 585
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Aleksander Adamowski
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
please buy this thing and then I get a fat cut of the action:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic