An interesting discussion arose in one of the projects I am involved in. The functional requirement at stake: show records in a table. In one of the columns, show an indicator (checkbox for example) that signals whether or not that particular record has a specific type of details associated with it. You could say that it was the equivalent of a table of Departments with a checkmark for each Department that has employees (or employees of a certain type).
There are several ways of doing this – and several tiers in the application at which it can be done. One fairly straightforward way in ADF BC would be to create a ViewObject for the detail records, link them through a ViewLink to the master-VO and create a Transient Attribute in the master VO that uses a Groovy expression to count the number details.
However, when discussing the best approach, we came across other options and then took the discussion to a more generic level. One of my favorite guidelines, useful in situations like this, is to not bring data to where it is not really needed. In this, on the middle tier we do not really need the detail records. All we need is an indication of the existence of the detail records, which is not the same at all. When you consider the approach in an extreme situation – say records can have 1 milion details – it quickly becomes clear that there is a distinction between deriving the indication of detail-existence on the database or in the middle tier.
An easy approach that makes it unneccessary for the ADF BC framework to query the database for detail-records and make a server round trip for every row presented in the web page in the table is the following:
Assume the example of DEPT and EMP. Assume an Fusion Web Application with a Model project that contains a Department EntityObject and a DepartmentsView VO. A web page is to be created with a table that lists all Departments. This table should contain a column that has a check-mark to indicate whether or not the Department has any Employees working in it.
The steps now are:
– Add an Attribute EmployeesIndicator of type String; check the checkbox Mapped to Column/SQL
– Enter the following SQL expression:
case (select count(*) from emp e where e.deptno = Department.deptno) when 0 then ‘N’ else ‘Y’ end
– run the Application Module to test whether the ViewObject is correctly defined:
Check if for department 40 – no employees there – the indicator attribute does indeed show N as value:
< P >
– create a new ADF Faces page DepartmentsPage.jspx
– drag DepartmentsView collection on Data Control palette to page and drop as table
– edit the column definition for EmployeesIndicator – turning it into a selectBooleanCheckbox
<af:selectBooleanCheckbox value=”#{row.HasEmployeeDetailsIndicator==’Y’}” id= “ot1″/>
– run the page to see the result
There we are: an indicator to indicate whether or not details exist (employees work in the department), all determined in a single server round trip, rather than five subsequent database access requests.
As always, good article.
This solution is indeed the fastest. However, some purists may complain that it breaks encapsulation, since any access to EMP table ideally should be ‘hidden’ behind EmpEO and EmpVO . Â Breaking this rule too often might decrease the maintenance level of the application.
Thank you,
Florin
Â
Anton, as always, I stand corrected – or at least tweaked. You are right of course. Thanks for this feedback.
Lucas
If you are worried about millions of details the following will be faster:
, case when exists ( select null from emp e where e.deptno = Department.deptno ) then ‘Y’ else ‘N’ end