13 Replies Latest reply on Mar 26, 2004 4:56 PM by sharky44

    Auto Increment primary key in postgresql:  Functional exampl

    Adam Young Newbie

      The following code works. It is the result of pulling together all the various references on how to get Auto increment and XDoclet working. Hope it helps.

      /*
      * This file is Copyright Adam Young.
      * Contact adam@younglogic.com
      */
      package com.younglogic.eventful.ejb;
      import java.rmi.RemoteException;
      import java.sql.Date;
      import javax.ejb.CreateException;
      import javax.ejb.EJBException;
      import javax.ejb.EntityBean;
      import javax.ejb.EntityContext;
      import javax.ejb.RemoveException;
      /**
      * @ejb.bean
      * name="ScheduledEvent"
      * local-jndi-name="Eventful/ScheduledEvent"
      * view-type="local"
      * type="CMP"
      * primkey-field="id"
      *
      * @ejb.persistence
      * table-name="scheduled_event"
      * @jboss.unknown-pk
      * class="java.lang.Integer"
      * auto-increment="true"
      * sql-type="serial"
      * jdbc-type="INTEGER"
      * @jboss.entity-command
      * name="postgresql-fetch-seq"
      *
      * @ejb.value-object
      * match="*"
      * @ejb.pk
      * class="java.lang.Integer"
      * generate="false"
      * @ejb.finder
      * signature="java.util.Collection findByDate(java.sql.Date d)"
      * query="SELECT OBJECT(e) from ScheduledEvent e where e.date = ?1"
      * unchecked="true"
      * @ejb.finder
      * signature="java.util.Collection findDateInRange(java.sql.Date fromDate,java.sql.Date toDate)"
      * query="SELECT OBJECT(e) from ScheduledEvent e where e.date = ?1 or ( e.date > ?1 and e.date < ?2 ) or e.date = ?2 "
      * unchecked="true"
      */
      public abstract class ScheduledEventBean implements EntityBean {
      private EntityContext entityContext;
      /**
      * TODO add a role for this method
      * @ejb.permission unchecked="true"
      * @ejb.create-method
      * @param beanPK
      */
      public Integer ejbCreate(ScheduledEventValue value)
      throws CreateException {
      setDate(value.getDate());
      setDescription(value.getDescription());
      return null;
      }
      public void ejbPostCreate(ScheduledEventValue value)
      throws CreateException {
      }
      /**
      * @ejb.persistence
      * sql-type="serial"
      * jdbc-type="INTEGER"
      */
      public abstract Integer getId();
      /**
      * @ejb.persistence
      * sql-type="serial"
      * jdbc-type="INTEGER"
      */
      public abstract void setId(Integer key);
      /**
      * @ejb.persistence
      */
      public abstract String getDescription();
      /**
      * @ejb.persistence
      */
      public abstract void setDescription(String description);
      /**
      * @ejb.persistence
      */
      public abstract Date getDate();
      /**
      * @ejb.persistence
      */
      public abstract void setDate(Date date);
      /**
      * @ejb.interface-method view-type="local"
      * @ejb.permission unchecked="true"
      */
      public abstract ScheduledEventValue getScheduledEventValue();
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#ejbActivate()
      */
      public void ejbActivate() throws EJBException, RemoteException {
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#ejbLoad()
      */
      public void ejbLoad() throws EJBException, RemoteException {
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#ejbPassivate()
      */
      public void ejbPassivate() throws EJBException, RemoteException {
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#ejbRemove()
      */
      public void ejbRemove()
      throws RemoveException, EJBException, RemoteException {
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#ejbStore()
      */
      public void ejbStore() throws EJBException, RemoteException {
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#setEntityContext(javax.ejb.EntityContext)
      */
      public void setEntityContext(EntityContext entityContext)
      throws EJBException, RemoteException {
      this.entityContext = entityContext;
      }
      /* (non-Javadoc)
      * @see javax.ejb.EntityBean#unsetEntityContext()
      */
      public void unsetEntityContext() throws EJBException, RemoteException {
      this.entityContext = null;
      }
      }



      Note that I am creating my DB tables myself, so I don't speak to the table generation. Here is the SQL for that:


      CREATE TABLE scheduled_event (id serial primary key, description varchar,Date date);


      And here is the XDoclet Ant portion of my Ant script: I replaced angle brackets with square

      [target name="ejbdoclet"]
      [taskdef
      name="ejbdoclet"
      classname="xdoclet.modules.ejb.EjbDocletTask" classpathref="xdoclet.path"/]
      [mkdir dir=""/]
      [ejbdoclet
      destdir=""
      excludedtags="@version,@author"
      ejbspec=""
      mergedir="/xdoclet"
      force=""
      ]
      [fileset dir=""]
      [include name="**/*Bean.java"/]
      [/fileset]
      [packageSubstitution packages="session,entity" substituteWith="interfaces"/]
      [valueobject/]
      [remoteinterface/]
      [homeinterface/]
      [localinterface/]
      [localhomeinterface/]
      [entitypk/]
      [entitybmp/]
      [entitycmp/]
      [deploymentdescriptor destdir="/META-INF"/]
      [jboss version=""
      xmlencoding="UTF-8"
      typemapping=""
      datasource=""
      destdir="/META-INF"
      validateXml="true"
      securityDomain="java:/jaas/younglogic"
      />
      [/ejbdoclet]
      [/target]

        • 1. Re: Auto Increment primary key in postgresql:  Functional ex
          bobsworld Newbie

          It took me a long time to get cmp at work with auto_increment PK's. But now I have the next problem.

          How do you get CMR at work with auto_increment keys?

          example (one-to-one unidirectional relationship):

          User:
          * id (PK auto_increment)
          * Lastname
          * Firstname
          * address
          * groupid (foreign-key to group.id)

          Group:
          * id (PK auto_increment)
          * name

          • 2. Re: Auto Increment primary key in postgresql:  Functional ex
            mandarjboss Newbie

            Hi,
            bobsworld,
            Can you share your solution to auto-increment primary key problem with me.Beacuse when i tried the example given here i get the error as

            Could not create entity:java.sql.SQLException: ERROR: ExecInsert: Fail to add null value in not null attribute id

            I feel that is obvoius beacuse u r not inserting any value thr'o ejbCreate into the column "id" i.e absence of call "setId(....)" in ejbCreate.
            As id is declared as primary key it has to be not null.So from the error message u can easily identify the problem.
            I am using postgreSQL database.With my databse the table definition becomes,

            CREATE TABLE public.trialquestion (
            id int4 DEFAULT nextval('public.trialquestion_id_seq'::text) NOT NULL,
            CONSTRAINT trialquestion_pkey PRIMARY KEY (id)
            );

            Can you help me to solve the problem?

            • 3. Re: Auto Increment primary key in postgresql:  Functional ex
              bobsworld Newbie

              Here are 2 classes. one is called CustomerBean and the other is TicketBean. They are both commented with xdoclet and the relation between them is a unidirectional 1-many-relation (one customer with many tickets). The tables are automaticly created by jboss for mysql (example works only for MySQL).

              CustomerBean holds all the customerinformation (email, password, lastnamen and firstname).
              TicketBean holds all the ticketinformation for eg a concert (name of ticket, price and number of tickets).

              The example isn't finished, but you'll get an idea.

              CustomerBean:

              /*
               * Created on 25-feb-2004
               *
               */
              package ejb.entity;
              
              import javax.ejb.CreateException;
              import javax.ejb.EntityBean;
              
              /**
               *
               * @ejb.bean
               * cmp-version = "2.x"
               * jndi-name = "ejb/entity/CustomerBean"
               * name = "Customer"
               * primkey-field = "id"
               * schema = "CustomerSchema"
               * type = "CMP"
               * view-type = "local"
               *
               * @ejb.persistence
               * table-name = "Customer"
               *
               * @jboss.entity-command
               * name = "mysql-get-generated-keys"
               *
               * @ejb.finder
               * signature = "java.util.Collection findAll()"
               * query = "select object(c) from CustomerSchema as c"
               *
               * @ejb.finder
               * signature = "ejb.CustomerBean findByEmail(java.lang.String email)"
               * query = "select object(c) from CustomerSchema as c where c.email = ?1"
               */
              public abstract class CustomerBean implements EntityBean {
              
               /**
               * @param email
               * @param password
               * @param lastname
               * @param firstname
               * @return
               * @throws CreateException
               *
               * @ejb.create-method
               */
               public Integer ejbCreate(String email, String password, String lastname, String firstname) throws CreateException {
               setEmail(email);
               setPassword(password);
               setLastname(lastname);
               setFirstname(firstname);
               return null;
               }
              
               /**
               * @throws CreateException
               */
               public void ejbPostCreate() throws CreateException {
               }
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.pk-field
               * @ejb.persistence
               * column-name = "id"
               * sql-type = "INT"
               * @jboss.persistence
               * auto-increment = "true"
               */
               public abstract Integer getId();
              
               /**
               * @param id
               */
               public abstract void setId(Integer id);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "email"
               * sql-type = "varchar"
               * @ejb.persistent-field
               */
               public abstract String getEmail();
              
               /**
               * @param email
               *
               * @ejb.interface-method
               */
               public abstract void setEmail(String email);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "password"
               * sql-type = "varchar"
               * @ejb.persistent-field
               */
               public abstract String getPassword();
              
               /**
               * @param password
               *
               * @ejb.interface-method
               */
               public abstract void setPassword(String password);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "lastname"
               * sql-type = "varchar"
               * @ejb.persistent-field
               */
               public abstract String getLastname();
              
               /**
               * @param lastname
               *
               * @ejb.interface-method
               */
               public abstract void setLastname(String lastname);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "firstname"
               * sql-type = "varchar"
               * @ejb.persistent-field
               */
               public abstract String getFirstname();
              
               /**
               * @param firstname
               *
               * @ejb.interface-method
               */
               public abstract void setFirstname(String firstname);
              
               /**
               * @return
               *
               * @ejb.interface-method
               *
               * @ejb.relation
               * name="CustomerHasTicketsRelation"
               * role-name="the-ticket-of"
               * target-ejb = "Ticket"
               * target-role-name = "the-customer-of"
               */
               public abstract java.util.Collection getTickets();
              
               /**
               * @param newTickets
               *
               * @ejb.interface-method
               */
               public abstract void setTickets(java.util.Collection newTickets);
              }





              TicketBean:
              /*
               * Created on 25-feb-2004
               *
               */
              package ejb.entity;
              
              import javax.ejb.CreateException;
              import javax.ejb.EntityBean;
              
              /**
               * @ejb.bean
               * cmp-version = "2.x"
               * jndi-name = "ejb/entity/TicketBean"
               * name = "Ticket"
               * primkey-field = "id"
               * schema = "TicketSchema"
               * type = "CMP"
               * view-type = "local"
               *
               * @ejb.persistence
               * table-name = "Ticket"
               *
               * @jboss.entity-command
               * name = "mysql-get-generated-keys"
               *
               * @ejb.finder
               * signature = "java.util.Collection findAll()"
               * query = "select object(t) from TicketSchema as t"
               */
              public abstract class TicketBean implements EntityBean {
              
               /**
               * @return
               * @throws CreateException
               *
               * @ejb.create-method
               */
               public Object ejbCreate(String name, Double price, Integer count)
               throws CreateException {
               setName(name);
               setPrice(price);
               setCount(count);
               return null;
               }
              
               /**
               * @throws CreateException
               */
               public void ejbPostCreate() throws CreateException {
              
               }
              
               /**
               * @return
               *
               * @ejb.pk-field
               *
               * @ejb.persistence
               * column-name = "id"
               * sql-type = "INT"
               *
               * @jboss.persistence
               * auto-increment = "true"
               *
               * @ejb.interface-method
               */
               public abstract Integer getId();
              
               /**
               * @param id
               */
               public abstract void setId(Integer id);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "name"
               * sql-type = "varchar"
               * @ejb.persistent-field
               */
               public abstract String getName();
               /**
               * @param name
               *
               * @ejb.interface-method
               */
               public abstract void setName(String name);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "price"
               * sql-type = "float"
               */
               public abstract Double getPrice();
              
               /**
               * @param price
               *
               * @ejb.interface-method
               */
               public abstract void setPrice(Double price);
              
               /**
               * @return
               *
               * @ejb.interface-method
               * @ejb.persistence
               * column-name = "count"
               * sql-type = "int"
               */
               public abstract Integer getCount();
              
               /**
               * @param count
               *
               * @ejb.interface-method
               */
               public abstract void setCount(Integer count);
              
               /**
               * @return
               *
               * @ejb.interface-method
               *
               * @ejb.relation
               * name = "CustomerHasTicketsRelation"
               * role-name = "the-customer-of"
               * target-ejb = "Customer"
               * target-role-name = "the-ticket-of"
               *
               * @jboss.relation
               * fk-constraint = "false"
               * related-pk-field = "id"
               * fk-column = "customerid"
               */
               public abstract CustomerLocal getCustomer();
              
               /**
               * @param newCustomer
               *
               * @ejb.interface-method
               */
               public abstract void setCustomer(CustomerLocal newCustomer);
              }


              • 4. Re: Auto Increment primary key in postgresql:  Functional ex
                mandarjboss Newbie

                Hi,
                bobsworld,
                Thanks a lot for sharing such a valuable code.I think if u can post this code separately with title such as "CMP auto-pk increment Works....!"
                it will help many JBoss developers and will find it extremely useful like me.You will receive bunch of thanks everyday.

                Well,you have used,

                @jboss.entity-command
                * name = "mysql-get-generated-keys"

                I have a Jboss3.2 documentation.When i looked into it i found that there is a separate "entity-command-name" for every database
                e.g. One which u have used is only for My-Sql.
                But i could not found any such entity-command-name for PostgreSQL.Can u help me regarding that?

                Have you seen the workaround solution for auto-incrementing primary keys at "pirack.com"

                I tried that solution but then JBoss is throwing,
                Jboss.TransactionRollBackedException when i execute my application.
                Please help me if u can?


                • 5. Re: Auto Increment primary key in postgresql:  Functional ex
                  bobsworld Newbie

                   

                  "mandarjboss" wrote:
                  Hi,
                  bobsworld,
                  Thanks a lot for sharing such a valuable code.I think if u can post this code separately with title such as "CMP auto-pk increment Works....!"
                  it will help many JBoss developers and will find it extremely useful like me.You will receive bunch of thanks everyday.

                  Well,you have used,

                  @jboss.entity-command
                  * name = "mysql-get-generated-keys"

                  I have a Jboss3.2 documentation.When i looked into it i found that there is a separate "entity-command-name" for every database
                  e.g. One which u have used is only for My-Sql.
                  But i could not found any such entity-command-name for PostgreSQL.Can u help me regarding that?


                  I found this example on the internet. I haven't tested because I don't have a postgreserver running (and don't have the time to set one up).

                  I hope this can help you some more.

                  /**
                   * @ejb.bean
                   * name="MyBean"
                   * type="CMP"
                   * cmp-version="2.x"
                   * schema="MyBean"
                   * view-type="both"
                   * reentrant="false"
                   * jndi-name="/MyBeanHome"
                   * local-jndi-name="/MyBeanLocalHome"
                   * primkey-field="id"
                   *
                   * @ejb.transaction
                   * type="Required"
                   *
                   * @ejb.persistence
                   * table-name="t_mybean"
                   *
                   * @ejb.pk
                   * generate="false"
                   *
                   * @jboss.entity-command
                   * name="postgresql-fetch-seq"
                   */
                  public abstract class MyBean implements EntityBean
                  {
                  
                   ...
                  
                   /**
                   * @ejb.interface-method
                   *
                   * @ejb.persistence
                   * sql-type="SERIAL"
                   * jdbc-type="INTEGER"
                   *
                   * @jboss.persistence
                   * auto-increment="true"
                   * sql-type="SERIAL"
                   * jdbc-type="INTEGER"
                   *
                   * @ejb.value-object
                   * match="Movie"
                   */
                   public abstract Integer getId();
                   public abstract void setId(Integer id);
                  
                   ...
                  


                  Have you seen the workaround solution for auto-incrementing primary keys at "pirack.com"

                  I tried that solution but then JBoss is throwing,
                  Jboss.TransactionRollBackedException when i execute my application.
                  Please help me if u can?


                  Sorry, but I haven't seen workaround of "pirack.com". I hope the previous example helps U.

                  Regards,
                  BoB

                  • 6. Re: Auto Increment primary key in postgresql:  Functional ex
                    mandarjboss Newbie

                    Hi bobs,
                    Thanx for ur reply.
                    I tried ur example but unfortunately it does not work.
                    Jboss is still throwing the same exception,

                    18:48:46,000 INFO [STDOUT] Could not create entity:java.sql.SQLException: ERROR: ExecInsert: Fail to add null value in not null attribute id
                    18:48:46,000 ERROR [Engine] StandardWrapperValve[TrialSerrvlet]: Servlet.service() for servlet TrialSerrvlet threw exception
                    javax.servlet.ServletException: Could not create entity:java.sql.SQLException: ERROR: ExecInsert: Fail to add null value in not null attribute id

                    Should we conclude that there is no way to implement the auto-increment of primary-key in this way?
                    I am really fed-up of this problem.

                    Is there any other alternative way, some indirect workaround , so that i can get rid of this problem.

                    In some articles on the net i read about some specialized Design Patterns for solving this problem.
                    Do you have any idea?

                    • 7. Re: Auto Increment primary key in postgresql:  Functional ex
                      bobsworld Newbie

                      It took me more then a week for the CMP's to work with auto-increment pk's, so don't quite to soon ;-)

                      You could also make a primarykey-generateclass for your primarykeys, but I red some discussions about patterns and anti-patterns.

                      If I find the page with the examples, I'll post them here.

                      BoB

                      • 8. Re: Auto Increment primary key in postgresql:  Functional ex
                        Adam Young Newbie

                        Well, I set up Nukes to let me know when someone responded, and just got this message now. Sorry for missing the discussion.

                        I had the same error, with the attempt to insert the null primary key. If you look at your database in psql, you should see the following:


                        (this is out of a pgdump)

                        CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler
                        AS '$libdir/plpgsql', 'plpgsql_call_handler'
                        LANGUAGE c;

                        As far as I can tell, this is triggered by the xdoclet tag:

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

                        But I've also found references to it in standardjbosscmp-jdbc.xml.

                        I had to shut down the JBoss server and restart it as well, as I think it got in an unstable state. I'm using 3.2.3.


                        I'll dig deeper and see what else I've done, but I do have autoinc working. Not for realtionships yet, thoughj .




                        • 9. Re: Auto Increment primary key in postgresql:  Functional ex
                          mandarjboss Newbie

                          Hi admiyo,
                          You have written that you have auto-increment working.I would like to know which version of PostgreSQL u r using ?
                          Can u post ur working example here?

                          • 10. Re: Auto Increment primary key in postgresql:  Functional ex
                            Sebastiaan Kortleven Newbie

                            I believe you have to make sure all your columns are nullable (except the PK column ofcourse) since JBoss will first create an empty record, and fill the fields up later...

                            • 11. Re: Auto Increment primary key in postgresql:  Functional ex
                              mandarjboss Newbie

                              Hi Aquilla
                              Just give me a couple of days.I am busy with some other work and will get back to you soon.

                              • 12. Re: Auto Increment primary key in postgresql:  Functional ex
                                mandarjboss Newbie

                                Hi Aquilla

                                Hey!Today i am vey happy.Ultimately I got my example working with Auto-increment of primary key.
                                Logic given at Pirack.com is working.
                                If you want you can just have a look at,

                                http://www.pirack.com/viewHowto.xphp?id=3

                                • 13. Re: Auto Increment primary key in postgresql:  Functional ex
                                  sharky44 Newbie

                                  If you're struggling to get these examples to work with XDoclet (like I have for a week or so now), be sure that your JBoss version is set to 3.2 and not to 3.0 in whichever one of your ant xml files has it (I'm using Lomboz, so it's in xdoclet.xml). If it's set to 3.0, xdoclet pretty much ignores any auto-increment stuff when it builds the deployment descriptors. Good luck.