5 Replies Latest reply on Apr 23, 2004 10:03 AM by roger_cmu

    Auto Increment Primary Keys and MySQL

    Michael Forster Newbie

      I am sorry if this is in the wrong place but I couldn't work out how to add the information to the Wiki.

      To do AutoIncrement Primary Keys in MySQL in JBoss CMP is easy and you dont need to use Unknown -pk either

      How we did it.....

      First you define your ejb-jar Bean entry

      <entity>
      <ejb-name>UserBean</ejb-name>
      <home>UserHome</home>
      <remote>User</remote>
      <local-home>UserLocalHome</local-home>
      <local>UserLocal</local>
      <ejb-class>UserBean</ejb-class>
      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.Long</prim-key-class>
      <primkey-field>id</primkey-field>
      <reentrant>False</reentrant>
      <cmp-field><field-name>id</field-name></cmp-field>
      <cmp-field><field-name>name</field-name></cmp-field>
      <cmp-field><field-name>password</field-name></cmp-field>
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>UserBeanAPS</abstract-schema-name>
      <resource-ref>
      <res-ref-name>jdbc/mysql</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      </resource-ref>
      </entity>
      


      Then you have to create the jbosscmp-jdbc.xml entry

      <entity>
      <ejb-name>UserBean</ejb-name>
      <table-name>user</table-name>
      <cmp-field>
       <field-name>id</field-name>
       <column-name>user_id</column-name>
       <auto-increment/>
       <not-null/>
      </cmp-field>
      <cmp-field>
       <field-name>name</field-name>
       <column-name>user_name</column-name>
      </cmp-field>
      <cmp-field>
       <field-name>password</field-name>
       <column-name>user_password</column-name>
      </cmp-field>
      <entity-command name="mysql-get-generated-keys"
       class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand"/>
      </entity>
      


      The last entry in the entity declaration above is the magic command,

      and not an Unknown Primary Key in sight....

      I hope this information helps...

        • 1. Re: Auto Increment Primary Keys and MySQL
          roger_cmu Newbie

          I am getting some errors. For completeness could you please post the sql to create tables. I just want to sure that I am not doing anything wrong on the database side.

          - Roger

          • 2. Re: Auto Increment Primary Keys and MySQL
            roger_cmu Newbie

            The data is getting inserted but I get following exception:

            javax.ejb.EJBException: getGeneratedKeys returned an empty ResultSet
            at org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand.executeInsert(JDBCMySQLCreateCommand.java:77)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.performInsert(JDBCAbstractCreateCommand.java:287)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractCreateCommand.execute(JDBCAbstractCreateCommand.java:138)
            at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCStoreManager.java:554)
            at org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersistenceManager.java:208)


            ejb-jar.xml


            <display-name>EmployeeEJB</display-name>
            <ejb-name>EmployeeEJB</ejb-name>
            <local-home>employee.ejb.com.sbm.cts.EmployeeHome</local-home>
            employee.ejb.com.sbm.cts.Employee
            <ejb-class>employee.ejb.com.sbm.cts.EmployeeBean</ejb-class>
            <persistence-type>Container</persistence-type>
            <prim-key-class>java.lang.Integer</prim-key-class>
            False
            <cmp-version>2.x</cmp-version>
            <abstract-schema-name>EmployeeSchema</abstract-schema-name>
            <cmp-field><field-name>firstname</field-name></cmp-field
            <cmp-field><field-name>lastname</field-name></cmp-field>
            <cmp-field><field-name>telephone</field-name></cmp-field>
            <cmp-field><field-name>employeeid</field-name></cmp-field>

            <primkey-field>employeeid</primkey-field>

            <resource-ref>
            <res-ref-name>jdbc/ctsDB</res-ref-name>
            <res-type>javax.sql.DataSource</res-type>
            <res-auth>Container</res-auth>
            <res-sharing-scope>Shareable</res-sharing-scope>
            </resource-ref>


            jbosscmp-jdbc.xml:

            <ejb-name>EmployeeEJB</ejb-name>
            <table-name>employee</table-name>

            <cmp-field>
            <field-name>firstname</field-name>
            <column-name>firstname</column-name>
            </cmp-field>
            <cmp-field>
            <field-name>lastname</field-name>
            <column-name>lastname</column-name>
            </cmp-field>
            <cmp-field>
            <field-name>telephone</field-name>
            <column-name>telephone</column-name>
            </cmp-field>
            <cmp-field>
            <field-name>employeeid</field-name>
            <column-name>employeeid</column-name>
            <auto-increment/>
            <not-null/>
            </cmp-field>
            <entity-command name="mysql-get-generated-keys"
            class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand"/>


            code:

            public Integer ejbCreate(EmployeeDetails employeeDetails) throws CreateException {

            logger.debug("ejbCreate");

            try {
            BeanUtils.copyProperties(this, employeeDetails);
            } catch (java.lang.reflect.InvocationTargetException e) {
            logger.error(e.getMessage());
            } catch (Exception e) {
            logger.error(e.getMessage());
            }

            return null;
            }


            public void ejbPostCreate(EmployeeDetails employeeDetails) {
            logger.debug("ejbPostCreate");
            }

            // CMP field accessors

            public abstract String getFirstname();
            public abstract void setFirstname(String firstname);

            public abstract String getLastname();
            public abstract void setLastname(String lastname);

            public abstract String getTelephone();
            public abstract void setTelephone(String telephone);

            public abstract Integer getEmployeeid();
            public abstract void setEmployeeid(Integer employeeid);


            • 3. Re: Auto Increment Primary Keys and MySQL
              roger_cmu Newbie

              I am using MySQL database with mysql-connector-java-3.0.11-stable-bin.jar as the connector.

              - Roger

              • 4. Re: Auto Increment Primary Keys and MySQL
                Michael Forster Newbie

                The setting above that we use there is no script to create the tables, the CMP creates the tables on first deploy.

                and we are using the 3.1.1. Alpha jdbc connector

                and for your ejbCreate funtion our code would be as follows

                Long ejbCreate(details)
                {
                setName(details.getName());
                setAddress(details.getAddress());
                return null;
                }

                • 5. Re: Auto Increment Primary Keys and MySQL
                  roger_cmu Newbie

                  Thanks. Problem solved on my side too. I didn't realise that in jbosscmp-jdbc.xml when I wrote
                  <auto-increment/> for the key field, it's meant for the mysql table too. My key field on mysql was not auto-increment and hence the cause of problem.