IT Innovation

Declarative Data Filtering

Define multiple query expressions and build search forms with ease.

By Steve Muench Oracle ACE

March/April 2008

In this column, we continue our exploration of the new features in the upcoming Oracle JDeveloper/Oracle Application Development Framework (Oracle ADF) 11g release by looking at a new feature called view criteria. The view criteria feature provides a declarative filtering mechanism that makes it easier to query data. The view criteria feature also eliminates the need to enter WHERE clauses in view objects by hand, makes it easier to configure and use multiple filters, and simplifies the creation of search forms.

We'll see all these capabilities in the walk-through below. To continue, download the starter workspace and ensure that you're using the Oracle JDeveloper Technology Preview 3 release, available as a free download.

Start by extracting the contents of the file and opening the FrameworksMarApr2008.jws workspace in Oracle JDeveloper. Note that the Model project in the starter workspace defines a familiar Emp entity object; three view objects, named Employees, EmployeesList, and Managers , respectively; and the HRModule application module. The Mgr attribute of the Employees view object defines a list of values (LOV) based on the EmployeesList view object, and the ViewController project defines the Employees.jspx and EditEmployees.jspx pages, which we will use in the last section. Before proceeding, adjust the properties of the connection named scott in the Application Resources zone of the Application Navigator until you can successfully test a connection to a SCOTT schema. If you need to create the tables, use the CreateDeptEmpTables.sql file provided with the starter workspace.

Using Declarative SQL Mode

In Oracle JDeveloper, you can specify easy-to-understand names for the columns defined in the SQL query encapsulated by a view object. For example, the Managers view object in the starter workspace queries the EMP table. Although the query includes the JOB column, the corresponding view object attribute is named CompanyRole . Previous versions of Oracle JDeveloper made it possible to manually enter custom WHERE and ORDER BY clauses in a view object query, and they included an "expert mode" that gave full control over the entire SQL statement. Although these modes are both still supported, they require you to remember the names of the underlying database columns—a tedious requirement if you're not intimately familiar with the schema. The new declarative SQL mode for view objects in Oracle ADF 11g lets you define query filter predicates by using view object attribute names without having to type in SQL yourself. At runtime, Oracle ADF translates the logical filter expressions into the appropriate SQL automatically.

Let's use this new feature to finish the definition of the Managers view object. This object is currently missing a necessary filter that restricts the resultset to include only employees who are also managers. To update the definition, double-click the Managers view object in the Application Navigator and click the Query tab in the View Object Overview Editor. In the Query section of the page, click the edit (pencil icon) button to modify the query definition. In the Edit Query dialog box, change the SQL Mode setting from Normal to Declarative , and click Yes to acknowledge the alert that appears. Note that the Query Clauses section changes to show declarative controls for the Where filter expression and the Order By attribute list. To define the filter expression for the query, click Edit.... The View Criteria Definition dialog box appears.

View criteria are used to define the WHERE clause of a declarative SQL mode view object. You use the View Criteria Definition dialog box to define a set of query filter predicates called view criteria items. These items represent a logical condition you enforce on the queried data, with each rule specified in terms of an attribute name, an operator, and operands. For this exercise, you will specify employees who are managers by adding and configuring a single view criteria item.

To begin, click Add Item and note that it appears in the View Criteria tree control. With the new criteria item selected, change its Attribute setting below to CompanyRole and the Operator to equal to . Leave Operand set to Literal , and enter the string MANAGER into the Value field. Next, set Case Insensitive Search to False —the default setting is case insensitive, but because the job values in the database are already set to uppercase, you can change the default setting. Next, set Usage to Required . We'll go over the purpose of optional view criteria items later in this column, but for now, the criteria item will always appear in the query at runtime. Now click OK to complete your changes to the declarative WHERE expression. Back in the Edit Query dialog box, configure the view object to sort rows by Ename. To do this, select the Ename attribute from the Available list and click the add button ( > ) to move it to the Selected list. Finally, click OK to save your changes to the Managers view object.

Now test the HRModule application module, by right-clicking it in the Application Navigator and selecting Run . Click Connect in the Business Components Configuration dialog box that appears. In the Business Components Browser, verify that the rows returned for the Managers view object instance include only those in which the CompanyRole equals MANAGER. (Under the covers, Oracle ADF issues the query at runtime with a predicate of JOB = 'MANAGER' on your behalf.)

Defining Named View Criteria

Before the advent of Oracle ADF 11g, to show an employee list filtered by company role on one page and by department number on another page, you would have needed to either create separate view objects for each page or write custom code to selectively modify a view object's WHERE clause and bind variable values. With the new release, you can now use a single view object with multiple named view criteria filters to accomplish the same task.

Let's try this now. Double-click the EmployeeList view object to open the Overview Editor. On the Query panel, look at the View Criteria section and note that one view criteria, called ByCompanyRole , is already defined. Double-click this definition to open the Edit View Criteria dialog box. Note that the definition looks very similar to the logical query filter we defined earlier. However, if you select the one view criteria item in the tree, you can see that it uses a bind variable named TheCompanyRole, instead of a literal value, for its operand. Click Cancel to return to the Overview Editor.

Next, define a second named view criteria that filters employee data by department. Click the "Create new view criteria" button, a green plus sign to the right of the View Criteria section heading. When the Create View Criteria dialog box appears, enter ByDepartment for Criteria Name and click Add Item to add a view criteria item. Set its Attribute to Deptno, Operator to equal to, Operand to Bind Variable , and Usage to Required . Then click New... to define a new bind variable. When the Bind Variable dialog box appears, enter TheDeptno for Name and set Type to Number . We want this bind variable to be set at runtime only if a named view criteria that references it is used, so leave the Required check box un checked. Then click OK to finish defining the bind variable.

Because this filter definition will drive the full list of values for the Mgr attribute, we also need to include the company president in our list, even though that person may not be in the selected employee's department. To define this additional rule, select the Criteria Group node in the View Criteria tree and click Add Item again. Set its Attribute to CompanyRole, Operator to equal to, Value to PRESIDENT, Case Insensitive Search to false , and Usage to Required . Next, change the conjunction between the two criteria items from AND to OR , by selecting the Deptno criteria item in the tree (currently displayed as AND Deptno = :TheDeptno) and setting its Conjunction to OR . Finally, click OK to finish defining the new criteria.

Using Named View Criteria

An LOV is based on a view object and uses a view accessor as a named, local "handle" to a rowset of available choices in the list. To use our new ByDepartment view criteria to filter the Mgr attribute's list of values, let's edit the existing view accessor that the LOV uses and apply the new view criteria to it. To do so, double-click the Employees view object in the Application Navigator to open the Overview Editor. Click the View Accessors tab, and double-click the EmployeeList entry. In the Edit View Accessor dialog box, select the ByDepartment view criteria from the Available list and click the add button ( > ) to shuttle it to the Selected list. In the Bind Parameter Values section below, double-click the Value cell in the row containing TheDeptNo to enter a value expression for this bind variable. The parameter should be set to the value of the Deptno attribute in the selected row, so enter the simple Groovy expression Deptno in this field. Then click OK.

The next step illustrates another use of named view criteria. We will retrieve a list of clerks by adding an instance of EmployeeList to the data model, filtered by company role. Double-click the HRModule application module, and click the Data Model tab. Select EmployeeList from the Available list, change the New View Instance field to the value Clerks, and click the add ( > ) button to shuttle the new view object instance into the Data Model tree. To configure the applied view criteria, make sure the new view object instance is selected and click the Edit... button. In the Edit View Instance dialog box, shuttle the ByCompanyRole view criteria to the Selected list. In the Bind Parameter Values section below, double-click the Value cell in the row containing TheCompanyRole and enter the Groovy expression ' CLERK ' (including the single quotes). Then click OK to finish configuring the view object instance.

After saving your changes, test the two filtered lists by right-clicking HRModule in the Application Navigator and selecting Run . In the Business Component Browser, double-click the Employees view object instance and navigate through the employee rows. Note that the list for the Mgr attribute shows the filtered list of employees who are in the same department as the current employee (plus the company president). Double-click the Clerks view object instance, and you'll see that only clerk rows are returned, as expected.

Creating Search Forms

One of the best uses of named view criteria is to simplify the creation of search forms. Once you define a named view criteria, you can drop it from the Data Controls section of the Application Navigator onto a page as an Oracle ADF Query Panel. The view criteria items you define automatically drive the input fields the user sees on the search form.

Let's try this out by dropping the ByNameOrSalaryRange view criteria, created in the Employees view object, onto the Employees.jspx page in the starter workspace. But first, take a closer look at the defined view criteria. Double-click the Employees view object, click the Query tab of the Overview Editor, and double-click its ByNameAndSalaryRange named view criteria. Note that this view criteria defines two view criteria items: one for the Ename attribute, using the "contains" operator and case-insensitive search, and the other for the Sal attribute, using the "between" operator. Both items use literal values and specify Optional for the Usage setting. In a search form, the optional view criteria items for which an end user does not specify any value do not participate in the query filter. By contrast, the Selectively Required usage setting forces the end user to enter at least one of the selectively required criteria items in the search form, to avoid performing a "blind query" over large tables. Now cancel the view criteria editor.

The final exercise involves building a search page by using this named view criteria. In the ViewController project, double-click the Employees.jspx page in the Web Content folder to open the visual Web page editor. Expand the Data Controls heading of the Application Navigator and expand the HRModuleDataControl node to show the Employees data collection. Expand the node, scroll down to find the Named Criteria folder, and expand it. Drag the ByNameAndSalaryRange view criteria, and drop it in the center of the page. In the Create menu that appears, select Query -> ADF Query Panel with Table.... In the Edit Table Columns dialog box, select and delete all of the attributes except Empno, Ename , and Sal . Click the Row Selection and Sorting check boxes, and click OK.

Now test the page, by right-clicking the Employees.jspx page in the Application Navigator and selecting Run . Try entering different search value combinations for Ename and the salary range, and see the search results that appear.

As usual, this column has only scratched the surface of this powerful new feature. For more information about this new feature, see the Oracle ADF 11 g Fusion Developer's Guide on the Oracle Technology Network (OTN).

Next Steps

 READ more Frameworks

more about Oracle JDeveloper and Oracle ADF
 Oracle ADF Developer's Guide

Oracle JDeveloper 11g Technology Preview
 the starter workspace for this column


Photography byRicardo Gomez Angel,Unsplash