Auto-increment on CMP EJB + Primary key

[madhup narain]

Hi all,

I working with CMP EJB and oracle.
I have a table that has an trigger to autoincrement the primary key.
In the create method of the EJB i am able to insert into the database.

Since i specify a dummy value for the primary key in the Create method for EJB the database actually assigns the next auto increment value. which for all purposes is fine with me.

Question ? .... How do i get the latest value for the primary key for the tuple that has been just inserted in the database. ?? Remember i put in a Dummy value but the real values is one inserted by database

is there any way i can get the values that i just stored in the DB in the create method.... in the same create call ???

EJB gurus please.. help me out.

[Jeroen T Wenting]

No, there isn't.
For some reason wiser men than me don't understand the sages at Sun decided that autoincrement values don't exist in real databases and therefore didn't allow for them in the EJB specification.

What I (and many others) do is create generators in my database which I call before inserting a new record, setting the primary key myself instead of using autoincrement fields.
Of course that immediately puts a database dependency into your code as such things are not part of standard SQL and therefore need custom logic for each RDBMS that supports them (not all do).

For Firebird I do something like this:
Create a generator and set it to initial value of 0.

CREATE GENERATOR UNIQUE_ID_GENERATOR;
SET GENERATOR UNIQUE_ID_GENERATOR TO 0;

In the EJB:

DBHelper dbHelper = DBHelperFactory.instance("Address");
setId(dbHelper.getUniqueIdentifier());

In the dbHelper (which is created for different generators as requested):

DataSource dataSource = getDataSource();
con = dataSource.getConnection();
st = con.prepareStatement("SELECT GEN_ID(UNIQUE_ID_GENERATOR, 1") FROM RDB$DATABASE");
rs = st.executeQuery();
if (rs != null && rs.next()) {
result = rs.getInt(1);
}

That at least concentrates all the engine specific code in a single place where it can be easily swapped out by instantiating another class from the factory.
This last bit of code of course (and the DDL) are extremely DMBS dependent and will only work with Interbase and Firebird.

[Justin Chu]

ejb containers may provide proprietary support for auto generation of primary keys, through oracle sequence, table managed sequences, etc.

If you are using weblogic/ejbgen, use

@ejbgen:automatic-key-generation type="Oracle" name="MY_SEQUENCE" cache-size="1"

[Krzysiek Hycnar]

That's right you can rely only on vendor specific support.

Below you've got an example how to do this with JBoss and PostgreSQL (I've got a link with explanation how to achieve the same with JBoss and Oracle, but it's on another comp, if you need it I can look for it).

What you must do to compile it is to add a com.hicnar.library.Author class which is a simple transfer-object bean (you can infer from the last method how it's built).

Good Luck
Krzysiek

/*
* Created on Feb 24, 2006
*/
package com.hicnar.library.ejb;
 
import java.rmi.RemoteException;
import java.util.Collection;
 
import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.RemoveException;
 
import com.hicnar.library.Author;
 
/**
* @ejb.bean name="AuthorEntity"
*           display-name="Name for AuthorEntity"
*           description="Description for AuthorEntity"
*           jndi-name="ejb/AuthorEntity"
*           type="CMP"
*           cmp-version="2.x"
*              reentrant="false"
*           view-type="both"
*              primkey-field="id"
*
* @ejb.pk class="java.lang.Integer" generate="false"
*
* @jboss.persistence table-name="AUTHORS"
* read-only="false"
* create-table="true"
* pk-constraint="true"
* datasource="java:jdbc/libraryDS"
* datasource-mapping="PostgreSQL"
*
* @ejb.finder view-type="both"
* signature="com.hicnar.interfaces.AuthorEntityLocal findByPrimaryKey(java.lang.Integer key)"
* @jboss.entity-command name="postgresql-fetch-seq"
*/
public abstract class AuthorEntity implements EntityBean {
 
   /** The entity context */
   private EntityContext context;
 
   /**
    *
    */
   public AuthorEntity() {
       super();
       // TODO Auto-generated constructor stub
   }
 
   /**
    * @throws EJBException Thrown if method fails due to system-level error.
    */
   public void setEntityContext(EntityContext newContext) throws EJBException {
       context = newContext;
   }
 
   /**
    * @throws EJBException Thrown if method fails due to system-level error.
    */
   public void unsetEntityContext() throws EJBException {
       context = null;
   }
 
   /* (non-Javadoc)
    * @see javax.ejb.EntityBean#ejbRemove()
    */
   public void ejbRemove()
       throws RemoveException,
       EJBException,
       RemoteException {
       // TODO Auto-generated method stub
 
   }
 
   /* (non-Javadoc)
    * @see javax.ejb.EntityBean#ejbActivate()
    */
   public void ejbActivate() throws EJBException, RemoteException {
       // TODO Auto-generated method stub
 
   }
 
   /* (non-Javadoc)
    * @see javax.ejb.EntityBean#ejbPassivate()
    */
   public void ejbPassivate() throws EJBException, RemoteException {
       // TODO Auto-generated method stub
 
   }
 
   /* (non-Javadoc)
    * @see javax.ejb.EntityBean#ejbLoad()
    */
   public void ejbLoad() throws EJBException, RemoteException {
       // TODO Auto-generated method stub
 
   }
 
   /* (non-Javadoc)
    * @see javax.ejb.EntityBean#ejbStore()
    */
   public void ejbStore() throws EJBException, RemoteException {
       // TODO Auto-generated method stub
 
   }
 
   /**
    *
    * @param author
    * @return
    * @throws CreateException
    *
    * @ejb.create-method
    */
   public Integer ejbCreate(Author author) throws CreateException {
       this.setFirstName(author.getFirstName());
       this.setLastName(author.getLastName());
       return null;
   }
 
   public abstract Collection getBooks();
   public abstract void setBooks(Collection books);
 
   /**
    * @ejb.persistence column-name="FIRST_NAME" jdbc-type="VARCHAR"
    *
    * @jboss.column-name name="FIRST_NAME"
    * @jboss.jdbc-type type="VARCHAR"
    *
    * @return Returns the firstName.
    */
   public abstract String getFirstName();
 
   /**
    * @param firstName The firstName to set.
    */
   public abstract void setFirstName(String firstName);
 
   /**
 
    *
    * @ejb.interface-method
    * @ejb.persistence column-name="id" jdbc-type="INTEGER"
 
    * @jboss.persistence auto-increment = "true"
    * @jboss.jdbc-type type="INTEGER"
    * @jboss.sql-type type="SERIAL"
    * @ejb.pk-field
    *
    * @return Returns the id.
    */
   public abstract Integer getId();
 
   /**
    * @param id The id to set.
    */
   public abstract void setId(Integer id);
 
   /**
    * @ejb.persistence column-name="LAST_NAME" jdbc-type="VARCHAR"
    *
    * @jboss.column-name name="LAST_NAME" jboss.jdbc-type type="VARCHAR"
    *     * @return Returns the lastName.
    */
   public abstract String getLastName();
 
   /**
    * @param lastName The lastName to set.
    */
   public abstract void setLastName(String lastName);
     /**
    * @ejb.interface-method view-type="both"
    */
   public Author getAuthor() {
       return new Author(
               this.getId(),
               this.getFirstName(),
               this.getLastName());
   }
}

[Tiago Sudeval]

What's Jboss version i can do it ? ( Krzysiek Hycnar )