Using Database Sequence in Oracle ADF

In some databases, like MySQL, you have the auto-increment option to generate an unique number when you add a new row. This is useful to create an unique number to act as a primary key. In Oracle Database, you don’t have auto-increment, but you have Sequences. A Sequence is an object that is used to generate a number sequence.There are three approaches to call Sequences in ADF: Override the Entity Object Row Base Class, use Groovy expression or use a DBSequence + Trigger. In this post you will learn all of them. Download the sample application: ADFDBSequenceApp.zip.

DBSequence + Trigger

First of all, you need to create the Trigger that executes before an INSERT statement.
This Trigger calls the Sequence and assign the value into primary key column.

CREATE OR REPLACE TRIGGER ASSIGN_DEPARTMENT_ID
BEFORE INSERT ON DEPARTMENTS FOR EACH ROW
BEGIN
  IF :NEW.DEPARTMENT_ID IS NULL OR :NEW.DEPARTMENT_ID < 0 THEN
    SELECT DEPARTMENTS_SEQ.NEXTVAL INTO :NEW.DEPARTMENT_ID FROM DUAL;
  END IF;
END;

In the Applications window, double-click the Departments Entity Object.
In the Overview editor, click the Attributes navigation tab.
In the Attributes page, select the DepartmentId attibute and change its type to DBSequence.

Save everything and run the Oracle ADF Model Tester.
Double-click the DepartmentsView1 node and add a new department.

A negative number is assigned to DepartmentId as its temporary value.
This value acts as the primary key for the duration of the transaction in which it is created.
When you commit the transaction, the value of attribute changes.

Override the Entity Object Row Base Class

To create a Java Class to centralize the code to retrieve the primary key, go to Applications window, double click Department entity> java > create > generate entityImpl class

Over ride this method in this class

  protected void create(AttributeList attributeList) {
    super.create(attributeList);
    for (AttributeDef def : getEntityDef().getAttributeDefs()) {
      String sequenceName = (String) def.getProperty("SequenceName");
      if (sequenceName != null) {
        SequenceImpl s = new SequenceImpl(sequenceName, getDBTransaction());
        setAttribute(def.getIndex(), s.getSequenceNumber());
      }
    }
  }

Click the Attributes navigation tab, and select the DepartmentId attribute set its data type to Number.
In the Custom Properties tab, click the Add icon > Non-translatable Property.
Create the SequenceName property with DEPARTMETNS_SEQ as value.

Save everything and run the Oracle ADF Model Tester.
Double-click the departmetnsView1 node and add a new Department.
The Sequence was executed and the number was assigned to DepartmentId field.

Groovy expression

In the Applications window, double-click the Employees Entity Object.
Click the Attributes navigation tab and select the EmployeeId attribute.
In the Details tab, change Default Value to Expression and add the following code:

(new oracle.jbo.server.SequenceImpl("EMPLOYEES_SEQ", adf.object.getDBTransaction())).getSequenceNumber()

Instead of replicating code, you might create an EntityImpl helper method.

protected oracle.jbo.domain.Number nextVal(String sequenceName) {
  SequenceImpl s = new SequenceImpl(sequenceName,getDBTransaction());
  return s.getSequenceNumber();
}

Then, the groovy expression can just be:

adf.object.nextVal("EMPLOYEES_SEQ")

Save everything and run the Oracle ADF Model Tester.
Double-click the EmployeesView1 node and add a new employee.
The Sequence was executed and the number was assigned to EmployeeId field.