At some point in time while development, developers often face issues related to the performance of their components. Likewise, if we want to fetch large data from a database through a SQL query through view object but also we need to take care of UI part as well as performance issues. If Developers want to show large data of result set into two different tables on the same page along with that developer wants to filter data in both the tables on different criteria.
USE CASE: We have a query from the Employees table and we need to show separately clerks in one table and managers in another one.
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 the 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 View Objects containing one "Default" Row Set only. View Object creates it internally and implementing Row Set interface delegates it's methods to the "Default" Row Set. But View Object can support more than one Row Set. Except internally created "Default" Row Set we can create "secondary" Row Sets. This feature can be very useful for our use case. We're going to use "Default" Row Set to retrieve all rows from database and create two "secondary" Row Sets (clerks and managers) with filtered in memory rows. These "secondary" Row Sets 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 ViewObjectEmployeesView with two ViewCiterias - EmployeesViewCriteriaClerk andEmployeesViewCriteriaMAN:
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);
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 the 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 the 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.vaibhav.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.vaibhav.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 methodActionsgetManRowSet 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.
That will be all now, we can have the fruits for all our hard work :
You can have a look at the sample but to run this do modify your Employees table of default Oracle Database schema “HR” as per the attributes used in this post.
filterViewObject
— Email us to get the application file
