9 Replies Latest reply on Jan 25, 2004 9:45 AM by b_boyle18

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

    lhaas Newbie

      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 !!!

        • 1. Re: How do I use EJB CMP with an autogenerated PK (in postgr
          marcuslarsson Newbie

          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




          • 2. Re: How do I use EJB CMP with an autogenerated PK (in postgr
            marcuslarsson Newbie

            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




            • 3. Re: How do I use EJB CMP with an autogenerated PK (in postgr
              garbelini Newbie

              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

              • 4. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                thorsten maus Newbie

                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)

                • 5. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                  Shai Almog Newbie

                  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.

                  • 6. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                    Alwyn Schoeman Newbie

                    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?

                    • 7. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                      wunderkind Newbie

                      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...

                      • 8. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                        Alan Bram Newbie

                        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).

                        • 9. Re: How do I use EJB CMP with an autogenerated PK (in postgr
                          b_boyle18 Newbie

                          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