4 Replies Latest reply on Apr 22, 2004 2:25 AM by sirvaulterscoff

    autogenerated pk's with cmp entity beans ?

    rlaenen

      Hi,

      I would like to make use of db-generated pk values in combination with cmp entity beans.

      I want to avoid to do a supplementary query to the db for retrieving the next-key value in the ejbCreate method.
      Instead, i would like the app. server to make use of the jdbc 'returning' feature to get the pk-value assigned by the db on the insert.

      I've been searching through the forums and in the docs but don't find a clear answer.

      Does anybody know if this is possible and how to do it ???

      Roger.

        • 1. Re: autogenerated pk's with cmp entity beans ?

          I haven't tried it but the 3.2.x jbosscmp-jdbc DTD supports this. Have a look at that file.

          • 2. Re: autogenerated pk's with cmp entity beans ?
            rlaenen

            After putting

            <entity-command name="get-generated-keys"/>

            in the defaults section of jbosscmp-jdbc and

            <unknown-pk>
            <unknown-pk-class>java.lang.Integer</unknown-pk-class>
            <field-name>id</field-name>
            <auto-increment/>
            </unknown-pk>

            in the entity section and deploying the stuff,
            the Oracle 9.2 jdbc driver came back with an 'unsupported feature' error.

            After looking in the jBoss source code the offending statement was :

            ps = con.prepareStatement(insertEntitySQL,
            PreparedStatement.RETURN_GENERATED_KEYS);

            Conclusion, the default Oracle 9 drivers don't support the jdbc type 3 feature that jBoss is using.
            I suppose this is linked with the fact that Oracle doesn't know auto-increment columns but makes use of sequences for this.

            So, I think we'll need to live with that extra roundtrip to the db for getting the pk-value.






            • 3. Re: autogenerated pk's with cmp entity beans ?
              stephaner

              Hi,

              It is a way to configure a autogenerated key for an entity bean for Oracle.

              First, you have to create a sequence in DB to generate your PK.

              In jbosscmp-jdbc.xml, you have to add the following elements to your entity definition:

              Code:



              <unknown-pk>
              <unknown-pk-class>java.lang.Integer</unknown-pk-class>
              <column-name>DB_column_name</column-name>
              <jdbc-type>INTEGER</jdbc-type>
              <sql-type>NUMBER(8)</sql-type>
              </unknown-pk>
              <entity-command name="oracle-sequence" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
              <attribute name="sequence"\>your_sequence_name
              </entity-command>



              • 4. Re: autogenerated pk's with cmp entity beans ?
                sirvaulterscoff

                Its interesting for me to know weather i could use 2 sequinces for one table. I.e. i need one row to have primary key between 1 and 1000000 and the second row - between 1000001 - inf.