JOracle

In ADF Faces Rich Client (RC) there is a nifty little component that will show a list of values for an input field. This takes the form of a popup with a table of values with a search form. It is very useful for filling in foreign key fields that often don’t have a value that means anything to users.

Before 11g came out, I had already started on something in 10g with the exact same functionality and since we're not going to move to 11g any time soon (all of our customers still use Internet Explorer 6), I thought that maybe there were some others out there who were in the same position and needed the same functionality.



NOTE
I have included sample applications for JDeveloper 10g (10.1.3.4.0, ADF Faces) and 11g (11.1.1.0.2, Trinidad) using the HR database schema. The HR (human resources) schema is a sample schema from Oracle and is usually found in Oracle databases. If not, it is fairly easy to install (Google it). There is, however, one bug in the schema. It contains a trigger that is fired whenever an employee's job or department is changed. It will insert a new record into the job_history table, with the previous job and department and the duration the employee was there (start_date and end_date). The problem is that the primary key on the table is employee_id and start_date and the trigger (actually a procedure that the trigger calls) inserts the same start_date every time an employee is modified, therefore violating the primary key constraint. If you can work out how to fix this, feel free to try. I however, have simply dropped the trigger as I don’t need the functionality any way.

Okay, so you've seen what the ADF Faces RC version looks like above, here is my version (10g):


There are some minor differences – some which are better in the 11g version and some, which in my opinion, are better in my version

These are the differences and why I think it makes my version better or worse:-
My version: Popup doesn't have a search form.
11g version: Popup has a search form.
Comment: Can easily be added to my version (I have done it).

My version: Popup is quite large.
11g version: Popup is compact.
Comment: 11g is better.

My version: A moderate amount of setup is required.
11g version: A minimal amount of setup is required.
Comment: 11g is better.

My version: Form field is uneditable.
11g version: Form field is editable.
Comment: It depends what you want. I quite like the fact that you can't type in any old rubbish into mine.

My version: Form field can show any value you want.
11g version: Form field can only show field value.
Comment: I'm not actually sure this is true as I've not really played around with LOV's in 11g, but I think that being able to show any value you want is an advantage.

My version: Popup can contain any ADF component.
11g version: Popup is restricted to a search form and a table of values.
Comment: Mine is better.

My version: Previous value can be pre-selected when popup appears.
11g version: Don't know.
Comment: This one is a matter of opinion, but at least mine has the option of either doing it or not.

I think that the main advantage of my solution is that it effectively hides the original field value, which as I said before, could be meaningless to the user.

To get a generic solution that will work in pretty much any situation, I have constructed a class which does most of the work for you, and just requires a few inputs. Other than that, there are a small number of methods and values that you need to set up and that's it!

The class is called SelectionDialogModel (even though I'm British and we spell it 'dialogue'), and for each field that requires a selection popup, you need to set up a managed bean of this class with these attributes:
displayAttrMap - This is a key-value map of view object attribute names and an expression language (EL) string to get/set the value. The EL must be WITHOUT #{} as the class calculates the value on-the-fly and with #{} it would calculate the value too early. This can be used to populate backing bean attributes that will be shown instead of the field value. (Not required, but this means that the original field value can’t be replaced with meaningful values).
viewObjectName - This is the absolute view object name to retrieve from the application module. (Required).
keyAttrName - This is the name of the key attribute in the view object. This is used as the value to populate the background (hidden) field. (Required).
keyAttrValueEL - This is the EL to get or set the background field. (Required).
preSelect - This is a boolean value that determines whether the current value is already selected when the popup appears. (Not required. Default value is false).

Okay, now let's run through an example (included in the sample workspace).
I have a page that shows a table of departments with a selection column and an edit button. The edit button navigates to another page with the selected department's data. In the edit page there are two fields: Department Name and Manager. I want the manager field to show the full name of the manager and not the id, and I also want to have a list of values popup, showing all the possible managers I can choose.

I'm assuming that the model has already been created and I will be focusing on the view layer.

This is the process to follow:
1. Create a backing bean for the edit page.
2. Create a managed bean for the selection dialogue.
3. Create a popup page.
4. Replace form fields with uneditable fields and choose buttons.

Create a backing bean for the edit page.
I've called my class DepartmentEdit.java and the managed bean backing_DepartmentEdit. This class will hold the value(s) that will be shown instead of the hidden value. For the department page there needs to be two values stored (first name and last name of the manager). It also holds a method to clear the values when entering the edit page from the list page.

Create a managed bean for the edit page.
I've called it selection_DepartmentManager and pointed it to the SelectionDialogModel class. Then set the attribute values - I have given it these values:
displayAttrMap = [FirstName=backing_DepartmentEdit.managerFirstName, LastName=backing_DepartmentEdit.managerLastName]
viewObjectName = ManagersView1
keyAttrName = EmployeeId
keyAttrValueEL = bindings.ManagerId.inputValue
preSelect = true

The displayAttrMap attribute is pointing the FirstName and LastName view attributes to their equivalent in the backing bean.
The viewObjectName attribute is set to the view of managers in the model. This is simply another instance of the employees view – kept separate so that each can be iterated through independently.
The keyAttrName attribute is set to the EmployeeId value in the managers view.
The keyAttrValueEL attribute is set to the page definition value binding of the ManagerId attribute in the departments view.
The preSelect attribute is set to true so that the current manager will be selected when the popup appears.

Create a popup page.
This has a table of the managers available with a selection column and two buttons: choose and cancel. Choose will call the chooseActionListener in the SelectionDialogModel class. So, how does it know which managed bean to use, since an edit page could have more than one selection dialogue and a selection dialogue might be called from more than one edit page? Well the SelectionDialogModel instance that is called by the edit page puts a copy of itself in the process scope (this is specifically used within popup pages), meaning that all popup pages call the two methods (chooseActionListener and cancelActionListener) in the same way: processScope.selectionDialog.chooseActionListener and processScope.selectionDialog.cancelActionListener. The choose method will return the currently selected row to the return listener, which is also in this class.
There will also need to be a navigation rule from the edit page to the popup page with a dialog: prefix. I have named mine dialog:SelectManager.

Replace form fields with uneditable fields and choose buttons.
The editable field (inputText component) containing the real value needs to be removed from the form on the edit page and replaced with a label (outputText component) to output a string that the user will understand. There also needs to be a choose button (or image) that will launch the popup.

In this case, the outputText component points to two backing bean attributes (managerFirstName and managerLastName in the backing_DepartmentEdit backing bean). The choose button (commandButton component) should navigate using the dialog:SelectManager navigation rule set up previously. Other attributes that should be set are these:
LaunchListener = #{selection_DepartmentManager.launchListener} - This is called when the popup is launched and sets up the values needed in the popup.
ReturnListener = #{selection_DepartmentManager.returnListener} - This is called when the popup is closed.
UseWindow = true - Tells the application to launch a popup.
WindowHeight/WindowWidth - Change these to whatever size you want the popup to be.

The only other thing to do is make sure that the backing bean attributes are repopulated with the current values each time the user enters the edit page. Do this by putting an action listener on the button that takes the user to the edit page.

And that's it. I would recommend that you have a look at the sample workspace provided as it has this example plus a more complex example using employees. It has three selection popups (job, department and manager).


Sample Workspaces
ADF Faces 10g version: SelectionDialog-10_1_3_4_0.zip

Trinidad 11g version: SelectionDialog-11_1_1_0_2.zip

In the ADF Developer's Guide and in the SRDemo sample application, there is a backing bean class to help populate a SelectManyShuttle component from two tables with a foreign key association.

This is already fairly flexible as you can specify the underlying value, the label and even a description attribute for each item that is retrieved. There is a level of flexibility that is missing though, and that is the ability to specify more than one attribute to serve as the label (or description).

Say you wanted to be able to assign several employees to a project using the shuttle adapter as backing for a SelectOneShuttle component. You would obviously choose the EmployeeId as the value attribute, but what attribute would you choose for the label? Either FirstName or LastName is likely to be repeated, but a combination of both (with maybe another attribute in the description to differentiate employees with the exact same name) would make it much easier to select the correct employee.

It is actually fairly simple to modify the existing shuttle backing described in the Developer's Guide. The end result of this being that you can specify the allItemsDisplayAttrName attribute in the following form:

LastName, FirstName

where any word that resolves to an attribute name in the row will be replaced by the value of that attribute, and any other charaters will stay as they are. So this example might give you a list of employees looking like this:

Abel, Ellen
Ande, Sundar
Atkinson, Mozhe
Austin, David
Baer, Hermann
Baida, Shelli
Banda, Amit
Bates, Elizabeth
Bell, Sarah
etc.

The work for this is done in the ShuttlePageBackingBeanBase class and in the getAllItems method. Before we do anything, it looks like this:

public List getAllItems() {
  if (allItems == null) {
    allItems = ADFUtils.selectItemsForIterator(allItemsIteratorName,
                                               allItemsValueAttrName,
                                               allItemsDisplayAttrName,
                                               allItemsDescriptionAttrName);
  }
  return allItems;
}

I've replaced this code completely with my own, but you could easily move it into the ADFUtils so as to make this generic for anywhere that uses the selectItemsForIterator method.

Here's the modified method:

public List getAllItems() {
  if (allItems == null) {
    allItems = new ArrayList();
    
    ArrayList attrNameList = new ArrayList();
    DCIteratorBinding iter = ADFUtils.findIterator(allItemsIteratorName);
    AttributeDef[] attrDefs = iter.getAttributeDefs();
    for (int i = 0; i < attrDefs.length; i++)
    {
      AttributeDef attrDef = attrDefs[i];
      String attrName = attrDef.getName();
      if (allItemsDisplayAttrName.matches(".*\\b" + attrName + "\\b.*") ||
          allItemsDescriptionAttrName.matches(".*\\b" + attrName + "\\b.*"))
      {
        attrNameList.add(attrName);
      }
    }
    
    for (Row r: iter.getAllRowsInRange())
    {
      for (int i = 0; i < attrNameList.size(); i++)
      {
        String attrName = (String)attrNameList.get(i);
        allItemsDisplayAttrName = allItemsDisplayAttrName.replace(attrName,
          r.getAttribute(attrName).toString());
        allItemsDescriptionAttrName = allItemsDescriptionAttrName.replace(attrName,
          r.getAttribute(attrName).toString());
      }
      
      allItems.add(new SelectItem(r.getAttribute(allItemsValueAttrName),
        allItemsDisplayAttrName,
        allItemsDescriptionAttrName));
    }
  }
  return allItems;
}

This code finds the specified iterator, then extracts the attribute definitions. From there it uses a regular expression match to find if the attribute name is used in either the display or description strings. If an attribute is found, it is stored for later use.

The code then iterates through each of the rows in the iterator and then uses the stored attribute names to do a global replace on the strings with the actual values from the current row.

I hope this is simple enough not to need an example project. If you have any questions, just pop a reply in here and I can get back to you.

I recently read a question on the JDeveloper forum about an advanced search. This intrigued me, and so I set about implementing this.

Required: I used the HR schema for my sample, so you need to have a connection to HR from JDeveloper to run it. The advanced search itself, however, is not tied to any particular schema and has been made to be as generic as possible.

Firsly, let me show what an example search form will look like using this method:



The first drop down box has been populated with field labels, based on a managed property that you define as a list of field names. The second drop down box has two options: Is and Is Not, and the third drop down box has four different operators: Like, Equal To, Greater Than and Less Than. To perform a search on one field you would select the field label from the first list, select whether you want the field to match or not match the parameters from the second list, select the relevant operator from the third list and enter a value in the input box. Once the search button is clicked, the associated table will update to show the results.

The other two buttons are there to add and remove a search criteria. If you click the add criteria button, another row will be added with a drop down box before it containing two items: And and Or. This allows you to select how the search method will treat the two criteria (whether the records must match both, or just one).

Here's an example of the search form with two criteria and the results shown in the table below:



Most of the work of this search form is done by a backing bean named SearchCriteriaModelAdapter. There are just a few things to set up in the faces-config.xml and on the page itself, to make it work. Before I get into the code that lies behind all this, I'll show you what set up is actually needed.

In the faces-config.xml file, you need to add a session-scoped managed bean that has the class SearchCriteriaModelAdpater. You then need to add two managed properties: searchIteratorName that contains the name of the iterator binding (in the case of my sample EmployeesSearchIterator) and searchAttrNames that contains a list of attribute names (e.g. FirstName and LastName). That's it for the bean. On the page you need to do a bit more.

<af:panelForm partialTriggers="Add Remove">
  <af:forEach items="#{searchCriteria.model}" var="criteria"
              varStatus="vs">
    <af:panelHorizontal halign="center">
      <af:objectSpacer width="50" height="10"
                       rendered="#{vs.count == 1}"/>
      <af:selectOneChoice value="#{criteria.andConjunction}"
                          rendered="#{vs.count > 1}">
        <af:selectItem label="And" value="true"/>
        <af:selectItem label="Or" value="false"/>
      </af:selectOneChoice>
      <af:selectOneChoice value="#{criteria.attrName}">
        <f:selectItems value="#{searchCriteria.attrList}"/>
      </af:selectOneChoice>
      <af:selectOneChoice value="#{criteria.notConjunction}">
        <af:selectItem label="Is" value="false"/>
        <af:selectItem label="Is Not" value="true"/>
      </af:selectOneChoice>
      <af:selectOneChoice value="#{criteria.operator}">
        <f:selectItems value="#{searchCriteria.operatorList}"/>
      </af:selectOneChoice>
      <af:inputText value="#{criteria.attrValue}"/>
    </af:panelHorizontal>
  </af:forEach>
  <f:facet name="footer">
    <h:panelGroup>
      <af:commandButton text="Add criteria"
                        actionListener="#{searchCriteria.addCriteria}"
                        id="Add" partialSubmit="true"/>
      <af:commandButton text="Remove criteria"
                        actionListener="#{searchCriteria.removeCriteria}"
                        id="Remove" partialSubmit="true"/>
      <af:commandButton text="Search"
                        actionListener="#{searchCriteria.updateView}"
                        id="Search" partialSubmit="true"/>
    </h:panelGroup>
  </f:facet>
</af:panelForm>

Above is a code sample showing what is needed to set up the search form. The managed bean is called searchCriteria in this example. There is a forEach component that is mapped to the model property of the bean. This property is a list of criteria, each of which contain values defining the search attribute name, the operator in use, the attribute value, whether it should be an and conjuction or an or conjunction and whether it should match the condition or not. Each of these properties are mapped to the values of four selectOneChoice components and an inputText component. The items in the attribute and operator selectOneChoice components are mapped to another two attributes from the bean (attrList and operatorList respectively). The first of these is constructed from the list of attribute names defined in the faces-config.xml, while the other is hard-coded in the backing bean class (this could be extended to include other operators). The three commandButton components each have a corresponding action listener method in the backing bean.

Now I'll talk about the code in the backing bean class. The SearchCriteriaModelAdapter class has two attributes that are set in the faces-config (searchIteratorName and searchAttrNames) and three others accessible from EL (model, attrList and operatorList). The operatorList is the most simple to explain - it is a list containing a few SelectItems corresponding to the different operators available. This maps directly to the selectItems component on the page. The attrList property is constructed from the list of attribute names defined in the searchAttrNames property. The code below shows how the label for each attribute is found and then added to a SelectItem instance.

public List getAttrList()
{
  if (attrList == null)
  {
    attrList = new ArrayList();
    ViewObject view = getViewObject();
    for (int i = 0; i < searchAttrNames.size(); i++)
    {
      String searchAttrName = (String) searchAttrNames.get(i);
      AttributeDef attrDef = view.findAttributeDef(searchAttrName);
      if (attrDef != null)
      {
        String label = 
          attrDef.getUIHelper().getLabel(ADFUtils.getDCBindingContainer().getLocaleContext());
        SelectItem item = new SelectItem(searchAttrName, label);
        attrList.add(item);
      }
    }
  }
  return attrList;
}

The getViewObject method uses the searchIteratorName property to find the DCBindingIterator and from that, the ViewObject. ADFUtils is a class that is distributed with the SRDemo sample and it is used here un-altered. Once this method is called once, the attrList is set up for the whole session.

The model is the main property of this class and the code below shows how it is constructed.

public List getModel()
{
  if (model == null)
  {
    model = new ArrayList();
    model.add(getBlankItem());
  }
  return model;
}

private SearchCriteriaItem getBlankItem()
{
  return new SearchCriteriaItem((String)searchAttrNames.get(0), "like", "", true, false);
}

At the start it is simply populated with a "blank" criteria. The SearchCriteriaItem class is a simple bean with five properties for storing the attribute name, the operator, the attribute value, whether to use an and conjuction or and or conjunction, and whether to use a not conjunction. A blank criteria in this case defines default values for all attributes other than the attribute value, which really is blank. This means that the top item in each of the drop down boxes will be selected, which is probably what the user wants. You could change this so that you can define which attribute name or which operator to use as the defaults. You could even define a default value to go in the input field.

The methods to add and remove a criteria row are quite simple too.

public void addCriteria(ActionEvent event)
{
  model.add(getBlankItem());
}

public void removeCriteria(ActionEvent event)
{
  model.remove(model.size() - 1);
  if (model.size() == 0) model.add(getBlankItem());
}

The removeCriteria method ensures that if the user removes the last criteria, there will always be a blank criteria put in its place.

The updateView method is called when the search button is click and it is the main method in the class.

public void updateView(ActionEvent event)
{
  ViewObject view = getViewObject();
  ViewCriteria criteria = view.createViewCriteria();
  for (int i = 0; i < model.size(); i++)
  {
    SearchCriteriaItem item = (SearchCriteriaItem)model.get(i);
    if (!item.getAttrValue().equals(""))
    {
      String attrName = item.getAttrName();
      String operator = item.getOperator();
      String attrValue = item.getAttrValue();
      ViewCriteriaRow row = criteria.createViewCriteriaRow();
      row.setAttribute(attrName, operatorMap.get(operator) + " '" + attrValue + "'");
      if (item.isAndConjunction())
      {
        if (item.isNotConjunction()) row.setConjunction(ViewCriteriaRow.VCROW_CONJ_NOT | ViewCriteriaRow.VCROW_CONJ_AND);
        else row.setConjunction(ViewCriteriaRow.VCROW_CONJ_AND);
      }
      else
      {
        if (item.isNotConjunction()) row.setConjunction(ViewCriteriaRow.VCROW_CONJ_NOT | ViewCriteriaRow.VCROW_CONJ_OR);
      }
      criteria.addElement(row);
    }
  }
  view.applyViewCriteria(criteria);
  view.clearCache();
}

Using all the values from each of the SearchCriteriaItems in the model, this method constructs a ViewCriteria object and applies it to the view of the specified iterator. The clearCache method is called so that the view and its iterator are refreshed.

Since each SearchCriteriaItem object created has default values given to it, you can make the search form as simple or as complex as you want. For example, you could simply not include drop downs for And/Or or Is/Is Not, since the default values of these are probably what most users will want (it's the default behaviour of a normal search form).



That's all there is to it. I have created a sample application which has two pages - one with the full advanced search form and the other with the simpler form described above. You can download this below.

CustomSearch.zip

This is a query rather than a solution, although if someone works out how to do it, I'll update this post with the solution.

I have constructed a very simple example schema to illustrate my query. Here is a picture of the schema.


It has three entity tables: Employee, Department and Location, and two intersection tables: Employee_Location and Department_Location.

What I want to do is have a creation process for an employee where you first fill in the details (forname and surname), then assign them to department, then depending on that department assign them to one or more possible locations.

So each department is based in one or more locations and an employee can also be based in one or more locations, but only those where the department is based (imagine that the employee is a door-to-door salesman and the department has several areas of a town that it covers).

The first two steps are easy - the details step can be modelled using a form on the employee view object and the department assignment can be done with a list of values drop down on the department view object.

The third step is a little more complex because you can't just show a list of all locations, since some of these will not apply to the department. So my question is how do I model this using ADF Business Components?

Update: 21st March 2007
I have now been able to solve this problem thanks to a blog entry by Frank Nimphius: Working with the afselectManyListsbox (Part I). This describes how to use the ViewCriteria object to perform a search on a view using a list of values, which is exactly what I needed - thanks Frank.

MetaLink is a good place to solve your major problems, that is of course if your work has a MetaLink account.

If the solution to your problem is an Oracle patch, the instructions will tell you that you need perl to be able to install it. This comes with OracleAS, but not with JDeveloper or standalone OC4J.

After being told that a patch would solve a problem I had in JDeveloper embedded OC4J and the standalone OC4J, I queried MetaLink on how to install them without perl. They came back with a load of command prompt lines that were specific to the patch to install them. I decided to make a generic batch script to install any patch to any OC4J installation.

orapatch.bat

@echo off
:: Installer for Oracle Patches
:: Parameters:
:: <patch path> The absolute or relative path to the patch folder
:: <oc4j path> The absolute or relative path to an oc4j instance
:: <backup dir> The directory name to store the backups
:: e.g. orapatch C:\1234567 "C:\Program Files\JDeveloper" JDeveloper
:: or D:\7654321 D:\oc4j "Standalone OC4J"

:initial
if "%~3"=="" goto useerror

set PATCH_DIR=%~f1
set OC4J_DIR=%~f2
set BACKUP_DIR=%~3

if not exist "%PATCH_DIR%" goto patherror2
if not exist "%PATCH_DIR%\files\j2ee\home\" goto patherror2

if not exist "%OC4J_DIR%" goto patherror
if not exist "%OC4J_DIR%\j2ee\home\lib" goto patherror

if not exist "%PATCH_DIR%\backup" md "%PATCH_DIR%\backup"
if not exist "%PATCH_DIR%\backup\%BACKUP_DIR%" md "%PATCH_DIR%\backup\%BACKUP_DIR%"

if not exist "%PATCH_DIR%\temp" md "%PATCH_DIR%\temp"
set TEMP_DIR=%PATCH_DIR%\temp

if not exist "%PATCH_DIR%\files\j2ee\home\lib\oc4j-internal.jar" goto client

echo Patching oc4j-internal.jar
copy "%OC4J_DIR%\j2ee\home\lib\oc4j-internal.jar" "%TEMP_DIR%\oc4j-internal.jar" > NUL
jar -ufv0 "%TEMP_DIR%\oc4j-internal.jar" -C "%PATCH_DIR%\files\j2ee\home\lib\oc4j-internal.jar" .
copy "%OC4J_DIR%\j2ee\home\lib\oc4j-internal.jar" "%PATCH_DIR%\backup\%BACKUP_DIR%\oc4j-internal.jar.original" > NUL
copy "%TEMP_DIR%\oc4j-internal.jar" "%OC4J_DIR%\j2ee\home\lib" > NUL

:client
if not exist "%PATCH_DIR%\files\j2ee\home\oc4jclient.jar" goto finish

echo.
echo Patching oc4jclient.jar
copy "%OC4J_DIR%\j2ee\home\oc4jclient.jar" "%TEMP_DIR%\oc4jclient.jar" > NUL
jar -ufv0 "%TEMP_DIR%\oc4jclient.jar" -C "%PATCH_DIR%\files\j2ee\home\oc4jclient.jar" .
copy "%OC4J_DIR%\j2ee\home\oc4jclient.jar" "%PATCH_DIR%\backup\%BACKUP_DIR%\oc4jclient.jar.original" > NUL
copy "%TEMP_DIR%\oc4jclient.jar" "%OC4J_DIR%\j2ee\home" > NUL
goto finish

:finish
echo.
echo OC4J installation at %OC4J_DIR% patched.
echo Backed up files stored in %PATCH_DIR%\backup\%BACKUP_DIR%.
rd /S /Q "%TEMP_DIR%"
set PATCH_DIR=
set OC4J_DIR=
set BACKUP_DIR=
set TEMP_DIR=
goto done

:useerror
echo Usage: orapatch ^<patch path^> ^<oc4j path^> ^<backup dir^>
echo.
echo e.g. orapatch C:\Patches\7654321 C:\JDeveloper JDeveloper
goto done

:patherror
echo Specified path (%OC4J_DIR%) is not an OC4J installation.
goto done

:patherror2
echo Specified path (%PATCH_DIR%) is not an Oracle patch.
goto done

:done

If you copy and paste this into a new text file called orapatch.bat and then save it. You can run it from the command prompt with this command:

orapatch <patch path> <oc4j path> <backup dir>

Where <patch path> is the absolute or relative path to the extracted patch folder, <oc4j path> is the absolute or relative path to the OC4J installation and <backup dir> is the name of the directory you want the backed up files to go to.

Example:

orapatch "C:\Oracle Patches\1234567" "C:\Program Files\JDeveloper" "JDeveloper Backup"

When you run the batch file you will see that it adds some files to two jars in the OC4J installation.

All you need to do then is test that it works as it should. If for some reason it doesn't, the output at the end of the script will tell you where the original files are.

Edited 6th December 2006: Fixed a couple of bugs in the script