| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
| Oracle® Application Development Framework Developer's Guide For Forms/4GL Developers 10g (10.1.3.1.0) Part Number B25947-01 |
|
|
View PDF |
When you need to filter the query results that a view object produces based on search criteria provided at runtime by the end user, you can apply a ViewCriteria to the view object. The view criteria is a row set of one or more view criteria rows, whose attributes mirror those in the view object. The key difference between a view row of query results and a view criteria row is that the data type of each attribute in the view criteria row is String to allow query-by-example operators to be entered like "> 304", for example.
To use a view criteria, follow the steps illustrated in the TestClientViewCriteria class in Example 5-11 to call:
createViewCriteria() on the view object, to be filtered to create an empty view criteria row set
createViewCriteriaRow() on the view criteria, to create one or more empty view criteria rows
setAttribute() as appropriate on the view criteria rows, to set attribute values to filter on
add() on the view criteria, to add the view criteria rows to the view criteria row set
applyViewCriteria(), to apply the view criteria to the view object
executeQuery() on the view criteria, to execute the query with the applied filter criteria
The last step to execute the query is important since a newly applied view criteria is only applied to the view object's SQL query at its next execution.
Example 5-11 Creating and Applying a View Criteria
package devguide.examples.client;
import oracle.jbo.ApplicationModule;
import oracle.jbo.Row;
import oracle.jbo.ViewCriteria;
import oracle.jbo.ViewCriteriaRow;
import oracle.jbo.ViewObject;
import oracle.jbo.client.Configuration;
public class TestClientViewCriteria {
public static void main(String[] args) {
String amDef = "devguide.examples.UserService";
String config = "UserServiceLocal";
ApplicationModule am =
Configuration.createRootApplicationModule(amDef, config);
ViewObject vo = am.findViewObject("UserList");
// 1. Create a view criteria rowset for this view object
ViewCriteria vc = vo.createViewCriteria();
// 2. Use the view criteria to create one or more view criteria rows
ViewCriteriaRow vcr1 = vc.createViewCriteriaRow();
ViewCriteriaRow vcr2 = vc.createViewCriteriaRow();
// 3. Set attribute values to filter on in appropriate view criteria rows
vcr1.setAttribute("UserId","> 304");
vcr1.setAttribute("Email","d%");
vcr1.setAttribute("UserRole","technician");
vcr2.setAttribute("UserId","IN (324,326)");
vcr2.setAttribute("LastName","Baer");
// 4. Add the view criteria rows to the view critera rowset
vc.add(vcr1);
vc.add(vcr2);
// 5. Apply the view criteria to the view object
vo.applyViewCriteria(vc);
// 6. Execute the query
vo.executeQuery();
while (vo.hasNext()) {
Row curUser = vo.next();
System.out.println(curUser.getAttribute("UserId") + " " +
curUser.getAttribute("Email"));
}
Configuration.releaseRootApplicationModule(am, true);
}
}
Running the TestClientViewCriteria example in Example 5-11 produces the output:
305 daustin 307 dlorentz 324 hbaer
When you apply a view criteria containing one or more view criteria rows to a view object, the next time it is executed it augments its SQL query with an additional WHERE clause predicate corresponding the query-by-example criteria that you've populated in the view criteria rows. As shown in Figure 5-18, when you apply a view criteria containing multiple view criteria rows, the view object augments its design time WHERE clause by adding an additional runtime WHERE clause based on the non-null example criteria attributes in each view criteria row.
There are several things you may need to know about query-by-example criteria, including how to test view criteria in the Business Components Browser, altering compound search conditions using multiple view criteria rows, searching for a row whose attribute value is NULL, searching case insensitively, clearing view criteria in effect, and how applying view criteria causes a query to be re-parsed.
In Section 5.6.1, "Common Methods for Working with the View Object's Default RowSet", you saw that the setWhereClause() method allows you to add a dynamic WHERE clause to a view object. As you'll see in later examples in this chapter, when you use setWhereClause() you pass a string that contains literal database column names like this:
vo.setWhereClause("LAST_NAME LIKE UPPER(:NameToFind)");
In contrast, when you use the view criteria mechanism, you saw in Example 5-11 above that you reference the view object attribute name instead like this:
criteriaRow.setAttribute("LastName","B%");
As explained above, the view criteria rows are then translated by the view object into corresponding WHERE clause predicates that reference the corresponding column names.
As shown in Figure 5-19, for any view object instance that you browse, clicking the Specify View Criteria toolbar icon brings up the Business Component View Criteria dialog. The dialog allows you to create a view criteria comprising one or more view criteria rows. To apply criteria attributes from a single view criteria row, enter query-by-example criteria in the desired fields and click Find. To add additional view criteria rows, click OR and use the additional tabs that appear to switch between pages, each representing a distinct view criteria row. When you click Find the Business Components Browser uses the same APIs described above to create and apply the view criteria to filter the result.
When you add multiple view criteria rows, you can call the setConjunction() method on a view criteria row to alter the conjunction used between the predicate corresponding to that row and the one for the previous view criteria row. The legal constants to pass as an argument are:
ViewCriteriaRow.VCROW_CONJ_AND
ViewCriteriaRow.VCROW_CONJ_NOT
ViewCriteriaRow.VCROW_CONJ_OR (default)
The NOT value can be combined with AND or OR to create filter criteria like:
( PredicateForViewCriteriaRow1) AND ( NOT( PredicateForViewCriteriaRow2 ) )
or
( PredicateForViewCriteriaRow1) OR (NOT( PredicateForViewCriteriaRow2 ) )
The syntax to achieve these requires using Java's bitwise OR operator like this:
vcr2.setConjunction(ViewCriteriaRow.VCROW_CONJ_AND | ViewCriteriaRow.VCROW_CONJ_NOT);
To search for a row containing a NULL value in a column, populate a corresponding view criteria row attribute with the value "IS NULL".
To search case-insensitively, call setUpperColumns(true)on the view criteria row to which you want the case-insensitivity to apply. This affects the WHERE clause predicate generated for String-valued attributes in the view object to use UPPER(COLUMN_NAME) instead of COLUMN_NAME in the predicate. Note that the value of the supplied view criteria row attributes for these String-valued attributes must be uppercase or the predicate won't match.
To clear any view criteria in effect, you can call getViewCriteria() on a view object and then delete all the view criteria rows from it using the remove() method, passing the zero-based index of the criteria row you want to remove. If you don't plan to add back other view criteria rows, you can also clear all the view criteria in effect by simply calling applyViewCriteria(null) on the view object.
A corollary of the view criteria feature described above is that each time you apply a new view criteria (or remove an existing one), the text of the view object's SQL query is effectively changed. Changing the SQL query causes the database to re-parse the statement again the next time it is executed. If you plan to use the view criteria filtering feature to apply different criteria values for fundamentally the same criteria attributes each time, you will get better performance by using a view object whose WHERE clause contains named bind variables as described in Section 5.9, "Using Named Bind Variables". In contrast to the view criteria filtering feature, using named bind variables you can change the values of the search criteria without changing the text of the view object's SQL statement each time those values change.
