ADF: The best way to indicate for records in a table the existence of details – or: the importance of up-to-date SQL knowledge

3

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

Image

- run the Application Module to test whether the ViewObject is correctly defined:

Image

Check if for department 40 – no employees there – the indicator attribute does indeed show N as value:

Image

< P >

 

- create a new ADF Faces page DepartmentsPage.jspx

- drag DepartmentsView collection on Data Control palette to page and drop as table

Image

 

- edit the column definition for EmployeesIndicator – turning it into a selectBooleanCheckbox

<af:selectBooleanCheckbox value=”#{row.HasEmployeeDetailsIndicator==’Y’}” id= “ot1″/>

Image

- run the page to see the result

Image

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

3 Comments

  1. Florin Marcus on

    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
     

  2. Anton Scheffer on

    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

Leave a Reply