Sequence number for Primary key in oracle ADF, JDeveloper

If you have created an Entity object with a primary key field. The question is, how you will generate maximum serial no for primary key filed and where the logic will be placed? There are two methods to do this, using row level database trigger or using application level logic to obtain sequence no for primary key.

Suppose you have created an Entity object for Employees table in HR schema. You have also created the view object and added this view object to default application module. Employees table has a primary key on Employee_Id field.

First part explains database trigger method and second part explains it with application level logic in JDeveloper.

1- Create a database trigger on Employee table as under.

CREATE OR REPLACE TRIGGER employees_generate_pk
BEFORE INSERT
ON employees
FOR EACH ROW
BEGIN
SELECT employees_seq.nextval
INTO :new.employee_id
FROM dual;
END;

This code assumes that you have already created a sequence named “employees_seq” to generate next value for primary key.

2- Edit Employee Entity object and change data type of employee_id to “DBSequence” as shown in figure below.

DBSequence is a special domain. When you insert a new record, ADF creates a negative number for field. When you perform commit, database trigger generates value for that column which is then re-queried by ADF and shown to users.

Application Level Logic to generate Sequence no for Primary key in JDeveloper

1- First Method

Do following steps to achieve this

    1. Data type of employee_id field should by Number, uncheck Mandatory check box on edit attribute page of Entity object.
    2. Create a new view object name “Seq_Employee” bases on “Read-only access through query” using following query

select nvl(max(employee_id)+1,1) seq_no from hr.employees

  1. Add a view accessor on Employee Entity object using its “View Accessor” page. And add “Seq_Employee” view object as shown in figure below.

4-Now generate Entity object class for employee entity object as shown in figure below figure

5-Generate view row class for “Seq_Employee” view object. While creating view object class, do not forget to click on “Include accessors” check box. By default, JDeveloper will name this class as “Seq_EmployeeRowImpl” .

6- Open EmployeesImpl entiry object class wich you created in setp 4, and edit it doDML() method. After editing method should look like under.

protected void doDML(int operation, TransactionEvent e) {
if (operation==DML_INSERT){
RowSet seqForThisEmp = (RowSet) getSeq_Employee();
Seq_EmployeeRowImpl nextSeqNoRow =(Seq_EmployeeRowImpl) seqForThisEmp.next();
Number next_seq = nextSeqNoRow.getSeqNo();
this.setEmployeeId(next_seq);
}
super.doDML(operation, e);
}

This method fires just before ADF performs insertion. This method progamatically accesses view accessor to get next sequence no for employee_id and sets the this value for current row.

2- Second Method

Your can also directly use your database sequence in doDML as under

if(operation==DML_INSERT)
this.setEmployeeId(new DBSequence((new SequenceImpl("employees_seq",getDBTransaction()).getSequenceNumber()).longValue()));

Above code assumes that data type of EmployeeID is DBSequence. If it is Number, remove cast operation as under

if(operation==DML_INSERT)
this.setEmployeeId
((new SequenceImpl(“employees_seq“,getDBTransaction()).getSequenceNumber()));

12 thoughts on “Sequence number for Primary key in oracle ADF, JDeveloper”

  1. great effort,

    but if I want same thing on Character datatype instead of Number, I want to generate Document number like “11/1” , “11/2” and so on where 11 represent year and 1, 2 represent serial

  2. What if I have an adf table and insert multiple rows and click Commit button? It reads the same sequence for all rows and prints the following exception:

    oracle.jbo.TooManyObjectsException: JBO-25013

  3. Then you must use the second method given in “Application Level Logic to generate….” portion. As sequence will always return a new value for each row, so it will resolve the problem.

  4. Hi,

    Thanks for the great information. I am looking to do the same using MySQL database. MySQL does not support sequences, how do we achieve this in MySQL?

    Appreciate your help on this.

  5. Mark,

    MySQL supports auto increment. Create your primary key filed with AUTO_INCREMENT and in JDeveloper set the type of the field to DBSequence.

  6. I have follow your seconf method. but falied to create the method of getSeqNo().
    how to create it.

  7. Already i have develop a application. Actually i want to generate the PK value from interface. For that i have followed your second method. But i did not the get method of getSeqNo().

  8. Sayadur,

    Make sure that you followed following step,

    5-Generate view row class for “Seq_Employee” view object. While creating view object class, do not forget to click on “Include accessors” check box. By default, JDeveloper will name this class as “Seq_EmployeeRowImpl” .

Leave a Reply

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