Oracle Sequence Create Command (one sequence per table)

SourceForge Submitter: gcompagnon .
Oracle Sequence Create Command (one sequence per
table)

As the existing OracleCreateCommand is not efficient as
all: we had to use a single sequence for all tables
(named SEQUENCE_TEST), we need to add another CMP
Create-command for supporting a more efficient
manner.

Each entity will have its own sequence in order to auto-
increment its primary key. The sequence will created by
the post-create-table using a generic pattern :
<tableName>_seq. (this pattern could be modified)

This feature has been fully implemented and tested
with Jboss 3.2.2.

Following the HOW-TO:

********************************************
*How to Configure the CMP Entities for using auto-
increment feature based on a Oracle Sequence with
XDoclet:
********************************************
(for those who don't use XDoclet for generating the
EJBs ... please refer to the jbosscmp-jdbc.xml)

*for each entities that need the auto-increment facility,
add the line
@jboss.persistence post-table-create="CREATE
SEQUENCE %%t_seq START WITH 1 INCREMENT BY 1"
into the class definition zone:
for example:
[...]
/**

• <ul><b>XDoclet configuration</b></ul>
  *
  *
• The DataSource registry used by that Entity
• @jboss.persistence datasource = "java:/OracleDS"
• Type of Mapping used for that Bean
• @jboss.persistence datasource-mapping = "Oracle9i"
  *
• If the DB table doesn't exist, then create it using the
  jboss mapping rules
• @jboss.persistence create-table = "true"
• If that bean is undeployed, then drop the DB table
• @jboss.persistence remove-table = "true"
  *
• @jboss.tuned-updates "true"
• @jboss.persistence read-only="false"
• @jboss.persistence post-table-create="CREATE
  SEQUENCE %%t_seq START WITH 1 INCREMENT BY 1
  "
  *
• Do not implement the PK constraint in the DB table
  with the autogenerated PK
• @jboss.persistence pk-constraint = "false"
• Use a util method to get the generated PK by Oracle,
  unless a NullPointerException is thrown (new feature of
  jboss 3.2)
• @jboss.entity-command name = "oracle-table-
  sequence"
  *
• XDoclet configuration for ejb, documentation and
  tutorial are on <A
  HREF="http://xdoclet.sourceforge.net/">http://xdoclet.
  sourceforge.net/</A>
  [...]

For declaring the class member to be the auto-
increment primary key:

[...]
/**

• @jboss.persistence auto-increment = "true" <-
  • • this line is optional for Oracle but required for
      Hypersonic or some others DBMS.
      *
      *
• @ejb.persistent-field
• @ejb.persistence column-name = "id"
• @ejb.interface-method view-type = "both"
• @ejb.pk-field
  *
• The ID (generated by the DataStore)
• @return the id
• @author COMPAGNON
  *
  */
  abstract public Integer getID();
  [...]

********************************************
*Enhance Jboss CMP with that CreateCommand

the Create Command should be declared inside
standardjbosscmp-jdbc.xml :

[..]
<entity-command name="oracle-table-sequence"

class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCOracl
eSequenceCreateCommand">
<attribute name="sequence_name">%%
t_seq</attribute>
</entity-command>
[..]

********************************************
Some feedbacks:

*there will be a Oracle warning when Jboss server
starts, because it tries to create sequence twice.
Nothing fatal for the good start of Jboss server, except
some ugly logs onto the console.
*Jboss post-create-table feature will create a sequence
for association table that is absolutely unnecessary.
=>these 2 dysfonctions will not appear if we decide not
to use the create-table feature.(as this should be done
on production stage).

------------------------------------------------------------
Guillaume Compagnon
gcompagnon@jyperion.net