Using Database Sequence in ADF

Tweet about this on TwitterShare on Facebook1Share on LinkedIn7Share on Google+0

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.

using-database-sequence-in-adf1

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

using-database-sequence-in-adf2

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.

using-database-sequence-in-adf3

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, right-click the Model project and choose New > From Gallery.
In the New Gallery, choose General > Java > Class and click OK.
In the Create Java Class dialog, specify the following values and click OK.

  • Name: CustomEntityImpl
  • Package: br.com.waslleysouza.model.adfbc.base
  • Extends: oracle.jbo.server.EntityImpl

using-database-sequence-in-adf4

Replace the content of Java class with the following code:

package br.com.waslleysouza.model.adfbc.base;

import oracle.jbo.AttributeDef;
import oracle.jbo.AttributeList;
import oracle.jbo.server.EntityImpl;
import oracle.jbo.server.SequenceImpl;

public class CustomEntityImpl extends EntityImpl {
  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());
      }
    }
  }
}

using-database-sequence-in-adf5

To override the Entity Object Row Base Class, go to Applications window, and double-click the Locations Entity Object.
Click the Java navigation tab and then click the Edit Java Classes button.
In the Select Java Options dialog, click Classes Extend.
In the Override Base Classes dialog, replace the content of Row field:

br.com.waslleysouza.model.adfbc.base.CustomEntityImpl

using-database-sequence-in-adf6

Click the Attributes navigation tab, and select the LocationId attribute.
In the Custom Properties tab, click the Add icon > Non-translatable Property.
Create the SequenceName property with LOCATIONS_SEQ as value.

using-database-sequence-in-adf7

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

using-database-sequence-in-adf8

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()

using-database-sequence-in-adf9

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();
}

using-database-sequence-in-adf10

Then, the groovy expression can just be:

adf.object.nextVal("EMPLOYEES_SEQ")

using-database-sequence-in-adf11

using-database-sequence-in-adf12

JDeveloper 12.1.3 and ADF has introduced a tightening security model for ADF BC Groovy expressions. To avoid JBO-25152 exception (https://java.net/jira/browse/ADFEMG-241), we need to change the value of trustMode.
In the Source editor, select the trustMode="untrusted" and change to "<default> (trusted)".

using-database-sequence-in-adf13

using-database-sequence-in-adf14

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.

using-database-sequence-in-adf15

Tweet about this on TwitterShare on Facebook1Share on LinkedIn7Share on Google+0

Author: Waslley Souza

Consultor Oracle com foco em tecnologias Oracle Fusion Middleware e SOA. Certificado Oracle WebCenter Portal, Oracle ADF e Java.

Leave a Reply

Your email address will not be published. Required fields are marked *