Lately I saw a couple of posts on the OTN JDev & ADF forum where users tried to add redundant data into their data model and store it to the DB table. One common use case here is to have the result of a calculation as an attribute of a table.
In general you should be very careful when doing this. This is error prone and will you get into trouble almost every time. If you do add an attribute for such a calculation to a table in the DB, you have to think of the integrity of the data. Let’s look into the use case and the integrity problem.
We have a table in the DB which holds start and end for multiple data types like integer, data and timestamp:
We use the different start and end attributes to calculate the difference between start and end.
We do have the option to add attributes to the table and calculate the difference using a trigger in the DB each time the data is inserted or updated. Problem here is that the user will see the result only after the insert or update is done. For web pages this isn’t a good design.
Another option is to add the fields but do the calculation in the business component layer in ADFbc and store them in the DB together with all other changes done to the data. The your see the calculation, but other applications won’t see them until you store the record.
Problem with storing redundant data in a DB table
Both options have one flaw. When you store the result of a calculation in the DB, what happens if someone, person or program, changes one of the attributes used in the calculation?
Assume STARTINT is set to 5, ENDINT is set to 10. The result of the calculation is 5. This result we store in an attribute in the DB table. Now a bad programmer who does not know about the calculation, changes the ENDINT to 15 and commits the change.
When the other program looks at the data again the data is inconsistent. Which of the values is correct? The result? The STARTINT value? The ENDINT value? Or is the calculation simply wrong?
In this simple use case it’s fairly easy to find the problem. In more complex use cases where other workflows depend on the numbers it’s not as easy.
This leads to the solution shown in this post: don’t store results of calculations in the DB if possible. Do the calculation when they are needed.
There are cases where storing the result would be the better way to archive the whole use case, but this has to be decided on the use case and weighted against the complications. Most simple use cases don’t need to store the results and should not.
The remainder of this post we see how to implement such calculated fields using ADFbc.
Implementing calculated fields in ADFbc using Groovy
We start with creating a new Fusion Web Application and building the ‘ADF Business Components from a Table’. The sql script to create the table is
CREATE TABLE "HR"."CALCULATION"
( "ID" NUMBER(*,0) NOT NULL ENABLE,
"STARTTIMESTAMP" TIMESTAMP (6),
"ENDTIMESTAMP" TIMESTAMP (6),
CONSTRAINT "CALCULATION_PK" PRIMARY KEY ("ID")
REM INSERTING into CALCULATION
SET DEFINE OFF;
Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('1','1',null,to_timestamp('24-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('26-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),null,null);
Insert into CALCULATION (ID,STARTINT,ENDINT,STARTTIME,ENDTIME,STARTTIMESTAMP,ENDTIMESTAMP) values ('2','4','6',to_timestamp('31-DEZ-15','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('05-JAN-16','DD-MON-RR HH.MI.SSXFF AM'),null,null);
We use the HR DB schema to add the table, but it can be added to any schema you want. The CALCULATION table consists of some start and end values of different types to later show how to work with them. To work with the table we add two records resulting in the following data
I don’t show the steps to create the basic application from the wizards as the application is available via the link GitHub base application.
Once you downloaded and unzipped the workspace you should see the base application as it will be created by following the wizard.
The first step is to create a transient field in the Calculation EO to hold the result of the calculation of the difference of STARTINT and ENDINT. The difference here is, that we store the result in the EO as transient attribute which is not stored into the DB.
Add transient attribute and set the name to Durationint
Select expression and click the edit expression icon
edit the expression and select the attributes the calculation is dependent on
in the UI-Hints tab set the ‘autosubmit’ flag for Startint and Endint to ‘true’
Add the attribute to the VO from the EO
The real work is shown in the third image above ‘edit expression…’. Here we enter a Groovy expression to calculate the difference between STARTINT and ENDINT as
if (Endint == null)
The Groovy expression uses the attribute names from the EO not the ones from the DB table. First we check if the Endint is given, if not we return 0. If there is an Endint we return the (Endint-Startint).
We then add notifications to the calculated attribute whenever the attributes Startint or Endint change to recalculate the Durationint attribute (lower half of the dialog). Next we set the AutoSubmit property of the Startint and Endint attributes to true to make sure we get the new values when we calculate the result.
Finally we add the new calculated attribute to the VO. We can now test the application module using the application module tester:
Set Endint and get the calculated result
change Endint to get another result
We now add a index page to the View Controller project to add an UI to the application. We can just drag the CalculationView1 and drop is as an ADFForm with navigation and submit onto the page.
In the resulting form we set the Startint and Endint fields to autosubmit=’true’ to make sure the new values are submitted. As the Durationint field isn’t updateble we set it to read only.
Running the application will show you
The application in this state can be downloaded from GitHub (feature/calculated_int_field).
To show that this can be done with other data types we can use the other attributes of the table. As the way to do this is the same I spare to give detailed instructions. You can download the final application from GitHub (final).
All samples yre using the HR DB schema and table called CALCULATION. The needed SQL code to create the table and to insert data to the table is posted in here.