ADF DB Sequence - Using DB Trigger approach

In this post I am going to show another more sophisticated way to generate a unique number (generated by a database sequence) and display it on the ADF UI if needed. The advantage of this approach over the previous technique (using the View Object create() method) is that the DB sequence is not wasted - The sequence number gets generated only on commit.

Let us take an use-case and see how this can be achieved through a step-by-step illustration;

1. For this use-case assume the following database table; where REQUEST_ID is the primary key column for which we would insert value dynamically from a DB sequence through ADF

2. Now, create a Fusion Web Application (ADF) in JDeveloper - Provide a name for the ADF app and accept the defaults in the create wizard

3. Now, right click on the model project and choose 'New'. Choose the 'ADF Business Components' under the 'Business Tier' and select 'Business Components from Tables' option

 4. Choose the 'Requests' table as the entity object on the first screen and select the RequestsView entity as the 'Updatable View Object'. Accept defaults in the following screens to complete creation of the model layer business components

5. This will automatically create the data control for the Requests view object.

6. Let us now create a DB sequence which will provide values to be inserted into the REQUEST_ID column and a DB trigger on the REQUEST_ID column under the REQUESTS table. The idea of db trigger is to ensure that the sequence id gets generated whenever there is an insert request on the table. This way the sequence numbers are not lost even if the transaction is rolled back before commit.

If you are not familiar with DB sequence or DB trigger creation, don't worry. JDeveloper can help you to create these declaratively in no-time.

7. Go to the Database Navigator view -> Right click on the Sequences and create a sequence as shown below

8. Similarly, right click on the REQUESTS table and create a DB trigger as shown below. Enter the trigger name and choose the sequence name (created in step 7) and the column name (REQUEST_ID)

 

9. Now, go to the ADF model project -> open the Requests entity object. Under the 'Attributes' section, edit the 'RequestId' attribute as shown below;

• Change the type to DBSequence (oracle.jbo.domain.DBSequence) - If this is not showing up by default, you can browse & select this class
• Updatable property - Never
• Refrest After - Insert

10. Time to create the UI. Right click on the ViewController project and create a JSF page.

11. Let us now drag and drop the RequestsView1 data control from the 'Data Controls' panel on to the JSF page that we created as an ADF table component

12. Now, drag and drop the 'CreateInsert' operation on to the page as an ADF button

13. Similarly drag and drop the 'Commit' and 'Rollback' operations as ADF buttons as shown below

PS: Note that 'CreateInsert' operation is available under the 'RequestsView1' data control where as 'Commit' and 'Rollback' operations which are common across all VOs are available at the root level (AppModuleDataControl)

14. Now, let us run this page and observe the results. Click on the 'CreateInsert' button to create a new row. Enter values for RequestType and Description columns. Notice that the RequestId column is read only as modelled. This column will get a value once the commit button is hit. Behind the scenes, when commit button is pressed, a record is inserted into the REQUESTS table which will invoke the trigger to get a unique number from the DB sequence. After insert the ADF UI is refreshed partially to reflect the primary key value.

Hope this helps.

There are some tips & solutions here. Take a look.