Saturday, May 19, 2012

EJB DataControl - Configuring Sequential ids using JPA @TableGenerator

In this article I'm trying to explain how JPA feature used to generate and assign the unique sequence numbers to JPA entity using @TableGenerator.

You can download the sample workspace from here
[Runs with Oracle JDeveloper 11.1.2.0.0 (11g R2) + HR Schema]

Implementation steps

Create Fusion Web Application with entities based on Departments, then create a session bean and data control for the session bean.

First create the table required for @TableGenerator, download the required sql script.

Steps to configure @TableGenerator
  1. Right click on the Connection  and select New -> Offline Database Objects -> Offline Database Objects from Source Database, select Sequence table and click on Finish.
  2. Open persistence.xml in flat editor.
  3. Navigate to Departments Entity from persistence.xml and open departmentId field.
  4. Go to Primary Key Generation Tab select Table Generator section.
  5. Make the configuration as shown in below image.

Above Primary key section will have the configuration details of the table generator to be used Column field specifies the Sequence name column, the name of the Table Generator set in Name field should match the Sequence name present in the table and Value column field specifies the Sequence count column.

Now select Use Generated Value check box and set the Strategy as Table and Generator as DeptNoGenerator as shown below.


In View Controller project, create the index.jspx page and drop the departmentsFindAll->Table as ADF Table and drop the Create operation from departmentsFindAll->Operations as ADF Button.
From Data Control Palette drop persistDepartments as ADF Button and bind parameter as shown below.


Run the index.jspx page and click on create button and enter the filed details, don't enter for departmentId field and save the record. Now the departmentId will be added as 101 since we have started sequence number from 100.

2 comments:

  1. You should include the sql script in the ADF EMG sample, otherwise running the sample app gives:

    EJB Exception: : Local Exception Stack: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.1.3.v20110304-r9073): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist Error Code: 942 Call: UPDATE HR.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? bind => [1, DeptNoGenerator] Query: DataModifyQuery(sql="UPDATE HR.SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?") at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:797) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:863) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583) at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526) at org.eclipse.persistence.internal.sessions.AbstractSession.executeCall(AbstractSession.java:980) at

    ReplyDelete
    Replies
    1. Lynn,

      Sql Script is there in application folder - EJBTableGeneratorApp/etc/SequenceTable.sql

      Thanks,
      Deepak C S

      Delete