How do I use EJB CMP with an autogenerated PK (in postgresql

[lhaas]

Can anybody please tell me how to use the EJB CMP 2.0 to handle an autogenerated pk for a table in postgresql. Right now , I only have 1 ejbCreate -- that is ejbCreate(){}. I'm not setting the id to anything because postgresql should be the one generating it for me.

Currently JBOSS is blowing up when I'm trying to create the EJB complaining about id is being set to null.

Please help !!!

[marcuslarsson]

This probably isn't much help since I've still not been able to get this to work, but this is what I've tried:

In jbosscmp-jdbc.xml under defaults you should specify an unknown-pk element and an entity-command for getting the key.


java:/PostgresDS
<datasource-mapping>PostgreSQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
<pk-constraint>true</pk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="get-generated-keys"/>


The entity-command is declared in the entity-commands section of /server/default/conf/standardjbosscmp-jdbc.xml. I've tried using some of them, but without success.

The cmp-field for the pk should be:

<cmp-field>
<field-name>id</field-name>
<auto-increment/>
</cmp-field>

In ejb-jar.xml there shouldn't bee any cmp-field for the pk and prim-key-class should be java.lang.Object.

I've also modified the PostgreSQL type-mapping in standardjbosscmp-jdbc.xml adding an auto-increment-template like the one in the mySQL type-mapping. I don't know what the correct definition of this template for PostgreSQL should be ("?1 auto_increment" is specific for mySQL and won't work for PostgreSQL).

I tried declaring a sequence 'jbosseq', but I don't know what to put in the auto-increment-template in order to get the next value from the sequence (maybe something like <auto-increment-template>?1 nextval('jbossseq')</auto-increment-template> )

If you find a solution I would be very interested hearing about it. Right now I've given up on autogenerated pk's, and I have written a session bean with a method which returns the next number in a PostgreSQL sequence. I call this bean from the ejbCreate method of my entity beans to set the pk.

/Marcus

[marcuslarsson]

This probably isn't much help since I've still not been able to get this to work, but this is what I've tried:

In jbosscmp-jdbc.xml under defaults you should specify an unknown-pk element and an entity-command for getting the key.


java:/PostgresDS
<datasource-mapping>PostgreSQL</datasource-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
<pk-constraint>true</pk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="get-generated-keys"/>


The entity-command is declared in the entity-commands section of /server/default/conf/standardjbosscmp-jdbc.xml. I've tried using some of them, but without success.

The cmp-field for the pk should be:

<cmp-field>
<field-name>id</field-name>
<auto-increment/>
</cmp-field>

In ejb-jar.xml there shouldn't bee any cmp-field for the pk and prim-key-class should be java.lang.Object.

I've also modified the PostgreSQL type-mapping in standardjbosscmp-jdbc.xml adding an auto-increment-template like the one in the mySQL type-mapping. I don't know what the correct definition of this template for PostgreSQL should be ("?1 auto_increment" is specific for mySQL and won't work for PostgreSQL).

I tried declaring a sequence 'jbosseq', but I don't know what to put in the auto-increment-template in order to get the next value from the sequence (maybe something like <auto-increment-template>?1 nextval('jbossseq')</auto-increment-template> )

If you find a solution I would be very interested hearing about it. Right now I've given up on autogenerated pk's, and I have written a session bean with a method which returns the next number in a PostgreSQL sequence. I call this bean from the ejbCreate method of my entity beans to set the pk.

/Marcus

[garbelini]

You have to make some choices:
Do you really need to use the postgres sequences?
IF SO:
1 - On each create you will have to get the next id from the sequence yoursef
2 - Lookup the documentation and find out if your JBoss version has a CMP implementation that supports db sequence primary key generation. (I know that it has this feature for MySql for some time now)
3 - Adapt the Sequence Block pattern i the resource pointed below to get the PKs from each sequence.
IF NOT:
1 - Use one of the patterns sugested by Floyd Marinescu to generate primary keys. free pdf from http://www.theserverside.com//books/EJBDesignPatterns/index.jsp?tmc

Cheers,
Marco Garbelini

[loumaus]

have a look at www.pirack.com
go into howtos ...

there you find a solution ..
might not be the best one .. .
but it works ..

pirack.com looks as it is not yet ie compliant
:O)

[vprise]

I had this exact problem and there is no working solution other than the one at www.pirack.com which I haven't tried.
There are instructions on how to setup a solution similar to the one that currently works for MySql, but this doesn't work even when moving to RC4 and the development driver. But from reading the postgresql mailing lists it seems that someone is in fact working on this so hopefully this will actually work one day.

[alwyn]

Can anyone with a working XDOCLET example of a cmp bean using an auto incremental integer key with postgresql, please post it to the forum?

[wunderkind]

Hi there. I tried a combination of the x-doclet tags
@jboss.unknown-pk (with auto-increment="true") and
@jboss.entity-command name="mysql-get-generated-keys" but X-Doclet did not
generate the correct jboss-jdbc.xml file. I still have to
paste the correct elements into the xml. Maybe it is my
fault and there is some logic overlapping within my x-doclet tags. But IMHO this is one of the biggest disadvantages of x-doclet: Relationships and dependencies between tags (class and method-level) heavy to verbose...

[alanbram]

The way to make this work is to configure the sql-type of your primary
key column to be "SERIAL". In PostgreSQL, this isn't actually a real
SQL data type, but rather kind of a "notational convenience" which
automatically creates an associated SEQUENCE and sets the default
value of the table column to retrieve the next value from the
sequence. (See section 5.1.4 of the PostgreSQL User's Guide.)

Here's an excerpt from my jbosscmp-jdbc.xml:

<jbosscmp-jdbc>
<enterprise-beans>

<ejb-name>...
<cmp-field>
<field-name>id</field-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>serial</sql-type>
<auto-increment/>
</cmp-field>
<entity-command name="postgresql-fetch-seq"/>



Of course you also have to include the "auto-increment" and
"entity-command" (why on earth do they call it that?). But you
already knew this part, because it's documented in the JBoss manual.


If you're using PostgreSQL version 7.3 that's basically the end of the
story. However, if you're still using 7.2 you'll have one more little
headache:

PostgreSQL automatically creates the sequence for you when you create
the table. But in version 7.2 it does not drop the sequence when the
table is dropped. Therefore, if you rely on JBoss to create and drop
the table for you automatically, you'll be wedged: the second time you
try to create it, the sequence will already exist, and so you'll get
an error.

My preference is not to have JBoss creating and dropping tables
anyway. Note that if you follow this practice, you don't even have to
configure JBoss to know about the "SERIAL" type. Just be sure to use
"SERIAL" when you create the table yourself (or manually do the
equivalent thing as explained in that section 5.1.4).

[b_boyle18]

Hi!
I was wondering if anyone could help me further with the configuration of jbosscmp-jdbc.xml for autogenerated primary keys. I am using PostgreSQL 7.2 and JBoss 3.0.8. I added the code in th eprevious post to my jbosscmp-jdbc.xml and it did not work. I got errors at deployment time like : Element "cmp-field" allows no further input; "auto-increment" is not allowed.

and

Element type "auto-increment" is not declared.

There were more aswell but I won't put them all up. I have set my pk field in postges to type serial and in my ejbcreate() method I don't have an argument for primary key. THe primary key return type is Object.
This is a snippet of my jbosscmp-jdbc.xml.

 <entity>
 <ejb-name>Candidate</ejb-name>
 <table-name>Candidate</table-name>

 <cmp-field>
 <field-name>cand_id</field-name>
 <column-name>cand_id</column-name>
 <jdbc-type>INTEGER</jdbc-type>
 <sql-type>serial</sql-type>
 <auto-increment/>
 </cmp-field>
 <entity-command name="postgresql-fetch-seq"/>

 <cmp-field>
 <field-name>firstName</field-name>
 <column-name>firstname</column-name>

Hope someone can help.
Thanks,
B