Ways to Filter rows from View Object in oracle ADF. When and Which method to use?

During development, we always encounter a situation where we have to programmatically filter rows from a ViewObject. In this blog, I will talk about the different ways through which this can be done. However, there no one fit for all.

I will also try to explain the condition or scenario in which one should be used and also their Pros and Cons.

1. View Criteria 

View Criteria is used to filter the rows by running the Criteria in the database. It runs a WHERE CLAUSE over the query for your View Object. Let us take an example.

Suppose we have a View Object EmployeeVO with Query SELECT * FROM EMPLOYEES

Suppose we want to see the Employees of some particular department, to do so we create a View Criteria on this view object to fetch the employees of a particular department, let us say department no. 102. 

We create a view criteria EmployeeVOViewCriteria1 and apply on the view object with condition 

[DepartmentId = :DeptIdBind], where :DeptIdBind is bind variable for Department Id.

When we will filter the rows then, the framework runs query

 SELECT * FROM (SELECT * FROM EMPLOYEES) WHERE DEPARTMENT_ID = 100

and return the result.

When view criteria should be used.

1. In the case where the query in the view object is simple runs fast and has less no. of records.
2. If the query in the view object is complex with many computations and takes time to execute, then view criteria should not be used to fetch records.
3. View criteria should is not used for a repeated task in a transaction. As whenever you will fetch records from view criteria, a query will run it will add a database hit. Again this is just a suggestion

Pros:

1. Queries run directly in the database and fetch the records.

2. Only the records which are needed will be loaded and displayed.

Cons:

1. It runs the clause over the query of the view object, so this makes the overall query a little slower than the query with direct bind variables.

2. It has to go to the database to fetch records each time it's called.

2. Direct Bind variables in the Query.

We can also use direct bind variables in the view object to filter records.

For the above case if we have to use direct bind variables then we will create the view object query like 

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = NVL(:DeptIdBind);

Here if we have to filter the rows for a particular department, then we will execute the query with the bind variable :DeptIdBind.

On executing the query passing bind variable as null will give all the records.

When direct bind variables should be used.

1. If the query in the view object is complex with many computations and takes time to execute, then direct filtering through bind variables should be used.

2. Loads only the required rows into the memory.
3. Direct bind variables should is not used for a repeated task in a transaction. As whenever you will fetch records, a query will run it will add a database hit.

Pros:

1. Queries run directly in the database and fetch the records.

2. Only the records which are needed will be loaded and displayed.

3. The query runs faster than the query in case of view criteria.

Cons:

1. It has to go to the database to fetch records each time it's called.

2. So every time if the filtering needs to be done, the query will be fired and a hit to the database will be made.

3. Using RowQualifier or ViewObject.getFilteredRows().

RowQualifier is used for in-memory filtering of the data in the ADF Application. 

In the case of filtering the rows with RowQualifier or getFilteredRows, the query of the view object is executed once and fetches all the data in the view object in the application memory and does the filtering of the records.

If we take the above example and filter the rows through row qualifier.

At first all the rows from the query of the view object i.e. 

SELECT * FROM EMPLOYEES

will be loaded in the memory and then the filtering will be done in the memory by comparing the attributes of the query.

When direct bind variables should be used.

1. In the case where repeated filtering is needed many filtering is needed.

2. It loads all the rows in the memory, so it should not be used in case if there are hugs no. of records in the view object.

Pros:

1. The rows are loaded once in the memory and filtering is done.

2. Query execution is needed for the first time only, on the first filter, it loads all the rows into the memory and does the filtration on the basis of that.

3. Good for filtering rows to fetch certain data repeatedly in a transaction.

Cons:

1. It loads all the data into the memory on the first hit, thus increasing memory use of the Application.

2. If the view object contains a huge number of rows, then it takes quite a time to load into memory.

What is the RowQualifier in ADF? 

What is the ViewCriteria in ADF?