0 Replies Latest reply on Sep 3, 2003 9:48 PM by William Houck

    Primary Key Generation with MySQL

    William Houck Newbie

      Using entity commands in jbosscmp-jdbc.xml, can I auto generate a primary key without modifying my tables' sql create statement or the app itself? Stated another way: Does JBoss/MySQL offer a service, independent of my specific app, that will auto generate a unique value for use as a primary key?

      I'm using jboss-3.2.1_tomcat-4.1.24 and MySQL 4.0.14.

      Could one of these examples be modified somehow to answer the above question?

      1)
      This auto-increment technique doesn't quite meet the challenge. Notice that the INNODB attribute "auto_increment" has been used on column "id".


      <ejb-name>CustomerEJB</ejb-name>
      <table-name>CUSTOMER</table-name>
      <cmp-field>
      <field-name>id</field-name>
      <column-name>ID</column-name>
      <auto-increment/>
      </cmp-field>
      <entity-command name="mysql-get-generated-keys"/>


      CREATE TABLE `customer` (
      `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY (`id`)
      ) TYPE=InnoDB;


      2)
      The auto-increment sql (see tag entity-command) in this example is for the PostgreSQL database. Is there a similar sql statement for use with MySQL? Notice that the create statement was not modified to auto generate the primary key.


      <ejb-name>AccountEJB</ejb-name>
      <table-name>AccountEJBTable</table-name>
      <unknown-pk>
      <unknown-pk-class>java.lang.Long</unknown-pk-class>
      <field-name>__PMPrimaryKey</field-name>
      <column-name>__PMPrimaryKey</column-name>
      <jdbc-type>BIGINT</jdbc-type>
      <sql-type>BIGINT</sql-type>
      </unknown-pk>
      <entity-command name="pk-sql">
      SELECT nextval('generalSEQ')
      <!-- SELECT nextval('accountSEQ') -->
      </entity-command>



      CREATE TABLE AccountEJBTable (
      __PMPrimaryKey BIGINT,
      _contactInfo___PMPrimaryKey BIGINT,
      _creditCard___PMPrimaryKey BIGINT,
      status VARCHAR(255),
      CONSTRAINT pk_AccountEJBTabl PRIMARY KEY (__PMPrimaryKey));

      Finally, these techniques seem centered around pk's gen'd in sequence. What if I just want a guid generated, does that change the problem at all?

      Regards,
      Will