ADF Bussiness Components: In-Memory Sorting and Filtering

When we work with the ADF Business Components, we work with the View Objects that retrieves its rows from database. This is the default behavior we may need to change sometimes. It is much efficient to work with the data in the memory in such cases which promotes reuse. Once we have the view object with the rows populated from the database, we can work with the existing rows in memory, we can sort, filter, search the existing view object rows without re-querying the database. This will greatly help us to minimize the database round-trip. 

View objects has SQL MODE that determines whether to use existing rows in memory or retrieve data from the database each time it is referenced. The view object has 3 different SQL mode;

QUERY_MODE_SCAN_DATABASE_TABLES - Get the rows from database. This is the default mode.

QUERY_MODE_SCAN_VIEW_ROWS - Use the existing rows.

QUERY_MODE_SCAN_ENTITY_ROWS - Use entity objects rows (rows in the entity cache, valid only for entity-based view objects)

We can use setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS) method to set the View Object SQL mode to use the existing rows in memory. After we change the SQL mode, new setting will take effect the next time we call the executeQuery() method.In order to do in-memory sorting filtering we must first set the SQL Query Mode ViewObject.QUERY_MODE_SCAN_VIEW_ROW for a view object. This will make sure the existing view object rows will be using without re-querying the database.

We can use the setSortBy("attributeName desc") method to do in memory sorting. This will take effect next time we invoke executeQuery() method on the view object.

In order to do filtering on view objects row we can use applyViewCriteria() or findByViewCriteria() methods, again the changes will take effect after executeQuery(). Both methods can be executed to retrieve rows from database or to use the existing rows of View Object instance. Below is the flags that is used to set the CRITERIA MODE mode which is set by setCriteriaMode() method on the ViewCriteria object;

ViewCriteria.CRITERIA_MODE_QUERY  // Uses database.

ViewCriteria.CRITERIA_MODE_CACHE  // Uses rows in memory without querying the database.


Below is the code snippet that implements in-memory sorting and filtering;

        // Get View Object Instance and retrieve rows from the database
        ViewObject allEmployees = applicationModule.findViewObject("EmployeesView");
        allEmployees.executeQuery();
        //Call helper method to display all the employees without sorting and filtering
        displayEmployees(allEmployees);

        // Lets tell to use all the existing rows of the view object without requerying the database
        // and then do in-memory sorting and then display the sorted rows
        allEmployees.setQueryMode(ViewObject.QUERY_MODE_SCAN_VIEW_ROWS);
        allEmployees.setSortBy("Salary desc");
        allEmployees.executeQuery();
        displayEmployees(allEmployees);

        // Lets implement in memory filtering and display only whose first name starts with R
        // First lets create view criteria and set the criteria mode        
        ViewCriteria viewCriteria = allEmployees.createViewCriteria();
        ViewCriteriaRow viewCriteriaRow = viewCriteria.createViewCriteriaRow();
        viewCriteriaRow.setAttribute("FirstName","LIKE 'R%'");
        viewCriteria.add(viewCriteriaRow);
        viewCriteria.setCriteriaMode(ViewCriteria.CRITERIA_MODE_CACHE);
        allEmployees.applyViewCriteria(viewCriteria);        
        allEmployees.executeQuery();
        displayEmployees(allEmployees);

If want to further modify the existing behaviour for sorting we can override the following methods;

    public void sortRows(Row[] rows)

    public Comparator getRowComparator()


You can download the source code/project that runs on HR schema by clicking HERE.