5 Replies Latest reply on Jun 23, 2004 7:32 PM by John Anderson

    Auto-increment Pickle

    John Anderson Newbie

      Hello all,

      I seem to a few choices I have to make to skirt around some issues with using the auto-increment methods of DBs. Here's my problem:

      Consider the following CMP bean snippet including the xdoclet code:

      /**
       * @author janderson
       *
       * @ejb.bean
       * display-name = "Ticket EJB"
       * description = "Getters and setters for tickets"
       * name = "Ticket"
       * view-type = "local"
       * jndi-name = "ejb/CallCenter/Core/Ticket"
       * type = "CMP"
       * cmp-version = "2.x"
       * primkey-field = "ID"
       * schema = "TICKET"
       * @ejb.persistence
       * table-name = "TICKET"
       * @jboss.persistence
       * create-table = "true"
       * table-name = "TICKET"
       * @ejb.finder
       * query = "Select Object(o) FROM TICKET o"
       * signature = "java.util.Collection findAll()"
       * unchecked = "true"
       *
       */
      
      public abstract class TicketBean implements EntityBean {
      
       /**
       * This class will hold generic TicketBean information.
       */
       public TicketBean() {
       super();
       }
       /**
       * @ejb.persistence
       * column-name = "ID"
       * jdbc-type = "BIGINT"
       * sql-type = "BIGINT"
       *
       * @jboss.persistence
       * auto-increment = "true"
       * not-null = "true"
       * @ejb.interface-method
       * @return
       */
       public abstract Long getID();
      
      
       public abstract void setID(Long ID);
       /**
       * @ejb.persistence
       * column-name = "DESCRIPTION"
       * jdbc-type = "VARCHAR"
       * sql-type = "VARCHAR(100)"
       * @jboss.persistence
       * not-null = "true"
       * @ejb.interface-method
       * @return
       */
       public abstract String getDesc();
       /**
       * @ejb.interface-method
       * @param Desc
       */
       public abstract void setDesc(String Desc);
      
       /**
       * @ejb.create-method
       * @param s
       * @throws CreateException
       */
      
       public Long ejbCreate(String s) throws CreateException{
       setDesc(s);
       return null;
       }
      


      This code basically creates a CMP bean with two fields. Now check out the ejbCreate() method again. It only set's the Desc field, not the ID field wich is the primary key! The database system should be providing the Primary Key field through the <auto-increment/>. Every time I create a new bean, a new record in the database is created and the Primary key is there and incremented. However, I get a Nullpointerexception because the container has no way of knowing that the primary key of the record I just set is not null.

      Anyway, I'm positive I'm not the first to run into this sort of thing. Here are my proposed solutions.

      1) Write a session bean to keep track of primary keys and generated auto-incremented primary keys. But this seems like a lot of work re-writing an auto-increment system when I could just use the host RDBMS's.

      2.)Write a bean that implements methods in the JDBC drivers to determine what the next auto-incremented number in a table would be. This would seriously hinder database indepence.

      Is there some other way that I'm just not seeing? Is there a way to allow records to be committed with unknown primary keys? What about null primary keys?

      I'm half-sure there could be a simple solution to this, but I haven't found it yet. Half of what I read suggested just writing your own auto-increment system.

        • 1. Re: Auto-increment Pickle
          jae Master

          are you by chance using postgres as your database?

          • 2. Re: Auto-increment Pickle
            John Anderson Newbie

             

            "jae77" wrote:
            are you by chance using postgres as your database?


            No, my testbed is MySQL, but I want to make this app database independent. Well, any database that supports auto-increment that is.

            • 3. Re: Auto-increment Pickle
              Mark Miller Newbie

              You need to add somehting similiar to this to the bean level xdoclet tag, these are the settings for postgres, I do not think there is an entirely database independent way to do this:

              @jboss.unknown-pk
              class="java.lang.Integer"
              auto-increment="true"
              jdbc-type="INTEGER"
              sql-type="SERIAL"

              @jboss.entity-command
              name="postgresql-fetch-seq"


              Hope this helps,
              Mark

              • 4. Re: Auto-increment Pickle
                jae Master

                i see what the problem is now, you're missing the @jboss.entity-command tag that will be used to generate the primary key.

                millerm1 is also correct w/ the settings for postgres. you're best bet is going to be to define the jdbc-type, sql-type, and entity-command as ant properties ${xxx} and then let the build process substitute them based upon the database you are using.

                also, if you plan to have foreign key relationships in the database (and you are using postgres) there are some other little nasties you have to deal w/ as well.

                check out this wiki page http://www.jboss.org/wiki/Wiki.jsp?page=NukesPureCMP

                it's primarily nukes specific, but does cover the postgres problems that you will encounter should be helpful in that reguard.

                • 5. Re: Auto-increment Pickle
                  John Anderson Newbie

                  Thank you both. I will definately look into this possible fix as well.


                  I may end up just coding some session beans and taking all of this pk generation away from the RDBMS. This will allow me to both control the recylcling of these primary keys when records are purged off, as well as allow me to distribute a single .ear file with several ????-ds.xml files as a binary distro, instead of using the ant props. Allowing people a "drop-in" installation.