Limit Query Results based on custom property

In ADF, for performance reasons you need to limit the results of the query of a view object. Also you need to inform the user that the results are limited so that he will know that he need to put specific criteria to find specific records.

You can use up To Row Number property in view object tuning, yet this just limit the fetched records and not the query.

You can use Range Paging option. Yet data for each row must be posted before you change row, so that makes it hard to use on view objects used for transactions.
So if a view object is based on entity that has many data it make it hard to tune.

An option for better performance is to use performance tip on tunning FIRST_ROWS and at the same time to limit the results of the query adding in where clause ‘ROWNUM <= :queryLimit’. In order to do that generic and not to change where clause of queries for each view object you need to tune, I use a custom property on view object ‘QueryLimit’ In the ViewObjectImpl

I override method executeQueryForCollection and, if there is a 'QueryLimit' property on view object then it apply a limit to the query of view object "ROWNUM <=QueryLimit" before performing query. Also after query I set the property "ResultsLimited" to "true" if queried records are not less of the QueryLimit.

I also have a method that checks ResultsLimited and return a message that the results are limited. I expose that method to client and use it from backing bean to show message to the user when results are limited.

We have put the logic in our base ViewObjectImpl class so it can be used from every view object
This approach boosted the performance in our application, of transaction pages that was based on tables with large amount of data.

Test Case

http://adfbugs.googlecode.com/files/LimitQueryResults.zip

GoMenuItem with target="_Blank" in Menu workaround

In a go menu item inside first Level of menu when we select goMenuItem 1 then new window opens behind my window (internet explorer 7,) works correctly in mozilla

We found a workaround for this case as follows.

We add client Listener in go menu item to execute a java script method
that remove the focus from the current window.



function focusoff(event)
{
blur(event.source);
}

This way the new window opens above the current window.

Test Case :
http://adfbugs.googlecode.com/files/TestGoMenuItemWA.zip

New Cascading LOV with required View Criterion Not a Bug

On july 2009 in the patch of jdeveloper (11.1.1.1.0) a new feature was introduced in view criteria. When a criterion is required then an error message is shown to user. Of course not without side-effects (http://adfbugs.blogspot.com/2009/07/query-component-required-criterion-bug.html)
On the latest patch of jdeveloper (11.1.1.2.0) a new feature and bugs was introduced in view criteria (http://adfbugs.blogspot.com/2010/01/new-bind-variable-and-view-criteria-bug.html)
The above bugs also effected the functionality of LOVs especially of those that use view criteria, either for selective required (http://adfbugs.blogspot.com/2010/01/selectively-required-criteria-in-lov.html) or for cascading LOVs.

In the current test case I have Employees and I want the List of value of Managers to depend on the department of the employee. I expect when the user has not selected a department the List of values of managers to be empty



Yet when I run application, just when I set department to null I get the following error.

ADF: Adding the following JSF error message: Attribute DepartmentId is required.
oracle.jbo.AttrValException: JBO-27035: Attribute DepartmentId is required.
at oracle.jbo.rules.JboVCItemRequiredValidator.validateItem(JboVCItemRequiredValidator.java:91)
at oracle.jbo.rules.JboVCItemRequiredValidator.validate(JboVCItemRequiredValidator.java:161)
at oracle.jbo.common.ViewCriteriaImpl.validateRow(ViewCriteriaImpl.java:606)
at oracle.jbo.common.ViewCriteriaImpl.validate(ViewCriteriaImpl.java:567)
at oracle.jbo.server.ViewRowSetImpl.validateViewCriteria(ViewRowSetImpl.java:7745)
User will think that DepartmentId is required, yet only the hidden criterion of LOV is required.

But actually it is just a warning, since if I press ok and submit then the new values are submitted, even though the value of ManagerId is not valid.

If I open ManagerLOV I get again the error and the LOV opens with no data.



If I clear the value of Manager I get the error again.

This is realy confusing and makes view criteria not usable in cascading LOVs.

The workaround we do for these cases is not to use view criteria for cascading LOVs but to put conditions directly in the where clause of the query.
i.e. WHERE DEPARTMENT_ID = :inDepId

Test Case
http://adfbugs.googlecode.com/files/TestLOVwithRequiredCriterion.zip

Thanks,
Anonymous said... In this case, all you need is to set "Validation" to "Optional" and leave "Ignore Null Values" un-checked.

I tried it and works fine.

Rollback to same row with view criteria workaround

Rollback for Forms and ADF means that we re-query data from database and remove changes that the user did.

From the user side of view rollback is expected to return to him to the same state he was when he last saved data.

So when the user that work on a Form (Row) presses Rollback and looses the row that he was working on is really confusing for him.

Fortunately Steve Muench has issued a workaround for this that many people use now in ADF applications.
http://blogs.oracle.com/smuenchadf/examples/
68. Restore Current Row After Rollback

Yet the above workaround does not work when View criteria are applied in view object and latest changes committed does not comply with these criteria.

This happens because Rollback forces query to re-execute with the applied view criteria and the changed (current) row does not exist in the new results set.

So for example in the current application there is a query and an edit page.
If you Query departments for specific manager (i.e. 100) and go to edit page if you change manager (i.e. to 200) and commit, after that if you press rollback you will loose that row since there is no longer in the queried rows.




I have applied a workaround in the current test case for that issue. In the beforeRollback of the view object I add a View criteria row that matches the current row primary key. This causes the current row always to comply to view criteria when we press rollback since in the query it is added
WHERE …. or (PK_FIELD = CURRENT_ROW_KEY)

In method afterRollback I remove the specific criteria row so that if the user query again (not by rollback) the results without the current row to be fetched.

This way the current row is not lost when it does not comply with applied criteria and we press rollback.

This workaround we have implemented in our Base ViewObjectImpl class and works also with composite primary keys.
The only issue is that with applied view criteria with no values the ‘or’ operator does not apply and only the current row remains in the result set after the rollback.

Test Case:
http://adfbugs.googlecode.com/files/TestRollbackToSameRowVC.zip

Bind Variable and View Criteria in view with range paging bug (Fixed in 11.1.1.4)

I managed to reproduce bug described in

http://adfbugs.blogspot.com/2010/01/new-bind-variable-and-view-criteria-bug.html

in a simple query page for view object with Range paging tuning.


When you query with a department then Reset and then Query again after scrolling and selecting rows, it throws exception:
java.sql.SQLException: Attempt to set a parameter name that does not occur in the SQL: inDeptId



Test Case
http://adfbugs.googlecode.com/files/TestNewBindVariableRP.zip