Query and Filter an af:listView

Most of the time we use tables to show tabular data to users. However, JDev and ADF allow for other components like the af:listView to be used to show such data to the user in a more modern way.

The image above shows the normal display of data when an af:query is used together with a table to show the result.

A more fancy, modern look we get if we use a af:listView to show the results as this allows us to style the data

Use case 1

We like to use an af:query to search for employees and show the result in a styled af:listView.

Implementation 1

This is pretty easy as we only have to use an af:listView as the result component of the af:query

And to exchange the af:table with an af:listView. Or you build the page by first dropping an af:query onto the page (without table) and then add the af:listView

Then you get the wizard to layout the list

This will give you a basic layout which can be styles in JDev as

The final result is

which looks more modern. One thing the af:table give you out of the box is the second use case.

User Case 2

We like the af:listView to be able to be filter the result like the af:table can.

Implementation of second use case

Easy you think? Well, the af:listView component doesn’t provide any filter out of the box. There isn’t even a filterModel like there is for an af:table.

So, how do we get this implemented. The idea is to use a af:table component but only use the filter provided by the af:table. The remaining parts like table data, possible scroll bars and status bar or scrollbars we remove.

We start by dragging the EmployeesView1 from the data control onto the page again.

And drop it after the closing af:panelHeader and before the af:listView as ‘ADF Table’

In the image you see that I have removed some available columns. Before we go to hide the part of the table we don’t need, we make the table work together with the af:query and the af:listView. When we use the af:query the table shows the right detail (auto PPR triggers the refresh of the table). However, if you have queried for the ‘Purchasing’ department and then enter an ‘s’ into the ‘First Name’ filter field of the table and hit enter, you get

As you see, the table shows the right result (2 rows) but the listView still shows all employees of the Purchasing department.

To make it work, we need to add a partialTrigger to the listView which points to the table. This way each time the table changes the listView will too.

Save all changes and refresh the page. Now if you enter a value into a filter field and hit enter, the listView will update too.

After the page works we have to get rid of the data below the header of the table. This is easy to accomplish by styling the table. We only need the filter field and the header below the filter fields so that we know which field filters which data. Simply set the maxHeight of the table to the exact height of the the two components. You can use your browser’s developer tools (F12) to measure the height. In my sample it’s 65px. So, setting the tables inlinestyle to

max-height: 65px;

will hide everything below the filter and the header

If you like you can create a skin and create a style class and use this style class instead of setting the max-height directly to the inlineStyle of the table. A nice addon is that the table header sorting is working too for the listView.

You can download the sample from gitHub BlogFilterListView. The sample is build using JDev 12.2.1.3 and uses the HR DB schema. The principle can be used in other JDev versions too.

Advertisements

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 11.1.1.4 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 11.1.1.4.0. 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" 
  rows="#{bindings.allEmployees.rangeSize}"
  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}"
  selectionListener="#{bindings.allEmployees.collectionModel.makeCurrent}" 
  rowSelection="single" id="t1" styleClass="AFStretchWidth"  partialTriggers="::cb1"
  queryListener="#{EmployeeQueryBean.onEmployeeQuery}">

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:inputDate>
                                                </af:panelLabelAndMessage>
                                                <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>
                                                </af:panelLabelAndMessage>
                                            </af:panelGroupLayout>
                                        </f:facet>
                                        <af:inputDate value="#{row.bindings.HireDate.inputValue}" label="#{bindings.allEmployees.hints.HireDate.label}"
                                                      required="#{bindings.allEmployees.hints.HireDate.mandatory}"
                                                      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}"/>
                                        </af:inputDate>
                                    </af:column>

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
        //#{bindings.allEmployeesQuery.processQuery}

        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
        rangeStartOperationBinding.execute();
        rangeEndOperationBinding.execute();

        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

JDev 12c: How to reset a filter on an af:table the 12c way

This post is a continuation of an earlier blog about how to reset a filter on an af:table.
A question on OTN JDev and ADF spaces brought a change to my attention which I like to share here.
Using the code from the former post now results in a depreated warning in 12c:

    public void resetTableFilter(ActionEvent actionEvent)
    {
        FilterableQueryDescriptor queryDescriptor =
            (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterCriteria() != null)
        {
            queryDescriptor.getFilterCriteria().clear();
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

Warning(7,28): getFilterCriteria() in oracle.adf.view.rich.model.FilterableQueryDescriptor has been deprecated

and a look into the javadoc for the getFilterCriteria() method showed

Deprecated. Please use #getFilterConjunctionCriterion

AFAIK you only get the javadoc if you have access to the source code of ADF which you can get via support.oracle.com.

Knowing what to use instead of the deprecated method is half the solution. It turned out that it’s not enough to to use the new method to get the FilterConjunctionCriterion but that you have to iterate over the ConjunctionCriterion and reset them one by one. Here you have to check which type of ConjunctionCriterion you get from the iterator as there are two

  1. AttributeCriterion
  2. ConjunctionCriterion

Only the AttributeCriterion needs to be reset, the ConjunctionCriterion represents a group of AttributeCriterion.
The final code looks like:

    /**
     * method to reset filter attributes on an af:table
     * @param actionEvent event which triggers the method
     */
    public void resetTableFilter(ActionEvent actionEvent) {
        FilterableQueryDescriptor queryDescriptor = (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterConjunctionCriterion() != null) {
            ConjunctionCriterion cc = queryDescriptor.getFilterConjunctionCriterion();
            List<Criterion> lc = cc.getCriterionList();
            for (Criterion c : lc) {
                if (c instanceof AttributeCriterion) {
                    AttributeCriterion ac = (AttributeCriterion) c;
                    ac.setValue(null);
                }
            }
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

The rest of the implementation remained unchanged so you only need exchange the resetTableFilter method in ResetTableFilterBean.java.
The sample used the HR schema as DB connection. You can download the sample workspace for JDev12c from the ADF-EMG Sample Repository.

JDev: How to reset a filter on an af:table

In my last blog entry “How to reset or undo a af:table sort” I showed how to clear a sort on a column of an af:table.

Chris Muir asked me to do a follow up showing how to do the same for a filter an an af:table. A short research about this question did not turn up anything. If somebody already has bloged about this, please drop me note and I’ll mention you for reference.

************
OK, just 5 minutes after first publishing I found Steve Muenchs sample #146 at http://blogs.oracle.com/smuenchadf/resource/examples. More to come?
************

You can download a sample workspace, which was set up using JDeveloper 11.1.2.1.0 and the HR schema as DB connection, using the link at the end of the blog.

The use case for the blog is

  1. A use has a filterable af:table on a page. At some point he fills in one or more filter criteria and executes the query.
  2. Now he wants to clear the filter criteria to show all rows again.

Here is the code for a table with filtering enabled. As you see the filter is implemented as a filterModel (line 06: filterModel=”#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}”).

                        <af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                  rows="#{bindings.EmployeesView1.rangeSize}"
                                  emptyText="#{bindings.EmployeesView1.viewable ? 'No data to display.' : 'Access Denied.'}"
                                  fetchSize="#{bindings.EmployeesView1.rangeSize}"
                                  rowBandingInterval="0"
                                  filterModel="#{bindings.ImplicitViewCriteriaQuery.queryDescriptor}"
                                  queryListener="#{bindings.ImplicitViewCriteriaQuery.processQuery}"
                                  filterVisible="true" varStatus="vs"
                                  selectedRowKeys="#{bindings.EmployeesView1.collectionModel.selectedRow}"
                                  selectionListener="#{bindings.EmployeesView1.collectionModel.makeCurrent}"
                                  rowSelection="single" id="resId1" styleClass="AFStretchWidth"
                                  binding="#{ResetTableFilterBean.empTable}"
                                  columnStretching="multiple">
                            <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                                       filterable="true" sortable="true"
                                       headerText="#{bindings.EmployeesView1.hints.EmployeeId.label}"
                                       id="resId1c1">

From the javadoc

getFilterModel

public final java.lang.Object getFilterModel()

    Gets the model used for filtering of data in the table. This attribute must be bound to an instance of FilterableQueryDescriptor class.

we see that the filterModel needs to be casted to a FilterableQueryDescriptor. A look into the javadoc shows that the class has a method to get a map of all criteria entered into the filter fields of a table. Here’s the javadoc for the FilterableQueryDescriptor.getFilterCriteria() method:

getFilterCriteria

public abstract java.util.Map<java.lang.String,java.lang.Object> getFilterCriteria()

    Gets the filter criteria associated with the query descriptor. Filter Criteria are generally useful for filtering data in the table.

    Returns:
        Map<String, Object> containg the filterCriteria

Clearing this map clears the filter fields of the table. Finally we queue an query event to the table to refresh it. Here is the bean code:

public class ResetTableFilterBean
{
    private RichTable empTable;

    public ResetTableFilterBean()
    {
    }

    public void resetTableFilter(ActionEvent actionEvent)
    {
        FilterableQueryDescriptor queryDescriptor =
            (FilterableQueryDescriptor) getEmpTable().getFilterModel();
        if (queryDescriptor != null && queryDescriptor.getFilterCriteria() != null)
        {
            queryDescriptor.getFilterCriteria().clear();
            getEmpTable().queueEvent(new QueryEvent(getEmpTable(), queryDescriptor));
        }
    }

    public void setEmpTable(RichTable empTable)
    {
        this.empTable = empTable;
    }

    public RichTable getEmpTable()
    {
        return empTable;
    }
}

In the following picture we see a page holding the query panel with a table with filter enabled. A search for employees with last name starting with ‘K’ has been executed and the result has been filtered for last name starts with ‘Ki’.

Query Panel with Filterable Table

Query Panel with Filterable Table

After a click an the ‘Reset Table Filter’ button clears the filter and shows the result for the query only.

Filter reset

Filter reset

The sample workspace uses JDeveloper 11.1.2.1.0 but the bean code should work in older JDeveloper version 11.1.1.x too. The sample used the HR schema as DB connection. You can download the sample workspace from BlogResetTableFilter.zip
After downloading the file rename it to ‘BlogResetTableFilter.zip’!

The sample also contains an other page showing the same for a simple table without the query panel.