A customer writes in with a question about allowing users to perform additional filtering on a detail rowset, based on any of its columns, "I'm trying to do additional filtering. We have customers and cases. On selects a customer (master) and all cases are listed (in sets of ten). This list needs additional filtering on any column the user chooses."
Whenever you don't know in advance which criteria (or combination of criteria) the user will provide, it's a good use case for putting our built-in query-by-example feature, called ViewCriteria, to work for you.
If you have already created an application module that contains instances of view-linked DeptView and EmpView view objects, then you can use our built-in BC4J Tester tool to experiment with our View Criteria feature. Right-mouse on your application module, and pick "Test..." then click (Connect) on the dialog that appears to launch the tester. After double-clicking on node in the tester tree that represents the view link, you'll see a master/detail display with two toolbars. If you click on the "Specify View Criteria" toolbar button above the detail table display, a dialog will appear that will let you fill in your query-by-example criteria. For example, type the string (without the surrounding quotes) "> 1500" into the Sal field, and "IS NULL" into the Comm field, then click (Find).
This view criteria will now only show detail rows for any master that have a salary greater than 1500 and a NULL commission.
To remove the filter, click again on the "Specify View Criteria" toolbar button and then press (Remove) in the view criteria dialog, followed by (Find) again.
Notice that the "View Criteria" dialog also as an (OR>>) button. This allows you to formulate query-by-example queries like "(Sal > 1500 AND Comm IS NULL) OR (ENAME LIKE '%E%'). To experiment with this, you would repeat the steps we did above, but instead continue by pressing the (OR>>) button, and fill in "%E%" into the Ename field in the second view criteria tab in the dialog.
The BC4J tester is just making use of basic features in the BC4J framework to accomplish this, with a simple UI on top of it to permit experimentation. If you have a look at my BC4J Coding Techniques demo, you can see examples of setting up and applying view criteria rows that then are converted by the framework into appropriate extra WHERE clauses.
For example, if you have an EmpView VO instance as a detail view, you can create a collection of view criteria rows (initially empty) on the detail by calling:
ViewCriteria vc = voEmp.createViewCriteria();
You can then use the view criteria as a factory to create view criteria rows. Usually you'll only need one, but we allow the view criteria collection to contain multiple query-by-example "view criteria rows" in order to specify more complex criteria. For example, to set up the complex "OR" query we did above by writing code, we could do the following:
ViewCriteriaRow vcrowOne = vc.createViewCriteriaRow();
vcrowOne.setAttribute("Sal","> 1500");
vcrowOne.setAttribute("Comm","IS NULL");
ViewCriteriaRow vcrowTwo = vc.createViewCriteriaRow();
vcrowTwo.setAttribute("Ename","%E%");
Then we need to add our view criteria row (or rows, in the case above) to the view criteria collection we created:
vc.add(vcrowOne);
vc.add(vcrowTwo);
And then finally apply the view criteria collection to the view object instance that we want to filter:
voEmp.applyViewCriteria(vc);
If we want to access the current view criteria collection in effect, there is the getViewCriteria() method on ViewObject for achieving that. This would allow you to manipulate the collection of view criteria rows being used. If instead you want to cancel the filtering effect of the view criteria, you can simply call applyViewCriteria(null) on your view object instance.
By default, BC4J converts the collection of one or more view criteria rows into SQL WHERE clauses to add to your query the next time it is executed. If you're interested in customizing the WHERE clause that is generated from the view criteria rows, this article explains how to do that.
10:26:57 AM 
|
