ADF: Table filters case insensitive and programmatic wildcards

Challenge

When the user uses the filter functionality on top of the ADF tables, every character based columns should be made case insensitive and will need the necessary wildcards.  So when the user adds wildcards, then no extra wildcards are necessary.  When the user doesn't use wildcards, add them programmatically.

Context

JDeveloper & ADF : 11.1.1.5.0
OS:  Windows 7

Solution

Since this is a feature that we want to apply to multiple tables, we are going to build a very generic solution.
To simplify the usage of the solution, we are going to put the necessary java code in a "base"-bean.  The latter is comparable with the base-classes in ADF Business Components.  Every managed bean that needs this functionality, just needs to extend from this "base"-bean.

Here are the different steps to undertake:

• Create the "base"-bean.

Just create a new java class, or use an existing one.  Like with the base classed of ADF BC, it is best to put this in a seperate application or at least in a different package structure.  But do not put them, the BC and the view once, together.  They always should remain in different projects.
Here is the content of the java class:

package view.framework;


import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import oracle.adf.view.rich.component.rich.data.RichTable;
import oracle.adf.view.rich.event.QueryEvent;
import oracle.adf.view.rich.model.FilterableQueryDescriptor;

import oracle.adfinternal.view.faces.model.binding.FacesCtrlSearchBinding;

import oracle.jbo.AttributeDef;
import oracle.jbo.common.JboTypeMap;
import oracle.jbo.uicli.binding.JUCtrlHierBinding;
import oracle.jbo.uicli.binding.JUFormBinding;

import org.apache.commons.lang.StringUtils;
import org.apache.myfaces.trinidad.model.CollectionModel;

import view.JSFUtils;

public class BaseBean {
    public BaseBean() {
    }
    /**
     * This method can be used to replace the existing queryListener for a table.
     * It will automatically put the used, char based columns into case insensitive
     * and add the necessary wild cards, when the user hasn't done so already.
     * It assumes that the Iterator name equals the Query name.
     * For example : DepartmentsView1Iterator and DepartmentsView1Query
     * @param queryEvent
     * @writer Filip Huysmans
     */
    public void onQuery(QueryEvent queryEvent) {
        boolean invokeQuery = true;
        List<String> columnsWithExtraWildCards = new ArrayList<String>();
        // pre-processing code here
        // Fetching the necessary objects
        FilterableQueryDescriptor fqd = (FilterableQueryDescriptor)queryEvent.getDescriptor();
        Map<String, Object> map = fqd.getFilterCriteria();
        RichTable rt = (RichTable)queryEvent.getComponent();
        CollectionModel model = (CollectionModel)rt.getValue();
        JUCtrlHierBinding data = (JUCtrlHierBinding)model.getWrappedData();
        String iteratorName = data.getIteratorBinding().getName();
        // Creating the search filter feature of case insensitive
        Set<FilterableQueryDescriptor.FilterFeature> featureSet = new HashSet<FilterableQueryDescriptor.FilterFeature>();
        featureSet.add(FilterableQueryDescriptor.FilterFeature.CASE_INSENSITIVE);
        Map<String, Set<FilterableQueryDescriptor.FilterFeature>> _filterFeatures = new HashMap<String, Set<FilterableQueryDescriptor.FilterFeature>>();
        // Looping through the entire set of attributes
        for (int i = 0; i < data.getAttributeCount(); i++) {
            AttributeDef attributeDef = data.getAttributeDef(i);
            if (JboTypeMap.isCharType(attributeDef.getSQLType())) {
                // this is a string type
                // Get the filter value for this attribute
                String stringValue = (String)map.get(attributeDef.getName());
                // When the user has filled in a search string and didn't use any wildcards
                // add the necessary wildcards
                if (stringValue != null && !stringValue.isEmpty()) {
                    if (!stringValue.contains("%")) {
                        // Adding the wildcards
                        stringValue = "%" + stringValue + "%";
                        map.put(attributeDef.getName(), stringValue);
                        // Adding this column to the list of custom wild cards
                        columnsWithExtraWildCards.add(attributeDef.getName());
                    }
                    // Setting the field Case Insensitive
                    ((HashMap)_filterFeatures).put(attributeDef.getName(), featureSet);
                }
            }
        }
        // Setting the new list of filter features
        fqd.setFilterFeatures(_filterFeatures);
        // Executing the query
        if (invokeQuery) {
            //Finding the name of the searchRegionBinding based on the name of the iterator
            String searchRegionBindingName = null;
            JUFormBinding bindings = (JUFormBinding)JSFUtils.get("#{bindings}");
            List executableBindings = bindings.getExecutableBindings();
            if (executableBindings != null && executableBindings.size() > 0) {
                for (Object o : executableBindings) {
                    if (o instanceof FacesCtrlSearchBinding) {
                        FacesCtrlSearchBinding fcsb = (FacesCtrlSearchBinding)o;
                        // Create the iterator name from the query name
                        String newIteratorName = StringUtils.strip(fcsb.getName(), "Query") + "Iterator";
                        if (newIteratorName.equals(iteratorName)) {
                            // This is the correct SearchRegionBinding
                            searchRegionBindingName = fcsb.getName();
                        }
                    }
                }
            }
            // Executing the query manually
            JSFUtils.invokeMethodExpression("#{bindings." + searchRegionBindingName + ".processQuery}", Object.class, QueryEvent.class, queryEvent);
        }
        // post processing code here
        // Loop through the map of filter values and remove the added wildcards
        Set<Map.Entry<String, Object>> entrySet = map.entrySet();
        if (!map.isEmpty()) {
            for (Map.Entry<String, Object> e : entrySet) {
                if (isInList(columnsWithExtraWildCards, e.getKey())) {
                    // This column has had extra wildcards
                    // Remove them
                    String s = (String)e.getValue();
                    s = s.replaceAll("%", "");
                    map.put(e.getKey(), s);
                }
            }
        }
    }
    /**
     * Verifies whether a String exists in a list of strings
     * Should reside in another utility class
     * @param list
     * @param value
     * @return
     * @writer Filip Huysmans
     */
    private boolean isInList(List<String> list, String value) {
        boolean returnValue = false;
        if (list != null && !list.isEmpty()) {
            for (String s : list) {
                if (s.equals(value)) {
                    returnValue = true;
                    break;
                }
            }
        }
        return returnValue;
    }

}

For the JSFUtils reference: look here.

• Create the managed bean that will be used for the table references.

Now that the base-bean exists, create a managed bean or use an existing one.  This managed bean doesn't need to have anything else in it, just the reference to the BaseBean class.  Request-scope is fine for this managed bean.

package view.mb;

import view.framework.BaseBean;

public class CustomTableFiltersMB extends BaseBean {
}

Here is the code for the taskflow definition:

  <managed-bean id="__17">
    <managed-bean-name id="__15">CustomTableFiltersMB</managed-bean-name>
    <managed-bean-class id="__16">view.mb.CustomTableFiltersMB</managed-bean-class>
    <managed-bean-scope id="__18">request</managed-bean-scope>
  </managed-bean>

• Change the queryListener property of the table to the onQuery method.

Now just create a table on a page.  In my example, I've used the DepartmentsView and dropped it on an empty page as a Read-Only table.  Just remember to activate the Filtering functionality.  Everything else, I left to their default values.
When you look at the source of your page, you will notice that in the af:table tag, a queryListener property was set.  The only thing you need to do, is to remplace this queryListener property with the onQuery-property of your managed bean.
Here is the code of the entire page:

<?xml version='1.0' encoding='UTF-8'?>
<jsp:root xmlns:jsp="http://java.sun.com/JSP/Page" version="2.1"
          xmlns:f="http://java.sun.com/jsf/core"
          xmlns:h="http://java.sun.com/jsf/html"
          xmlns:af="http://xmlns.oracle.com/adf/faces/rich">
  <jsp:directive.page contentType="text/html;charset=UTF-8"/>
  <f:view>
    <af:document id="d1">
      <af:messages id="m1"/>
      <af:form id="f1">
        <af:table value="#{bindings.DepartmentsView1.collectionModel}" var="row"
                  rows="#{bindings.DepartmentsView1.rangeSize}"
                  emptyText="#{bindings.DepartmentsView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                  fetchSize="#{bindings.DepartmentsView1.rangeSize}"
                  rowBandingInterval="0"
                  filterModel="#{bindings.DepartmentsView1Query.queryDescriptor}"
                  queryListener="#{CustomTableFiltersMB.onQuery}"                  filterVisible="true" varStatus="vs" id="t1">
          <af:column sortProperty="DepartmentId" filterable="true"
                     sortable="false"
                     headerText="#{bindings.DepartmentsView1.hints.DepartmentId.label}"
                     id="c2">
            <af:outputText value="#{row.DepartmentId}" id="ot3">
              <af:convertNumber groupingUsed="false"
                                pattern="#{bindings.DepartmentsView1.hints.DepartmentId.format}"/>
            </af:outputText>
          </af:column>
          <af:column sortProperty="DepartmentName" filterable="true"
                     sortable="false"
                     headerText="#{bindings.DepartmentsView1.hints.DepartmentName.label}"
                     id="c4">
            <af:outputText value="#{row.DepartmentName}" id="ot2"/>
          </af:column>
          <af:column sortProperty="ManagerId" filterable="true" sortable="false"
                     headerText="#{bindings.DepartmentsView1.hints.ManagerId.label}"
                     id="c1">
            <af:outputText value="#{row.ManagerId}" id="ot1">
              <af:convertNumber groupingUsed="false"
                                pattern="#{bindings.DepartmentsView1.hints.ManagerId.format}"/>
            </af:outputText>
          </af:column>
          <af:column sortProperty="LocationId" filterable="true"
                     sortable="false"
                     headerText="#{bindings.DepartmentsView1.hints.LocationId.label}"
                     id="c3">
            <af:outputText value="#{row.LocationId}" id="ot4">
              <af:convertNumber groupingUsed="false"
                                pattern="#{bindings.DepartmentsView1.hints.LocationId.format}"/>
            </af:outputText>
          </af:column>
        </af:table>
      </af:form>
    </af:document>
  </f:view>
</jsp:root>

Run this page and you will notice that for every character based field, the filters are case insensitive and that the wildcards are added to them.
I also made sure, that the wildcards that are added, are also removed after the query.  This to keep the user interface clean.  Wildcards added by the end-users are left in tact.

ToDo

If somebody can help me in linking the Iterator to the SearchRegion, or viceversa, in a more ellegant manner then based on the name, would be a big step foreward.