Building Cascading Lists for Query Screens with ADF Business Components & JSP

Written by Duncan Mills, Oracle Corporation
July 2004

Introduction

For web deployed applications it is common to provide query screens or search areas that allow the user to both query by example and select options from drop down lists to restrict the query results. A requirement that often comes up is to drive such lists from lookup tables in the database and additionally where there is a relationship between reference data elements, automatically narrow detail lists based on the selection in a master list. In this case I'll show an example with two drop down lists driven from the REGIONS and COUNTRIES tables in the standard Oracle HR schema. The idea here is that we can use these linked list items to select a particular country. When the region is selected in the first list, then the second list automatically displays just the countries in that region.

The approach here can be emulated to any depth, with any tables as long as they have view links set up between them, and the nice feature is that we don't have to write any of the list coordination code, the ADF Business Components framework does all of that work for us.

The Technique

There are several ways that this kind of interdependent list user interface can be driven, and different approaches have different advantages. It is, for instance, common in traditional web applications to use a JavaScript based solution where the whole dataset (master list and all detail lists) is downloaded into the browser and then JavaScript is used within the page to coordinate the lists. This technique has the advantage of not requiring further round trips during list operation, but of course you pay the penalty in downloading all of that data in the first place, irrespective of if you need it or not.

So the solution outlined here, actually goes back to the middle tier when each selection on the master list is made, however, it does have the benefit of being able to operate in a browser that has JavaScript disabled.

The technique itself depends on two things:

• The use of View Links to coordinate multiple view objects automatically
• The Navigation List Binding provided by the ADF model

Setting Up the Data Model

The first step to carry out is to define the View Objects (VO) and View Links(VO) used to define the data for driving the lists. I'd recommend that you define totally separate View Objects for use in the lists rather than trying to reuse and existing one. The reason for this, is that we will be changing the record currency as the lists are used, and this could have undesired side effects if reusing an existing VO.

Assuming the use of the Regions and Countries tables from the HR Schema and that you already have Entity Objects for both of those tables.

1. Create new VOs for Countries and Regions. Append the word List or Search to clarify their usage or place them in a sub-package.
2. Set these new VOs as read-only when you create them by un-setting the Updateable check box.
3. For each VO that is a master of another list, edit the query to ensure that the master record will only appear if it has children, otherwise you will end up generating empty lists (and errors!) if that particular master is selected. The simplest SQL to use is probably an exists clause. Here is the version for the Regions master table where we only want to list Regions which actually contain some Countries.
   
   
   
4. Create a suitable View Link between the master VO and the detail. For instance in the Regions / Countries example:
   
   
   
5. Expose the new list driving VOs through the Application Module. For any detail lists, only expose these through the View Link so that they can only be accessed in the context of the master View Object. This will ensure that usage's of the detail lists will always be coordinated with the master.
   
   
6. Test the master detail relationship in the ADF Business Components tester (available from the context menu on the Application Module).

Creating the Basic Search Screen

1. Create a DataPage in the page flow diagram and drill into it, creating the relevant JSP page in the process.
2. In the component palette switch to the Struts HTML palette and drop a Form onto the page (creating an <html:form> tag). The action for the Form should be the DataPage that you are working on, so submissions will post back to the same action for handling.
3. Switch to the Data Control Palette and drill down through the Application Module data control to find the View Objects that will be used to drive the lists e.g. RegionList. In the Drag and Drop As: drop list at the bottom of the palette, select Navigation List.
   
   
4. Drag and drop the VO as a navigation list into the Form on the screen .
5. Select the list object that has been created ( as an <html:select> tag) and in the property inspector, set the onChange property to read DataForm.submit();. If the user has JavaScript enabled in their browser, then this will ensure that as soon as an element is selected from this list, any detail lists will be updated to show the correct record set. If you anticipate that the application may be used in browsers with JavaScript disabled, then you should also create a <html:submit> button to allow the user to manually force the update of the detail lists.
6. In the structure pane, select the UI Model tab( ). Select the Iterator that has just been created by the drag and drop of the navigation list (RegionListIterator in this case), and in the property inspector change the range size from the default value of 10 to -1 to indicate that all of the rows should be shown.
7. Also in the UI Model tab of the structure, select the list binding( ) that was created called RegionList in this case and edit it. In this edit screen you can specify which columns should be displayed in the list at runtime. In the case of Regions, we'll just want to view the region name.
   
   
8. The master list is now complete. Repeat the process for any detail lists, remembering to access the detail rowsets in the Data Control Palette by expanding the master list source (RegionList in this case)
9. Test the screen, you should find that as you change the value in the Regions list, the Countries list automatically updates to restrict it to countries in that region

What Next?

So far we simply have two interrelated drop down lists, what you need to do next is to be able to extract some information from them - in this case you care probably interested in the COUNTRY_ID defined by the user's final selection. To make this value available in the binding context, the simplest approach will be to expand the CountriesList View Object in the Data Control Palette, select the CountryId attribute and drag and drop that as a "Value" into the page. This will create the relevant value bindings for you and a <c:out> tag to print the current CountryId on the screen. You can delete the <c:out> tag unless you want to see it for debugging purposes. Now, when a new country is selected in the country list box the expression ${data.searchScreenUIModel.CountryId} will provide the current country selection for use in passing to DataAction parameters. (Note the name of the UIModel will differ depending on what you called your initial DataPage). So if you have created a search DataAction you can simply pass the selected country Id to it.

Restrictions

1. Ensure that every value in the master list(s) has at least one corresponding detail record so that the detail list cannot come up empty. Use an EXISTS clause as shown in Setting Up the Data Model. This particularly applies to nested master detail. So if you have linked lists say three deep, then the top level master should only be shown if there are "level 3" children in that category. As such, depending on your data model you may need nested EXISTS clauses to ensure this. As an example if we extend the above scenario to include the Locations table as well as Regions and Countries, the Where clause on Regions has to become:
   

   where exists
     (select null from COUNTRIES C
      where C.REGION_ID = Regions.REGION_ID
      and  exists
        (select null from LOCATIONS L
         where L.COUNTRY_ID = C.COUNTRY_ID))
   

2. This technique will not work on ADF Business Components screens which are currently in Find Mode, this is because the navigation list VOs will also be in Find Mode and will not contain any data to display. If you need lists for a Find-Mode screen then these should be cached in the session before going into Find mode and displayed with the Struts <html:selection> tag.

drmills v1.1