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:

Key Generator Uses an external key generator
PkSQL Uses an SQL statement
JDBC 3.0 Generated Keys Uses the getGeneratedKeys() method from JDBC 3.0
HSQLDB Uses an IDENTITY column
Informix Uses a SERIAL or SERIAL8 column
MySQL Uses an AUTO-INCREMENT column
Oracle Uses a sequence with INSERT ... RETURNING
PostgreSQL Uses a SERIAL column and currval()
SQL Server Uses an IDENTITY column and SCOPE_IDENTITY()
Sybase Uses an IDENTITY column and @@IDENTITY

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>NUMBER(10)</sql-type>
</unknown-pk>

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:

<jbosscmp-jdbc>
<enterprise-beans>
<entity>
<ejb-name>Order</ejb-name>

<!-- define primary key class for Order -->
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>NUMBER(10)</sql-type>
</unknown-pk>

<!-- define command used to generate Order keys -->
<entity-command name="keygen">
<attribute name="sequence">ORDER_SEQUENCE</attribute>
</entity-command>
</entity>
<entity>
<ejb-name>LineItem</ejb-name>

<!-- define primary key class for LineItem -->
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ITEM_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>NUMBER(10)</sql-type>
</unknown-pk>

<!-- define command used to generate LineItem keys -->
<entity-command name="keygen">
<attribute name="sequence">ITEM_SEQUENCE</attribute>
</entity-command>
</entity>
</enterprise-beans>

<entity-commands>
<!-- define a command that uses an Oracle sequence to generate keys --> <entity-command name="keygen"
class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand">
</entity-command>
</entity-commands>
</jbosscmp-jdbc>

Key Generator Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCKeyGeneratorCreateCommand
Attribute Name Default Description
key-generator-factory none The JNDI name of a service that implements org.jboss.ejb.plugins.keygenerator.KeyGeneratorFactory

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:

<entity-command name="key-generator"
class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCKeyGeneratorCreateCommand">
<attribute name="key-generator-factory">UUIDKeyGeneratorFactory</attribute>
</entity-command>

PkSQL Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPkSQLCreateCommand
Attribute Name Default Description
pk-sql none An SQL query that returns the value to be used as the primary key

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:

<entity-command name="sysTime" 
                class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPkSqlCreateCommand">
<attribute name="pk-sql">CALL "java.lang.System.currentTimeMillis"()</attribute>
</entity-command>

JDBC3.0 Generated Keys Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBC30GeneratedKeysCreateCommand

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.

<entity-command name="get-generated-keys"
class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBC30GeneratedKeysCreateCommand"/>

HSQLDB Generator Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand
Attribute Name Default Description
pk-sql CALL IDENTITY() A SQL query that returns the identity generated by an insert

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER</sql-type> <auto-increment/>
</unknown-pk> <entity-command name="hsqldb-fetch-key"/>

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:

<entity-command name="hsqldb-fetch-key"
class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCHsqldbCreateCommand">
<!-- uncomment to change SQL statement used to obtain identity
<attribute name="pk-sql">CALL IDENTITY()</attribute>
-->
</entity-command>

Informix Generator Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCInformixCreateCommand
Attribute Name Default Description
class-name com.informix.jdbc.IfxStatement The name of the class to cast the PreparedStatement to in order to access the last inserted SERIAL value.
method getSerial The name of the method to invoke to get the last inserted value.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>SERIAL</sql-type> </unknown-pk> <entity-command name="informix-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:

      <unknown-pk>
<unknown-pk-class>java.lang.Long</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>BIGINT</jdbc-type>
<sql-type>SERIAL8</sql-type> </unknown-pk> <entity-command name="informix-serial">
<attribute name="method">getSerial8</attribute>
</entity-command>

MySQL Create Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand
Attribute Name Default Description
class-name com.mysql.jdbc.PreparedStatement The name of the class to cast the PreparedStatement to in order to access the last inserted value.
method getGeneratedKeys The name of the method to invoke to get the last inserted value.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER</sql-type>
<auto-increment/> </unknown-pk> <entity-command name="mysql-get-generated-keys"/>

Oracle Create Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracleCreateCommand
Attribute Name Default Description
sequence none The name of the sequence to use to generate the key value.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>NUMBER(10)</sql-type>
</unknown-pk> <entity-command name="oracle-sequence">
<attribute name="sequence">ORDER_SEQ</attribute>
</entity-command>

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

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPostgreSQLCreateCommand
Attribute Name Default Description
sequence ${table}_${pk-column}_seq The name of the sequence to use to generate the key value.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>SERIAL</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="postgresql-fetch-seq"/>

SQL Server Create Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSQLServerCreateCommand
Attribute Name Default Description
pk-sql SELECT SCOPE_IDENTITY() An SQL query that returns the value generated by an IDENTITY column.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="mssql-fetch-key"/>

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.:

      <entity-command name="mssql-fetch-key">
<attribute name="pk-sql">SELECT @@IDENTITY</attribute> </entity-command>

Sybase Create Command

Class Name org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSybaseCreateCommand
Attribute Name Default Description
pk-sql SELECT @@IDENTITY An SQL query that returns the value generated by an IDENTITY column.

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:

      <unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>ORDER_ID</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="sybase-fetch-key"/>

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