Sunday, April 17, 2011

ADF BC : Various way of setting Sequence Number

 

It is advisable to use sequence number for each ADF BC Entity object and there need to be corresponding sequence number created for each database table in DB.

It is known as ‘Surrogate Key’ or ‘Primary Key’ of ADF BC.

There are various way of populating this key for ADF BC component and I am trying to give few of best practices.

1. Using Create() method of entity

There is create method in BC Entity Object that can be overidden to assign primary key. Go to java tab for ADF BC entity object and generate this java class and don’t forget to select Create Method check box.

image

After that go to DepartmentsImpl.java and search for create method and write the following statement of SequenceImpl

image

Disadvantage of this way is ,if transaction is rollback then sequence number is lost.

2.  Using DB Trigger

we can use DB trigger to assign the sequence to departmentid and the advantage of this usage is we will retain the sequence number even after transaction is rollback.

Go to department Entity object and set the department id to DbSequence.

Set Updatable to Never since we will not allow user to edit this value

Set refresh after to update because when we use trigger and create row ADF BC will automatically assign negative value to Department id and when we press commit then only it will run trigger and assign value to dept id.

That is why we will not loose sequence number when we do rollback.

image

Create Trigger using Declarative way

Create a trigger for Department id sequence , there is no need to write trigger ,it can be automatically created by jdeveloper.

image

Select the following

image

then select the sql tab and you will see that jdeveloper has automatically created trigger definition for you.

image

image

Now run appmodule and create department row and we will see that negative value is assigned to department id.

image

When we press commit it will generate department id and if we press rollback nothing happens and negative value is rollback instead of sequence number.

3. Using Groovy to generate sequence number.

Here we can write custom method in in custom entity impl class that will be implemented by ObjectNameEntityImpl.java.

don’t forget to select the expression.

call

adf.object.customMethodName(“SEQ_NAME”) – This method will call generic custom method name that will return the sequence number.

image

customMethodName definition will be same as point number one except it prototype will be

customMethodName(String SeqNumber){

//call step one code and return sequence number.

}

4. Fourth way is most easiest and no code required.

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

image

Enjoy Primary Key !!!

7 comments:

  1. Nice :)
    Forth way is extremely easy

    ReplyDelete
  2. Yes Sadia , but it will not retain lost sequence number.If you want to retain lost sequence number then use second way.

    ReplyDelete
  3. I used the second way, but I face the following problem :
    Assume you have a table it's name dept have a primary key column dept_id and I follow the steps in the second way now it's data type is DBSequence.
    after that I create a page assume it's an employee page that reference the departments as select one choice list , and after submitting the page ,I got the following error :
    Can't convert 5 of type Integer to DBSequence

    ReplyDelete
  4. thanks for your post, i created a sequence but missed the trigger part and hence got an error "Cannot Insert Null Into". Your post helped me recognize the error and fix it.

    ReplyDelete
  5. Hi, I used the second way. Its working fine. But why the negative value is shown in the foreground. Is there a way to show actual sequence number? Or can we set a positive value or some default value in the foreground. Please suggest.

    ReplyDelete
  6. Hi I have a requirement that, When click on create insert button then corresponding column will generate the sequence number,
    i.e. if new row consists of a id then Id column will be auto fill with the next number(seq number)
    how to do this?

    ReplyDelete