22 Jan 2012

ViewObject. Working with multiple RowSets.

Let's say we have a ViewObject with quite "heavy" SQL query from performance point of view. We need to show ViewObject's rows in two different tables on the same page and rows in the tables should be filtered in different ways. For example, we have a query from Employees table and we need to show separately clerks in one table and managers in another one. And let's suppose that the query is very heavy, so it's preferable to be executed once only. It would be nice to retrieve records from database once and after that filter them in memory and show in appropriate tables as many times as we need.
In most cases we work with ViewObjects containing one "Default" RowSet only. ViewObject creates it internally and implementing RowSet interface delegates it's methods to the "Default" RowSet. But ViewObject can support more than one RowSet. Except internally created "Default" RowSet we can create "secondary" RowSets. This feature can be very useful for our usecase. We're going to use "Default" RowSet to retrieve all rows from database and create two "secondary" RowSets (clerks and managers) with filtered in memory rows. These "secondary" RowSets we will show in our tables on the page.

In the model of our sample application we have very simple (just for example) entity based ViewObject EmployeesView with two ViewCiterias - EmployeesViewCriteriaClerk and EmployeesViewCriteriaMAN:




In EmployeesViewImpl we have a method that creates new "secondary" RowSet as a result of filtering in memory rows from main "Default" rowset. It uses one of our ViewCriterias to filter the rows:

    private RowSet getRowSet(String rowSetName, String criteriaName) {
        //Find created secondary rowset by name
        ViewRowSetImpl rs = (ViewRowSetImpl)findRowSet(rowSetName);

        //if not found
        if (rs == null) {
            //Create new rowset as a result of filtering in memory rows
            //from DefaultRowSet using ViewCriteria criteriaName
            rs =
               (ViewRowSetImpl)findByViewCriteria(getViewCriteria(criteriaName), -1,
                                     ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);

            //RowSet is created with autogenerated name like "EmployeesView1_...
            //Let's remove the rowset from VO's rowsets table.           
            removeRowSet(rs);
            //Change rowset's name. 
            rs.setName(rowSetName);
            //And put it back. Next time we'll be able to find it by name. 
            addRowSet(rs);

        }

        return rs;

    }


And two methods-clients of the getRowSet method:

    private static String CLERK_RS = "ClerkRowSet";
    private static String MAN_RS = "ManRowSet";
    private static String CLERK_CRITERIA = "EmployeesViewCriteriaClerk";
    private static String MAN_CRITERIA = "EmployeesViewCriteriaMAN";
    
    //Get RowSet for clerks
    public RowSet getClerkRowSet () {
      return getRowSet(CLERK_RS, CLERK_CRITERIA);
    
    }
    
    //Get RowSet for managers 
    public RowSet getManRowSet () {
      return getRowSet(MAN_RS, MAN_CRITERIA);  
    } 


These two methods should be exposed via client interface to be accessible from the binding layer.

In our View we have the following TaskFlow:


The first activity is a method call, executing query of EmployeesView instance. So, on this step we're going to retrieve rows from the database. In order to retrieve all rows, be sure, that Fetch Mode attribute of the ViewObject is set to FETCH_ALL.


View activity MainView contains two tables for clerks and managers. Let's have a look at its pageDef:
  <executables>
    <variableIterator id="variables"/>

    <methodIterator Binds="getClerkRowSet.result"
                    DataControl="AppModuleDataControl" RangeSize="25"
                    id="getClerkRowsIterator"/>
    
    <methodIterator Binds="getManRowSet.result"
                    DataControl="AppModuleDataControl" RangeSize="25"
                    id="getManRowSetIterator"/>
  </executables>
  <bindings>
   
  
    <tree IterBinding="getClerkRowsIterator" id="EmployeesView11">
      <nodeDefinition DefName="com.cs.blog.testfilterVO.model.EmployeesView">
        <AttrNames>
          <Item Value="EmployeeId"/>
          <Item Value="FirstName"/>
          <Item Value="LastName"/>
          <Item Value="Email"/>
          <Item Value="PhoneNumber"/>
          <Item Value="HireDate"/>
          <Item Value="JobId"/>
          <Item Value="Salary"/>
          <Item Value="CommissionPct"/>
          <Item Value="ManagerId"/>
          <Item Value="DepartmentId"/>
          <Item Value="CreatedBy"/>
          <Item Value="CreatedDate"/>
          <Item Value="ModifiedBy"/>
          <Item Value="ModifiedDate"/>
          <Item Value="ActionComment"/>
        </AttrNames>
      </nodeDefinition>
    </tree>
    <tree IterBinding="getManRowSetIterator" id="EmployeesView12">
      <nodeDefinition DefName="com.cs.blog.testfilterVO.model.EmployeesView">
        <AttrNames>
          <Item Value="EmployeeId"/>
          <Item Value="FirstName"/>
          <Item Value="LastName"/>
          <Item Value="Email"/>
          <Item Value="PhoneNumber"/>
          <Item Value="HireDate"/>
          <Item Value="JobId"/>
          <Item Value="Salary"/>
          <Item Value="CommissionPct"/>
          <Item Value="ManagerId"/>
          <Item Value="DepartmentId"/>
          <Item Value="CreatedBy"/>
          <Item Value="CreatedDate"/>
          <Item Value="ModifiedBy"/>
          <Item Value="ModifiedDate"/>
          <Item Value="ActionComment"/>
        </AttrNames>
      </nodeDefinition>
    </tree>
    <methodAction id="getManRowSet" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="getManRowSet"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.EmployeesView1"
                  ReturnName="AppModuleDataControl.methodResults.getManRowSet_AppModuleDataControl_EmployeesView1_getManRowSet_result"/>
    <methodAction id="getClerkRowSet" RequiresUpdateModel="true"
                  Action="invokeMethod" MethodName="getClerkRowSet"
                  IsViewObjectMethod="true" DataControl="AppModuleDataControl"
                  InstanceName="AppModuleDataControl.EmployeesView1"
                  ReturnName="AppModuleDataControl.methodResults.getClerkRowSet_AppModuleDataControl_EmployeesView1_getClerkRowSet_result"/>

  </bindings>

Note, that Instead of Iterators we use methodIterators representing results of methodActions getManRowSet and getClerkRowSet and these methodIterators are set in IterBinding attributes of tree bindings EmployeesView11 and EmployeesView12 that are  used by our tables on the page.

The result of our work looks like this:


You can download sample application for this post. It was designed for JDev 11.1.1.2.0.
That's it!