As Published In
Oracle Magazine
November/December 2009

DEVELOPER: Frameworks


One Smart Combo

By Steve Muench Oracle Employee ACE

Let end users choose, search for, or manually enter valid values.

User interface components available in Oracle Application Development Framework (Oracle ADF) 11g accelerate how your end users can work with data. In “Easier Interactive Data Entry” (Oracle Magazine, January/February 2009), you built a view object with a declarative list of values (LOV) on one of its attributes. You then used the Input Text with List of Values UI component to enable users to enter a valid value manually for that attribute or choose one from a searchable list. This column shows you how to implement a more versatile UI component: the Combo Box with List of Values . It lets users pick frequently accessed values from a list (as well as search for values and enter values manually). You’ll learn declarative and programmatic techniques to control which and how many items appear in the list.

To begin, download the starter workspace at otn.oracle.com/oramag/oracle/09-nov/o69frame.zip and ensure that you’re using the studio edition of the Oracle JDeveloper 11.1.1.1 production release, available as a free download on Oracle Technology Network (OTN) at oracle.com/software/products/jdev. Start by extracting the contents of the o69frame.zip file and opening the FrameworksNovDec2009.jws workspace in Oracle JDeveloper. The Model project in the workspace defines a base set of Oracle ADF components for working with the data in the EMP and DEPT tables in the SCOTT schema.

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 necessary, use the provided CreateDeptEmpTables.sql script to create the schema’s tables.

Using a Combo Box with List of Values Component

The EmpView view object in the Model project already defines a LOV on its Dname attribute. You’ll edit this LOV definition to change its UI component type to a Combo Box with List of Values . Start by double-clicking the EmpView view object to open it in the View Object Editor. Select the Attributes page, and click the Dname attribute in the table. Expand the List of Values: Dname section heading below the table, and double-click the LOV_Dname entry in the List of Values table. When the Edit List of Values dialog box appears, select the UI Hints tab. Change the Default List Type property from Input Text with List of Values to Combo Box with List of Values , and then click OK .

Next, you’ll drop an edit form based on the EmpView1 view object instance (in the HRModule) onto the EditEmployees.jspx page to verify that the Dname attribute in the form uses the new UI component. In the ViewController project, double-click the EditEmployees.jspx page to open it in the visual editor. Expand the Data Controls section in the Application Navigator, and expand HRModuleDataControl . Drag the EmpView1 data collection, and drop it onto the editor’s Center facet. When the Create menu appears, choose Forms -> ADF Form . In the Edit Form Fields dialog box, leave the Include Navigation Controls and Include Submit Button boxes unchecked and click OK . Select the Dname UI component in the visual editor. Notice in the Structure window that the component is af:inputComboboxListOfValues . (You may need to scroll to see the selected component in the tree.) To enable the component’s autocomplete functionality, set its AutoSubmit property to true in the Property Inspector’s Behavior section. Then set the Deptno field’s AutoSubmit property to true as well.

Select the Bindings tab at the bottom of the editor window. In the Executables box, select the EmpView1Iterator , and in the Property Inspector’s Advanced section, change the setting for ChangeEventPolicy from (default) ppr to ppr . This setting ensures that the Deptno field will update when the user selects a new department from the Dname LOV and that the Dname field will update if the user types in a new department number. Click the Design tab at the bottom of the editor window to return to design view.

Right-click EditEmployees.jspx in the Application Navigator, and choose Run . When the page appears in your browser, click the triangle button in the form’s Dname field. A list appears, showing an initial set of departments, along with a Search link. The DEPT table, by default, has only four rows, so in this case, the list shows all departments. In real-world applications, the list of valid choices might include many entries (perhaps millions). In such a situation, a subset of rows appears in the list, and the user can click the Search link at the end of the list to find other legal values. Click Search now, and try searching for valid choices, using the Search and Select dialog box (shown in Figure 1). Note that whether you pick a choice from the list, use the Search and Select dialog box, enter a valid department name (in uppercase) into the Dname field, or enter a valid department number in the Deptno field, the corresponding field is automatically updated.

 

figure 1
Figure 1: Running EditEmployees.jspx and using the Search and Select dialog box


Controlling List Choices

You can limit the number of rows that appear in the list of department names by changing the LOV_Dname entry’s ListRangeSize property value. To see this property’s default value, select the EmpView view object in the Application Navigator and expand the List of Values folder in the Structure window. Select the LOV_Dname entry, and notice in the Property Inspector that the ListRangeSize is 10 . Unless you change this value (which you won’t do for this exercise), only the first 10 entries from the list view object’s result set will appear.

You can also configure the LOV to use an additional view criteria to identify a subset of all legal values that should appear in the list. For example, in an application that accepts amounts in all currencies, a Combo Box with List of Values could allow all 200 three-letter currency codes as legal values. However, its view criteria filter could restrict this list to display only the most frequently used currency abbreviations (USD, EUR, and JPY), for quick selection.

To apply a filter to your Dname LOV, return to the EmpView overview editor’s Attributes page, select the Dname attribute, and double-click its LOV_Dname list of values entry, as you did at the start of this column. On the UI Hints page of the Edit List of Values dialog box, in the Choice List Options section at the bottom, check the box to the left of the Filter Combo Box Using label. Select the already configured MostPopularDepartments view criteria from the list on the right. Click the Edit View Criteria pencil icon button to the right of the list. You can see that the view criteria is a simple filter on Deptno to return only departments with a number between 11 and 39. Click Cancel to leave the view criteria as it’s currently defined, and then click OK . Right-click EditEmployees.jspx in the Application Navigator, and choose Run . Notice in the displayed page’s Dname list that now only the restricted set of department choices— RESEARCH and SALES —appears. To enter other values, you can either type in a valid department name (in uppercase) or click the Search link to use the Search and Select dialog box.

Making the Filter Smarter

Next, you’ll make the list filter more intelligent, by overriding the framework’s default functionality programmatically. You’ll add a bit of Java code to make the autocompletion case-insensitive, and you’ll include a custom SQL fragment in the view criteria item filter clause. To begin, expand the DeptView view object node in the Application Navigator and double-click its custom Java file— DeptViewImpl.java —to open it in the code editor. Click the Override Methods button in the code editor toolbar. When the Override Methods dialog box appears, check the boxes next to the

 

getCriteriaItemClause() and 
findByViewCriteriaForViewRowSet() 


methods. Click OK to override these two methods.

In the code editor, find the overridden

 

findByViewCriteriaForViewRowSet() 


method. The application calls this method whenever the framework tries to find a row in a DeptView rowset based on a view criteria. The LOV mechanism does this at runtime to check for matches based on the Dname value the user types in or selects from the list. To customize this method behavior to allow case-insensitive matches, first position your cursor at the start of the line that contains

 

super.findByViewCriteriaForViewRowSet(...) 


and press Enter to open a blank line above it. On this new line, add the following code:

 

if (criteria.getName() == null) {
    criteria.setUpperColumns(true);
}


If the overridden method’s parameter of type ViewCriteria has a name other than criteria , modify the added code by replacing both occurrences of criteria with that name. The lookups performed against the DeptView view object that use a system-created view criteria (having a null name)—including lookups that the LOV performs at runtime—will now be case-insensitive.

Next you’ll run a script to create a POPULAR_DEPARTMENTS table and populate it with a few department IDs. Then you’ll write a bit of code to return a custom SQL fragment for the Deptno item of the MostPopularDepartments view criteria to reference this table. (This exercise simulates a more data-driven way to filter the list.) To create the new table, first double-click the CreatePopularDeptTable.sql file in Application Navigator to open it in the editor. Notice that the script creates the table and inserts new rows for departments 10 and 40. Click the Run Script icon in the editor toolbar (the second one from the left). When the Select Connection alert appears, select the connection named scott, and click OK .

Back in the DeptViewImpl.java source-code file, find the overridden getCriteriaItemClause() method. This method is called for each item in each view criteria when it’s used for both in-memory and database-based searching. Overriding it enables you to return a custom WHERE clause fragment for any item. To implement an example, open a blank line above the line that contains

 

super.getCriteriaItemClause(...)


In this new space, add the following code:

 

 
ViewCriteria vc = vci.getViewCriteria();
if ("MostPopularDepartments".equals(vc.getName()) && 
    "Deptno".equals(vci.getName()) &&
    vc.isCriteriaForQuery()) {
  return "DEPTNO IN (SELECT DEPTNO FROM POPULAR_DEPARTMENTS)";
}


If the overridden method’s parameter of type ViewCriteriaItem has a name other than vci , modify the added code by replacing both occurrences of vci with that name. This code returns a custom SQL fragment involving the POPULAR_DEPARTMENTS table when the framework calls the getCriteriaItemClause() method for the MostPopularDepartments view criteria’s Dname view criteria item for use in a query against the database. This causes the LOV filter, which you’ve configured to use the MostPopularDepartments view criteria, to show only the departments whose DEPTNO value appears in the POPULAR_DEPARTMENTS table. If the view criteria item whose WHERE clause fragment you are overriding referenced a bind variable, the custom clause you return from the getCriteriaItemClause() method could also reference that bind variable value in its custom SQL fragment. This way your smart LOV filter could not only be data-driven but also be parameterized by a value in the current row. 

Next Steps


 READ more Frameworks

READ more about
Oracle JDeveloper and Oracle Application Development Framework
 oracle.com/technetwork/products/jdev
oracle.com/technetwork/products/jdev/tips/muench/designpatterns
Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g

Oracle Fusion Middleware
download.oracle.com/docs/cd/E12839_01/index.htm
oracle.com/technetwork/documentation

DOWNLOAD
Oracle JDeveloper 11g
the starter workspace for this column

Now rerun the EditEmployees.jspx page to test the reconfigured filter. In the Dname field, type part of the name of a department in lowercase—for example, oper —and press the Tab key to exit the Dname field. You can see the value autocomplete in a case-insensitive way to OPERATIONS . You also can verify that the LOV list now has the entries for departments 10 and 40, as configured by the contents of the POPULAR_DEPARTMENTS table.

Using a Combo Box with List of Values in Search

As this column’s last exercise, you’ll fine-tune how the Combo Box with List of Values works in a search form on the SearchEmployees.jspx page. The form is based on the EmpView view object’s EmployeeSearch view criteria, which includes items for Ename and Dname . Because the Dname attribute is the same one on which you already configured the LOV, the list shows up automatically in the search form. However, because the Dname LOV is designed to populate the Deptno item in the current row with the selected department’s Deptno value, you need to create a Deptno view criteria item to receive this value. This will ensure that the search query includes a clause involving the DEPTNO column, which likely has an index on it, instead of searching only on the (probably unindexed) DNAME column. To prevent end users from seeing the Deptno value in the search form, you’ll mark it as hidden.

To add this view criteria item, start by double-clicking the EmpView view object in the Application Navigator to open it in the editor. Select the Query page, and expand the View Criteria section at the bottom. Double-click the EmployeeSearch view criteria in the table. When the Edit View Criteria dialog box appears, click Add Item . In the Criteria Item section at the bottom, choose Deptno for Attribute . Next, select the UI Hints tab. In the Criteria Item UI Hints section, click the Deptno view criteria item in the tree. Set the Rendered Mode list to Never , so that the Deptno item will not appear in the search form, and then click OK . Next, select the editor’s Attributes page and select the Dname attribute in the table. Using the Property Inspector, set the AutoSubmit property in the UI Hints section to true , thereby ensuring that the autocomplete functionality of the Combo Box with List of Values will work in the model-driven search form. Right-click SearchEmployees.jspx in the Application Navigator, and choose Run . When the page appears in your browser, select a department name from the list and then click Search to see the results. The underlying query uses both the DEPTNO and DNAME columns in the predicate, even though end users can’t see the Deptno value in the search form.

By using the versatile Combo Box with List of Values component in your next Oracle ADF application, you can significantly enhance your end users’ data entry experience. For more information, see Section 5.11, “Working with List of Values (LOV) in View Object Attributes,” in Oracle Fusion Middleware Fusion Developer’s Guide for Oracle Application Development Framework 11g.

 


Steve Muench is a consulting product manager for Oracle JDeveloper. Since 1990 he has developed and supported Oracle tools and XML technologies and continues to evangelize them. Muench coauthored Oracle ADF Developer’s Guide for Forms/4GL Developers (Oracle, 2006), wrote Building Oracle XML Applications (O’Reilly Media, 2000), and shares tips and tricks on OTN and in his Dive into ADF blog.

 



Send us your comments