How-to filter ADF bound tables by date range (JDeveloper 12.1.x)

Based on an older article from Frank Nimphius How-to filter ADF bound tables by date range JDeveloper I got a interesting question on the OTN JDeveloper & ADF forum why the solution provided in the article does not work in JDev 12c.

The solution from Frank’s article is designed for JDev Today’s version of JDev is 12.1.3 where the solution does not seem to work. Migrating the source of the article and running it under JDev 12.1.3 indeed shows, that filtering the employees records for a date range does not work at all. Setting dates into the filter and hitting enter to activate the filter does not filter the data in the table.
The reason for this was easily found by debugging the code. Set a breakpoint into the query listener which is setup in the table

<af:table value="#{bindings.allEmployees.collectionModel}" var="row" 
  emptyText="#{bindings.allEmployees.viewable ? 'No data to display.' : 'Access Denied.'}"
  fetchSize="#{bindings.allEmployees.rangeSize}" rowBandingInterval="0"
  filterModel="#{bindings.allEmployeesQuery.queryDescriptor}" filterVisible="true" 
  varStatus="vs" selectedRowKeys="#{bindings.allEmployees.collectionModel.selectedRow}"
  rowSelection="single" id="t1" styleClass="AFStretchWidth"  partialTriggers="::cb1"

As you can see it’s pointing to a bean method ‘onEmplyoeeQuery’. A look into this method reveals that the method FilterableQueryDescriptor.getFilterCriteria() has been deprecated.

        FilterableQueryDescriptor fqd = (FilterableQueryDescriptor) queryEvent.getDescriptor();
        Map map = fqd.getFilterCriteria();

Instead of the deprecated method you should use the method FilterableQueryDescriptor.getFilterConjunctionCriterion() which now holds the map of parameters.

        FilterableQueryDescriptor fqd = (FilterableQueryDescriptor) queryEvent.getDescriptor();
        ConjunctionCriterion cc = fqd.getFilterConjunctionCriterion();
        Map<String, Criterion> criterionMap = cc.getCriterionMap();

When you set a breakpoint in this method and step through the code you see that the values entered into the filter fields in the UI are not visible in the map as Frank describes in his article.

Criterion Map and old FilterCriteria Map

Criterion Map and old FilterCriteria Map

As you can see there are no map entries for the made up variables ‘HireStartRange’ and ‘HireEndRange’. This is the reason the filter by date range does not work. There are simply not dates to filter the rows.

I’m not sure if this is a bug or a change in behavior which was made for a reason. Anyway, you can’t just simply add values to the map anymore.

The solution to fix the problem is simple. As you can’t store additional values in the criterion map, you have to store the values entered by the user somewhere else. A valid storage area is the variables iterator each pagedef holds.
In one of my other blogs Creating Variables and Attribute Bindings to Store Values Temporarily in the PageDef I showed how to add temporary variables in this iterator.

Create two new variables inside the variable iterator of type oracle.jbo.domain.Date, name them ‘startDate’ and ‘endDate’. Then create attribute bindings for them.
The final touch is to wire the new variables up in the HireDate filter for start range and end range:

                                    <af:column sortProperty="HireDate" filterable="true" sortable="true"
                                               headerText="#{bindings.allEmployees.hints.HireDate.label}" id="c1" width="277">
                                        <f:facet name="filter">
                                            <af:panelGroupLayout id="pgl2" layout="horizontal">
                                                <af:panelLabelAndMessage label="From: " id="plam1">
                                                    <af:inputDate id="id2" value="#{bindings.startDate1.inputValue}" clientComponent="false">
                                                        <af:convertDateTime pattern="#{bindings.allEmployees.hints.HireDate.format}"/>
                                                        <f:validator binding="#{bindings.HireDate.validator}"/>
                                                <af:spacer width="5" height="5" id="s1"/>
                                                <af:panelLabelAndMessage label="To:" id="plam2">
                                                    <af:inputDate id="id3" value="#{bindings.endDate1.inputValue}" required="false" clientComponent="false">
                                                        <f:validator binding="#{bindings.HireDate.validator}"/>
                                                        <af:convertDateTime pattern="#{bindings.allEmployees.hints.HireDate.format}"/>
                                        <af:inputDate value="#{row.bindings.HireDate.inputValue}" label="#{bindings.allEmployees.hints.HireDate.label}"
                                                      shortDesc="#{bindings.allEmployees.hints.HireDate.tooltip}" id="id1" styleClass="AFStretchWidth">
                                            <f:validator binding="#{row.bindings.HireDate.validator}"/>
                                            <af:convertDateTime pattern="#{bindings.allEmployees.hints.HireDate.format}"/>

The code above shows the new column for the HireDate and the new storage location for the startDateRange as ‘value=”#{bindings.startDate1.inputValue}”‘ and EndDateRange as ‘value=”#{bindings.endDate1.inputValue}”‘. Next we change the bean method which reads the filter values and calls the query:

    public void onEmployeeQuery(QueryEvent queryEvent) {
        //default EL string created when dragging the table
        //to the JSF page

        BindingContext bctx = BindingContext.getCurrent();
        DCBindingContainer bindings = (DCBindingContainer) bctx.getCurrentBindingsEntry();

        //access the method bindings to set the bind variables on the ViewCriteria
        OperationBinding rangeStartOperationBinding = bindings.getOperationBinding("setHireDateRangeStart");
        OperationBinding rangeEndOperationBinding = bindings.getOperationBinding("setHireDateRangeEnd");

        // get the start date and end date from the temporary valiables
        AttributeBinding attr = (AttributeBinding) bindings.getControlBinding("startDate1");
        oracle.jbo.domain.Date sd = (oracle.jbo.domain.Date) attr.getInputValue();
        attr = (AttributeBinding) bindings.getControlBinding("endDate1");
        oracle.jbo.domain.Date ed = (oracle.jbo.domain.Date) attr.getInputValue();

        //set the start and end date of the range to search
        rangeStartOperationBinding.getParamsMap().put("value", sd);
        rangeEndOperationBinding.getParamsMap().put("value", ed);

        //set bind variable on the business service

        invokeMethodExpression("#{bindings.allEmployeesQuery.processQuery}", Object.class, QueryEvent.class, queryEvent);

In line 14-17 you see that we read the values from the newly created attribute bindings for the temporary variables. After removing the unnecessary parts of the code, which tried to read the values from the map, the rest of the code remains as is.

Here is an image of the now working filter by date range

Filter Table by Date Range

Filter Table by Date Range

Please note that if you run the sample in your environment, that you have to change the DB connection to the HR DB schema according to your environment. You can download the changed code for the sample from GitHub

5 thoughts on “How-to filter ADF bound tables by date range (JDeveloper 12.1.x)

  1. Thank you for this article. So the filter works in 12.1.3 but we lost the possibility to erase filter.

    I explain : we have surrounded the table with a panelcollection. In, if we click on the filter button of panelcollection, the filter area disappears AND the dates used to filter are erased and the table is refreshed with all the rows. In 12.1.3, the filter area disappears but the the dates used to filter are not erased : it’s logic because they are no more in the vs.filtercritria.

    Do you have an idea to erase the dates like the other inputs in filter area ?

    Thanks a lot,

    Anne GIBUT

  2. Hello,

    On may 26, I asked you a question about this article regarding the differnce between and 12.1.3 : when using QBE button in 12.1.3 the dates use to filter are not cleared as in I found a solution for this problem : we add a transient attribute in the VO and use it in the filter area and catch it in the querylistener to do what we want.

    Anne GIBUT

  3. Very good method on filter. I have a question here.

    What If the Table is already surrounded with Search Criteria and On top of it we have to apply this table filter. is this same code work?

    I have this scenario. Please suggest.


      • I just did testing on this scenario, it works, has some logical problems.
        more details can be found here:

        Description as follows:
        1. I have a Start Date with the value: (09.01.2015) in search Criteria and clicks on the Search button. I will get results for Start Date ONORAFTER 09.01.2015.
        here the results count is 20.
        2. Now I will apply table column filter for Start Date with value 01.01.2015 and I get this time more results. here the row count is 30. Ignoring the Search criteria and overridden with table filter.

        Logically I need to query the results only available on the table, not on the fresh query.

        is there anyway that I can achieve this.Please suggest.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s