Tuesday, January 15, 2008

Complex Calculated Values in Oracle ADF af:table Component

Title of this post not accidentally contains word 'complex', developed sample application is really a bit complex, but I will try to describe it in easy way. Some time ago I was blogging about how to include calculated column into af:table component - Calculated Column in ADF Faces af:table Component. But, it was just separate read-only calculated column based on values available in other columns. However, what if you need to include calculation logic for database table related column and on the same time allow it to be editable.

Developed sample application - CalculatedComplex.zip is based on HR schema available in Oracle XE database and implements logic that allows to edit two calculated columns - MinSalary and MaxSalary.

Calculation rule applied for MinSalary:
  • IF (MaxEmployeeSalary - AvgEmployeeSalary <>
Calculation rule applied for MaxSalary:
  • IF (AvgEmployeeSalary + MaxEmployeeSalary > MaxSalary) THEN update(MaxSalary)
If described conditions will not be satisfied, this means value that was entered by user will be stored in database.

MaxEmployeeSalary and AvgEmployeeSalary are values calculated in EmployeesQueryView:

Calculations for both columns are done in Backing bean, methods are invoked through dummy binding to ReadOnly property:

All calculations related to MinSalary are done in getCalculatedMinSalary() method. Based on JobId parameter are calculated AvgEmployeeSalary and MaxEmployeeSalary values, and if specified condition is satisfied MinSalary is updated:

MaxSalary is calculated in exactly the same way as MinSalary.

Now I will show how it works. Let's say we want to focus on PU_CLERK salary:

If in Employees table we set Salary values for PU_CLERK to 1900, 1600, 1700, 1600, 1500 and press Save button - MinSalary in Jobs table for PU_CLERK is updated to 240:

MaxSalary is not updated, because AvgEmployeeSalary + MaxEmployeeSalary = 3560 and this is less comparing to current 8000. So, user can set MaxSalary manually to let's say 5000 and this value will be stored in database:

When running sample application, don't forget to add adf-faces-impl.jar and jsf-impl.jar to application's WEB-INF\lib directory.


Anu said...

I am working in ADF BC + JSF.
Its a question regarding Jdev10g. How do I dsipaly a field as a LOV (the one with a flash light icon and pops up as a new window containing 1 or more columns). I have search a lot but coul
dn't get how to implement this req.
Kindly Help..!

Andrejus Baranovskis said...

Hi Anu,

You can find my post - Complex List-Of-Values (LOV) in Oracle ADF useful. I'm describing there how to develop LOV's in table.


Anonymous said...

Hi Andrejus

About this example:

I am new to ADF11G and did not know ADF10G. I Try do this example in ADF11g (not migrating) using backin bean as indicated, but I have this error:
javax.el.PropertyNotFoundException: The class 'view.backing.Main' does not have the property 'group1'.
at javax.el.BeanELResolver.getBeanProperty(BeanELResolver.java:547)
at javax.el.BeanELResolver.getValue(BeanELResolver.java:249)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:143)
at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:73)
at com.sun.el.parser.AstValue.getValue(AstValue.java:118)
Truncated. see log file for complete stacktrace

I need your help please...

And .. thank you very much for publishing such good examples to help that we are embarking on ADF.

Andrejus Baranovskis said...


It means, on your page you have af:group component with group1 binding. However, in Backing bean you dont have group1 object for this binding.