Defining a Cascading List of ValuesBy Steve Muench
Setting up LOVs is easier than ever with Oracle JDeveloper 11g.
Most data-intensive applications require users to select values from lists of choices. In many cases, the list of valid choices for one field depends on the selection a user makes in another field. For example, in a global commerce application, a user might first pick a country, leading the application to display a list of products for sale in that particular country.
The ability to simplify the creation of lists of values (LOVs) to support this kind of scenario has been one of the most common enhancement requests for Oracle Application Development Framework (Oracle ADF) 11g. In this column, I show how easy this task has become. In doing so, I take a first look at the upcoming Oracle JDeveloper/Oracle ADF 11g release and use it to build LOVs, including a list with cascading data dependencies.
To follow along, download and install the Oracle JDeveloper 126.96.36.199 Technology Preview release, available at oracle.com/technetwork/products/jdev/11. As with all of my other Frameworks tutorial articles, you should also have an Oracle Database instance available with access to the default SCOTT schema.
Setting Up the Model
Let's start by creating a new application in Oracle JDeveloper. If you don't have any other applications open, click the New Application node that appears in the Application Navigator. If you do have other applications open, select New Application from the application selector list at the top of the Application Navigator. In either case, the Create Application dialog box will appear. Enter OraMagDemo in the New Application field, and enter oramag.lovdemo in the New Application Prefix field. For Application Template , select Web Application [ADF Faces, ADF Page Flow, ADF BC] , and then click OK to create the application.
Next, let's build our starter application (as in past Frameworks columns) by creating default business components for the DEPT and EMP tables in the SCOTT account. To do this, right-click the Model project in the Application Navigator and select New . In the New Gallery dialog box that appears, select the ADF Business Components category (in the Business Tier grouping) and double-click the Business Components from Tables wizard. When the Initialize Business Components Project dialog box appears, click New to create a new application-level database connection. In the Create Database Connection dialog box, enter scott for the connection name, enter the appropriate username and password for the SCOTT schema, and enter the host name and instance ID (SID) to connect to your database. Note that in Oracle JDeveloper 11g, the default SID is now XE. This default will work if you're using Oracle Database Express Edition, but you'll need to change this to the correct SID (such as ORCL) if you're using a different Oracle Database edition. Click OK to create the database connection and OK again to start the Business Components from Tables wizard. As you've done before, go through the wizard to define entity objects for the DEPT and EMP tables, DeptView and EmpView , updatable view objects for each respective entity object, and an application module named HRModule . As before, don't bother creating any read-only view objects or a business components diagram.
When defining a list of values in Oracle JDeveloper, you reference a view object that provides the source of valid choices for your list. In this example, you'll create two lists of values. First, you'll define a list of values for the Deptno attribute in the EmpView view object created above, using the DeptView view object as the source for the list of valid departments. Then you'll build a more complex list of values for the Mgr (manager) attribute. This list will reference a new view object that queries a filtered list of employees who can serve as the current employee's manager. For the sake of this exercise, let's assume that the list of available managers for a given employee will be the list of other employees in the same department, along with any employee without a manager (such as the president).
To define this new view object, right-click the Model project in the Application Navigator and select New . Select the ADF Business Components category, and double-click the View Object component to open the Create View Object wizard. In the Name panel, change the name to ManagerListForDepartment and click Next . In the Entity Objects panel, select the Emp entity object from the Available list, click Add (the right-arrow button), and click Next . In the Attributes panel, include the Empno and Ename attributes, and click Next twice to get to the SQL Statement panel. In the Where field, add the following WHERE clause: (DEPTNO = :CurrentDept OR MGR IS NULL) AND EMPNO ! = :CurrentEmp . This clause will filter the list of employees to produce the set you defined above. Click Next to get to the Bind Variables panel. In this panel, create two new variables called CurrentDept and CurrentEmp (which are both referenced in the WHERE clause) and set both variables to be of type Number. Now click Finish to create the view object.
Defining the Lists of Values
Now that the basic model layer objects are in place, the next step is to configure the two lists of values. In Oracle ADF 11g, you can configure a list of values as a declarative feature of any view object attribute. By defining the list of values at the model layer instead of within the user interface (UI), you can ensure that your UI will present a list of values correctly and consistently, regardless of the particular Oracle ADF-supported UI client technology you decide to use.
In the Application Navigator, begin by double-clicking EmpView to open the view object editor. Note that in Oracle JDeveloper 11g, the business component editors appear as tabs within the main editor area instead of in a modal dialog box. Select the Attributes category along the left edge of the editor page, and select the Deptno attribute in the table that appears in the Attributes section near the top of the editor frame. Find the List of Values: Deptno section header below, and click the green plus-sign icon on the right side of the header to create a list of values for the Deptno attribute. When the List of Values dialog box appears, click the green plus-sign icon in the List Data Source section to add a datasource that will supply the list of valid choices for this attribute's value. In the View Accessors dialog box that appears, select the DeptView view object in the Available View Objects list on the left and click the Add Instance (right-arrow) button to add a new view accessor named DeptView1 for the selected view object. Then click OK . Note that the newly added view accessor datasource DeptView1 appears in the List Data Source tree display in the List of Values dialog box. Expand it, and select its Deptno attribute. Note that the List Return Values section updates to show how attributes in the list datasource will be assigned to attributes in the base view object when the user makes a choice from the list. You won't need to modify this section for this example, but note that you could map additional return value attributes in your application by adding to this list.
Before leaving the List of Values dialog box, click the Edit List UI Hints... button (near the top of the window) to open the List UI Hints dialog box. These settings provide information that defines how the list should appear within the user interface. Note that the default list type indicates a Choice List display style. You will use this default list type for this simple example, but if you click the list, you'll see the other types of list controls you can use within your own applications. In the Display Attributes section, select the Dname attribute from the Available list and click the Add (right-arrow) button to add it to the list of selected attributes. This selection indicates that you want the user to see the department name, rather than the department number, in the list. Finally, click OK to close the List UI Hints dialog box and OK again to close the List of Values dialog box.
Next, let's configure the list of values for the Mgr attribute. As before, select this attribute in the Attributes section of the view object editor for the EmpView object. Then click the green plus-sign icon in the List of Values: Mgr section header. When the List of Values dialog box appears, click the green plus-sign icon in the List Data Source section to add a new datasource that will supply the list of valid choices for this attribute's value. In the View Accessors dialog box, select the ManagerListForDepartment view object from the Available View Objects list on the left and click the Add Instance (right-arrow) button to add a new view accessor named ManagerListForDepartment1 for the selected view object.
Because this view object has bind variables, you need to configure how those bind values will get their values. To do so, make sure the ManagerListForDepartment1 view accessor is selected and click the Edit button. In the Edit View Accessor dialog box, note that the bind variable names CurrentDept and CurrentEmp appear in the Bind Parameter Values section. Check the Row-level bind values exist check box and double-click the Value column for the CurrentEmp bind variable in the table. You want to assign the value of this bind variable to be the value of the Empno attribute in the current row, so enter the (case-sensitive) expression Empno in this cell. Similarly, double-click the value column for the CurrentDept bind variable, and enter the expression Deptno to assign this bind variable's value to the value of the Deptno attribute in the current row. Finally, click OK to close the Edit View Accessor dialog box and OK again to close the View Accessors dialog box. In the List of Values dialog box, expand ManagerListForDepartment1 in the List Data Source tree and select its Empno attribute. Then, click Edit UI Hints as before to configure Ename as the list's display attribute. Click OK to close the List UI Hints dialog box and OK again to close the List of Values dialog box.
Testing Your Lists of Values
You can use the enhanced Oracle ADF 11g Oracle Business Component Browser to immediately test the LOVs you've just configured. To run the application module in the Oracle Business Component Browser, right-click the HRModule component in the Application Navigator and select Run . After you click Connect in the dialog box that appears, the Oracle Business Component Browser will appear. Double-click the EmpView1 view object instance to see its data. As shown in Figure 1, both Mgr and Deptno appear as lists. If you select the Mgr list, you'll notice that the list includes the employees in the current department (excluding the current employee) and employees such as KING who have no manager. When you navigate to different rows in the result set, you can see the lists automatically change to reflect the current department for the current employee. If you change an employee's department by choosing a different department name from the list, you'll notice that when you select the Mgr list, the set of choices will automatically update to reflect the new department.
When you're done experimenting, select File -> Exit to exit the Oracle Business Component Browser. Because the Oracle Business Component Browser has been reimplemented in the Oracle JDeveloper 11g release with Oracle ADF Swing, this example also serves as a quick illustration of how Oracle ADF Swing applications can make automatic use of model-defined LOVs.
Using Lists of Values in JavaServer Faces
Now that you've configured and tested the LOVs on the EmpView view object, let's see how simple it is to use them in an Ajax-enabled Web page using JavaServer Faces (JSF). In the Application Navigator, expand the ViewController project and its Web Content folder to display the project's Page Flows folder. Inside this folder, double-click the adfc-config node to open the page flow diagram. Next, go to the Component palette and find the Activities section. In this section, drag a View component and drop it onto the page flow diagram. When the new view appears on the diagram, rename the component Employees and press Enter . To create the JSF page for this view, double-click the icon representing the new view. When the Create JSF JSP dialog box appears, click OK to create the Employees.jspx page, using the default settings. The new Web page appears in the visual page designer.
The next step is to drop a data collection onto the page. First, take a look at the Application Navigator and note that the frame is grouped into several sections, each containing a titled heading bar and an arrow to enable you to expand or collapse the section contents. Below the Projects section of the Application Navigator is the Data Controls section. Expand that section, and then expand HRModuleDataControl to reveal the EmpView1 and DeptView1 data collections it contains. Drag the EmpView1 data collection, and drop it onto the center of the visual JSF page editor. From the Create menu, select Forms -> ADF Form to create an editable form showing EmpView1 data. In the Edit Form Fields dialog box that appears, note that the Component To Use value for both the Mgr and Deptno attributes is set to ADF Select One Choice . Oracle JDeveloper selects these UI component values by default as a result of the List Type UI hint you configured earlier when defining the list of values for those two attributes. Check the Include Navigation Controls check box, and click OK to create the form. Note that as expected, both the Mgr and Deptno fields appear as lists.
On a traditional Web page, when a user makes a change (such as picking a department from a list), those changes don't take effect until that person clicks a Submit button to send form results to a server. However, the Oracle ADF Faces JSF components make it easy to build more-interactive Web pages. For example, you can enable the Deptno list to automatically submit changes to the server whenever the end user changes its value, simply by setting its AutoSubmit property to true. To do this, select View -> Property Inspector from the Oracle JDeveloper main menu to show the Property Inspector. Next, select the Deptno list in the visual editor. In the Property Inspector window, note that the properties are grouped into categories such as Common, Data, Appearance, Style , and Behavior . Click the Behavior category, and set the AutoSubmit property to true .
Because you configured the list of values for the Mgr attribute to depend on the current value of the Deptno attribute, you need to perform two additional declarative steps to force the Mgr list to immediately update to reflect a changed list of managers. The first step is to assign a unique name to identify the Deptno list on the page. To do this, select the Common category in the Property Inspector, type the name DeptList in the Id field, and press Enter . The second step is to configure the Mgr list to refresh whenever the value of the Deptno component changes. To perform this step, select the Mgr list in the visual editor. Then select the Behavior category in the Property Inspector, type the same DeptList name into the PartialTriggers field, and press Enter .
Now it's time to run the page. Right-click the Employees.jspx page in Application Navigator and select Run. Oracle JDeveloper will launch the embedded Oracle Containers for J2EE (OC4J) application server and open the page in your default browser. Assuming you're using Internet Explorer 7.0 or Mozilla Firefox 188.8.131.52 or higher, you'll see a page similar to the one shown in Figure 2. To test the application, try performing the same steps you tried earlier while using the Oracle Business Component Browser. You'll see that when you navigate between rows, the declaratively configured lists update to reflect the current selection and set of valid choices. And when you change the department of an existing employee, the Mgr choice list immediately updates to reflect the data-driven set of valid values.
In this column I've shown how, by using a few declarative settings, you can configure both simple and cascading lists of values and use them within Web pages without writing any code. This example highlights the first in a series of new declarative features due in the Oracle JDeveloper/Oracle ADF 11g release. In future columns, I'll explore many more. For more information on Oracle JDeveloper/Oracle ADF 11g new features, see oracle.com/technetwork/products/jdev/11.
Steve Muench is a consulting product manager for Oracle JDeveloper and an Oracle ACE. In his more than 17 years at Oracle, he has developed and supported Oracle tools and XML technologies and continues to evangelize them. Muench coauthored the Oracle ADF Developer's Guide for Forms/4GL Developers (Oracle, 2006), and wrote Building Oracle XML Applications (O'Reilly Media, 2000). He shares tips and tricks on Oracle Technology Network and in his Dive into ADF blog.