Oracle ADF: Viewlink based attributes vs Full Blown Queries

At one time or an other all of us must have dealt with an entity based View object whose SQL statement looked more or less like this :

SELECT UserInfo.USER_ID,
      UserInfo.USER_NAME,
      UserInfo.FULL_NAME,
      UserInfo.USER_ROLE,
      UserInfo.DEPARTMENT_ID,
      UserInfo.ACTIVE,
      Department.NAME,
      Department.ID
FROM USERS UserInfo, DEPARTMENTS Department
WHERE UserInfo.DEPARTMENT_ID = Department.ID (+)

This kind of VO may appear easy to create and manage when your schema contains two or three entities but things may end up pretty complicated as the number of associated entities increases. Let me show you the SQL statement of a view object displaying my version of the Service requests view in full detail.

  SELECT
      ServiceRequest.ID,
      ServiceRequest.REQUEST_DATE,
      TRUNC(REQUEST_DATE) AS SEARCH_DATE,
      ServiceRequest.REQUESTER_FIRST_NAME,
      upper( REQUESTER_FIRST_NAME) AS SEARCH_FIRST_NAME,
      ServiceRequest.REQUESTER_LAST_NAME,
      UPPER( REQUESTER_LAST_NAME) AS SEARCH_LAST_NAME,
      ServiceRequest.REQUESTER_TELEPHONE,
      ServiceRequest.LOCATION_STREET,
      ServiceRequest.LOCATION_NUMBER,
      ServiceRequest.LOCATION_AREA,
      ServiceRequest.LOCATION_ZIP,
      ServiceRequest.LOCATION_CITY,
      ServiceRequest.DEPARTMENT_FOR,
      Department.NAME AS DEPARTMENT_NAME,
      ServiceRequest.ASSIGNED_DATE,
      ServiceRequest.ASSIGNED_TO,
      AssignedTechnician.FULL_NAME AS TECHNICIAN_NAME,
      ServiceRequest.RESOLUTION_COMMENTS,
      ServiceRequest.REQUEST_STATUS,
      Status.NAME AS STATUS_NAME,
      ServiceRequest.PROBLEM_DESCRIPTION,
      AssignedTechnician.USER_ID,
      ServiceRequest.CREATED_BY,
      ServiceRequest.CREATED_ON,
      ServiceRequest.MODIFIED_BY,
      ServiceRequest.MODIFIED_ON,
      ServiceRequest.RESOLVED_ON,
      Department.ID AS ID1,
      Status.STATUS_ID,
      ServiceRequest.TELEPHONE_BY,
      TelephonedOperator.FULL_NAME AS TELEPHONE_OPERATOR_NAME,
      ServiceRequest.TELEPHONE_DATE,
      TelephonedOperator.USER_ID AS USER_ID1
 FROM
   SERVICE_REQUESTS ServiceRequest,
   DEPARTMENTS Department,
   STATUSES Status,
   USERS AssignedTechnician,
   USERS TelephonedOperator
 WHERE 
  (ServiceRequest.DEPARTMENT_FOR = Department.ID (+)) AND
  (ServiceRequest.REQUEST_STATUS = Status.STATUS_ID) AND
  (ServiceRequest.ASSIGNED_TO = AssignedTechnician.USER_ID(+)) AND
  (ServiceRequest.TELEPHONE_BY = TelephonedOperator.USER_ID(+))

When dealing with pure entity based view objects -- that is without a custom where clause -- then JDeveloper makes some descent effort in keeping your where clause straight. If you decide to add bind variables however, then JDeveloper completely looses it and you end up having to manage everything by hand. Where clauses have to be tampered with if you also decide that you need outer left joins and you cannot get away without using the primary keys of all the participating entities and then having to learn how to hide them.

I am never too happy when dealing with such monsters but users are always hungry for more and more information. I guess that this why every self respecting SAP table has at least 70 or more columns :-) ....

Anyway the thing that triggered my search for alternatives was the fact that on many occasions the reference fields of multi entity view objects would not update their values correctly especially after edits, until you issued a final commit, thus giving users one more reason to grumble. So instead of using the SQL query mode, I ended up using view link based transient attributes.

The idea is very simple. Create single entity based view objects containing all the relevant information. In our example Users and Departments. Then create a view link between the two and expose accessors to the client. Enable Java code generation for the primary view row. Last create a transient attribute to the source View (Users) that exposes the department name through the view link provided departments row member.

Let's follow the steps in more details....

For starters we can create two view objects UsersFullList and DepartmentsList with SQL statements like the following :

SELECT UserInfo.USER_ID,
      UserInfo.USER_NAME,
      UserInfo.FULL_NAME,
      UserInfo.USER_ROLE,
      UserInfo.DEPARTMENT_ID,
      UserInfo.ACTIVE
FROM USERS UserInfo

... for the Users View and ...

SELECT Department.ID,
      Department.NAME,
      Department.IS_TECH
FROM DEPARTMENTS Department

... for the departments. If you do not intend to edit Departments through your application, you may very well create a read only view object. Next we shall create a view link named UserAssignedToDept . Creating the view link is trivial, so I will show only you the images where we define the connection ....

and the view link properties :

The important thing to remember here is that since the view link cardinality is set to 1 → ∞ the department accessor will be of type Row instead of RowIterator. So if we next edit the UsersFullList view and create a Java class for the view row, then the code for the DepartmentInfo attribute will look exactly like this :

   /**Gets the associated Row using master-detail link DepartmentInfo
    */
   public Row getDepartmentInfo()
   {
       return (Row)getAttributeInternal(DEPARTMENTINFO);
   }

Creating a department name attribute for the the new view is now done by defining a transient attribute to the UsersFullList view

and then providing a getter method that looks like this :

   public String getDepartmentName()
   {
       String deptName;
       try {
           Row deptRow = this.getDepartmentInfo();
           deptName = (String )deptRow.getAttribute("Name");
       } catch (Exception e) {
           deptName = "";
       }
       return deptName;
   }

From the controller's side the view object looks exactly like it would if based on SQL and you don't have to hide the participating entity key attributes.

So is it worth it you might ask. The answer is -- as usually -- yes and no depending on the size of your project the complexity of your view objects and whether or not you prefer tampering with SQL code than Java. As I explained in the beginning of this posting the main advantage of this method is that your reference attributes will always display correctly since they are composed of pointers to the actual data of the view cache instead of cached entries. On the other hand if you project contains two view objects each composed from two entities, going through all that will be like killing a fly with a machine gun.