Key Generators

JBoss CMP supports the use of key generators to automatically provide values for EJBs that have an Unknown Primary Key. Currently the following mechanisms are provided:

Configuring an Entity to use a Key Generator

There are two steps to configuring a CMP entity to use a key generator:

1. Defining the Primary Key
2. Associating an Entity with a key generator

The primary key is defined in jbosscmp-jdbc.xml using an unknown-pk element. This specifies the actual class to use, which field and column to map the key to and the JDBC data type for the column. The following XML snippet illustrates the definition of a Integer primary key for the Order EJB:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        NUMBER(10)
      

The unknown primary key can be linked to an existing cmp-field by specifying a field-name element. This allows access to the primary key value via that fields accessor. If no field-name is given, a virtual field is defined which has the name of the EJB concatenated with "_upk"; for example, for the "Order" EJB, the virtual field would be called "Order_upk".

A key generator is configured in jbosscmp-jdbc.xml by associating an entity-command with a specific entity bean . This association can be done at one of three levels:

• At the Container level in standardjbosscmp-jdbc.xml
• At the ejb-jar level in jbosscmp-jdbc.xml
• At the entity level for a single entity in jbosscmp-jdbc.xml

Attributes for each command can also be specified at each level and more detailed definitions override the higher level ones. For example, you could define at the ejb-jar level that you wish to use a Oracle key generator, and then for each entity define a different sequence to use.

The following XML snippet illustrates the definition of primary keys and an Oracle key generator for Order and LineItem entities:

  
    
      Order
 
      
      
        java.lang.Integer
        ORDER_ID
        INTEGER
        NUMBER(10)
      
 
      
      
        ORDER_SEQUENCE
      
    

    
      LineItem
 
      
      
        java.lang.Integer
        ITEM_ID
        INTEGER
        NUMBER(10)
      
 
      
      
        ITEM_SEQUENCE
      
    
  
 
  
    
                        class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
    
  

Key Generator Command

The Key Generator command uses an external key generator to obtain primary key values. Once ejbCreate returns, CMP calls the generateKey() method to obtain a new key value and sets the unknown-pk field to the returned value. This field is then inserted into the database along with other fields that were initialized in ejbCreate().

JBoss includes a simple key generator that generates a UUID comprising a 32 character long globally unique String. The entity-command definition to access this generator is:

                class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCKeyGeneratorCreateCommand">
  UUIDKeyGeneratorFactory

PkSQL Command

The PkSQL command generates a new primary key value by issuing an SQL query against the database. The query should return a single row with a single column that can be fetched using a ResultSet. The returned value is set into the unknown-pk field and then inserted into the database.

As an example, the following command uses HSQLDB's ability to call Java methods to generate a primary key using the current time:

  CALL "java.lang.System.currentTimeMillis"()

JDBC3.0 Generated Keys Command

The JDBC 3.0 Generated Keys command uses the getGeneratedKeys() functionality that was added to JDBC 3.0; this requires both JDK 1.4 and a driver that supports this functionality.

The command operates by inserting all cmp-fields except the primary key into the database and then calling the driver to obtain the value generated by the insert. No addition configuration is required; the standard command definition in standardjbosscmp-jdbc.xml can be used.

                class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBC30GeneratedKeysCreateCommand"/>

HSQLDB Generator Command

The HSQLDB generator operates by fetching the value generated by a IDENTITY column during the insert of the row. If JBoss is going to create the table, the unknown-pk field must be defined as auto-increment:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        INTEGER
        
      
      

This will result in an "IDENTITY" column being generated. If the table is not being created by JBoss, ensure the primary key column is an IDENTITY column.

The standard command definition in standardjbosscmp-jdbc.xml can normally be used:

                class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand">
  

Informix Generator Command

The Informix key generator inserts the cmp-field values and then uses a vendor-specific method on the PreparedStatement to access the last inserted value. This requires that the column for the primary key field is defined as a SERIAL datatype; if JBoss is creating the table then the unknown-pk element must be used to define the correct column type as follows:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        SERIAL
      
      

Long values are also supported with the SERIAL8 datatype. The method attribute of the entity-command must also be changed to invoke the getSerial8 method:

      
        java.lang.Long
        ORDER_ID
        BIGINT
        SERIAL8
      
      
        getSerial8
      

MySQL Create Command

The MySQL key generated uses the getGeneratedKeys() method on the driver's implementation of PreparedStatement to retrieve the last value inserted into an auto-increment column. This is esssentially the same functionality as the JDBC 3.0 Key Generator but also works under JDK 1.3.

The primary key column must be defined as being auto-increment, and if JBoss is creating the table the auto-increment element must be specified:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        INTEGER
        
      
      

Oracle Create Command

The Oracle key generator uses a sequence to supply the primary key value during the insert and uses a RETURNING clause to return the generated value. The unknown-pk element must be used to define the primary key column name and type:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        NUMBER(10)
      
      
        ORDER_SEQ
      

The statement executed will be similar to:

INSERT INTO ORDER_DATA(ORDER_ID, ...) VALUES (ORDER_SEQ.NEXTVAL, ...) RETURNING ORDER_ID INTO ?

This allows the key to be established as part of the insert; similar functionality could be achieved with a PkSQL Command but would require two database calls rather than one.

PostgreSQL Create Command

The PostgreSQL key generator uses the sequence associated with a SERIAL column to generate the key value. The default sequence name is derived form the database table name and the column name for the primary key field; this can be overridden if a specific sequence is being used. An unknown-pk element must be used to define the column name of the primary key column; if JBoss is creating the table, the sql-type must also be specified as SERIAL:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        SERIAL
        
      
      

SQL Server Create Command

The key generator for Microsoft SQL Server uses additional SQL to obtain the value inserted into an IDENTITY column; the SQL is appended to the INSERT statement and executed in a single batch. The unknown-pk element must be used to specify the identity column to use:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        INTEGER
        
      
      

The batch executed will be be:

INSERT INTO ORDER_DATA (...) VALUES (...) ; SELECT SCOPE_IDENTITY()

The SCOPE_IDENTITY() function is used to avoid problems with triggers that may insert to other identity columns. However, this function is only available in SQL Server 2000 and later. For previous versions, the older @@IDENTITY function can be used by setting the pk-sql attribute of the entity-command.:

      
        SELECT @@IDENTITY
      

Sybase Create Command

The Sybase key generator uses an additional SQL query to obtain the value inserted into an IDENTITY column. The SQL query is executed after the INSERT statement. The unknown-pk element must be used to specify the identity column to use:

      
        java.lang.Integer
        ORDER_ID
        INTEGER
        INTEGER
        
      
      

The pk-sql attribute can be used to change the SQL statement executed by the command if necessary.