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.