Assigning primary key value using Oracle Sequence

Many applications define tables with primary key which is populated using values from database sequence. There are several posts on the net that describe these techniques with some links listed below as references. In ADF applications there are several approaches to implement assignment of primary key values.

1. Using Groovy Expression in Entity class.

2. Overriding the create method of the Entity class.

3. Using database trigger.

1. Assign Primary Key values using ADF Entity level using Groovy Expression

SequenceImpl class is used in groovy expression of the key attribute of the entity. getSequenceNumber() method of the SequenceImpl class can be invoked using the database sequence.

In the Entity or the updatable View Object editor the following expression can be set in the properties of the key attribute as shown below.

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

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

1. Assign Primary Key values by overriding create method of the entity class

To override the create method of the entity class, generate  the java Accessors  (source code)  as shown below.

Once the java class is created modify the create method with following code.

    @Override

    protected void create(AttributeList attributeList) {

        super.create(attributeList);

        SequenceImpl seq = new SequenceImpl("T2_SEQ", getDBTransaction());

        Integer seqNextval;

        seqNextval = seq.getSequenceNumber().intValue();

        setT2Id(seqNextval);

    }

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

3. Assign Primary Key values by using database trigger

Use the database navigator to access the table, right-click on the table and select Create (PK from Sequence…) option.

Once a trigger is created, change the attribute type of the primary key column in the entity class to DBSequence.

Save the application and to test run the application module. Let us add a new row and notice that next sequence value is retrieved.

Note that a negative value is assigned until the actual INSERT is executed on the table in the database, which fires the trigger to assign new sequence value. This negative value is used to manage the entity relationships in the framework until changes are saved to the database. Once the changes are saved the framework will refresh the trigger assigned key value.

Till Next post  Bye.

Reference:

ADF Insider Video by Grant Ronald

Oracle Documentation

Chris Muir Blog