ADF Table – Adding column filters programmatically

In the following post I will present an example of adf table filtering programmatically. You can download the sample Fusion Web Application at the end of the post. The sample application was developed in JDeveloper 11.1.1.2.0 based on HR scheme.

In the web interface a read-only table with employees information is displayed, including job title and department name. Users have custom filtering options like searching for first names, last names, phone number and department names without the need of introducing special characters like “%”. The filtering of HireDate and Salary columns can be combined with the show options from the menu above the tables. I’ve also implemented the Reset show options functionality which restores the full list of employees.

The business components contain 2 view objects: EmployeesVO and JobsVO. The EmployeesVO will be the filterable view object, meanwhile the JobsVO will be used to provide the dropdown for filtering jobs in the web interface. In EmployeesVO I have declared some view criterias that I will later use as filter conditions in the web interface.

To override the column filtering default functionality we simply need to change the queryListener property of af:table to an EL that points to method that takes as parameter a QueryEvent.

    <af:table value="#{bindings.EmployeesVO.collectionModel}"
                        filterModel="#{bindings.EmployeesVOQuery.queryDescriptor}"
                        queryListener="#{columnFilters.queryListener}"
          ...

     public void queryListener(QueryEvent queryEvent) {
        doQuery();
    }

Each column has the filter facet declared. The input elements in the filter facet will set the bind variables of view criteria. The doQuery method in the query listener will apply the view criteria and execute the query.

 <af:column sortProperty="FirstName" filterable="true"
                           sortable="true"
                           headerText="#{bindings.EmployeesVO.hints.FirstName.label}"
                           id="c1">
                  <f:facet name="filter">
                    <af:inputText value="#{columnFilters.firstNameKwd}"
                                  id="fnKwd"></af:inputText>
                  </f:facet>
                  <af:outputText value="#{row.FirstName}" id="ot2"/>
 </af:column>

    private void doQuery() {
        ViewObject vo =
            ADFUtils.findIterator(getIteratorName()).getViewObject();
        if (vo != null) {
            vo.getViewCriteriaManager().setApplyViewCriteriaNames(null);

            applyViewCriteriaOnViewObject(vo, "FirstNameVC");
            vo.setNamedWhereClauseParam("firstNameKwd", getFirstNameKwd());

            applyViewCriteriaOnViewObject(vo, "LastNameVC");
            vo.setNamedWhereClauseParam("lastNameKwd", getLastNameKwd());

            applyViewCriteriaOnViewObject(vo, "HireDateVC");
            vo.setNamedWhereClauseParam("startDateKwd", getStartDateKwd());
            vo.setNamedWhereClauseParam("endDateKwd", getEndDateKwd());

            applyViewCriteriaOnViewObject(vo, "DepartmentNameVC");
            vo.setNamedWhereClauseParam("departmentNameKwd",
                                        getDepartmentNameKwd());

            applyViewCriteriaOnViewObject(vo, "JobsVC");
            vo.setNamedWhereClauseParam("jobIdKwd", getJobIdKwd());

            setSalaryKewyords(getSalaryOption(), getSalaryKwd());
             applyViewCriteriaOnViewObject(vo, "SalaryVC");
            System.out.println(getStartSalaryKwd() +  " -- " + getEndSalaryKwd());
            vo.setNamedWhereClauseParam("startSalaryKwd", getStartSalaryKwd());
            vo.setNamedWhereClauseParam("endSalaryKwd", getEndSalaryKwd());

            applyViewCriteriaOnViewObject(vo, "PhoneNumberVC");
            vo.setNamedWhereClauseParam("phoneKwd", getPhoneNOKwd());

            vo.executeQuery();
        }

To implement dropdown list for jobs filtering I’ve added JobsVOIterator in the page definition file. In the bean I returned a list of SelectItems built based on this iterator using selectItemsForIterator method from Steve Muench and Duncan Mills ADFUtils implementation.

 <executables>
     <iterator id="JobsVOIterator" Binds="JobsVO"
              DataControl="ColumnFiltersAppDataControl" RangeSize="25"/>
 </executables>

   <f:facet name="filter">
                    <af:selectOneChoice value="#{columnFilters.jobIdKwd}"
                                        id="jiKwd" unselectedLabel="">
                      <f:selectItems value="#{columnFilters.jobList}"></f:selectItems>
                    </af:selectOneChoice>
   </f:facet>

    public List<SelectItem> getJobList() {
        return ADFUtils.selectItemsForIterator("JobsVOIterator", "JobId",
                                               "JobTitle");
    }

The menu system above the table let us choose from more filtering options. We can use the select time interval options to display all employees hired today/this week /this month or this year. We may also set the option whether we are searching for salaries equal to, less than or greater than the keyword provided above the salary column. As implementation the action listeners of these commandMenuItems will calculate and set properly the bind variables and then invoke the doQuery method presented above.

<af:commandMenuItem text="This week" id="cmi2"
                                        actionListener="#{columnFilters.setDateThisWeek}"></af:commandMenuItem>

    public void setDateThisWeek(ActionEvent actionEvent) {
        setStartDateKwd(DateUtils.getThisWeekStart());
        setEndDateKwd(DateUtils.getThisWeekEnd());
        doQuery();
    }

Finally I will present the “Reset show options” button functionality that resets all keywords, show options and then invokes the doQuery method.

    public void resetShowOptions(ActionEvent actionEvent) {
        RichInputDate sdKwd =
            (RichInputDate)JSFUtils.findComponentInRoot("sdKwd");
        sdKwd.resetValue();
        RichInputText fnKwd =
            (RichInputText)JSFUtils.findComponentInRoot("fnKwd");
        fnKwd.resetValue();
        RichInputText lnKwd =
            (RichInputText)JSFUtils.findComponentInRoot("lnKwd");
        lnKwd.resetValue();
        RichInputText phKwd =
            (RichInputText)JSFUtils.findComponentInRoot("phKwd");
        phKwd.resetValue();
        RichSelectOneChoice jiKwd =
            (RichSelectOneChoice)JSFUtils.findComponentInRoot("jiKwd");
        jiKwd.resetValue();
        RichInputText dnKwd =
            (RichInputText)JSFUtils.findComponentInRoot("dnKwd");
        dnKwd.resetValue();
        RichInputText slKwd =
            (RichInputText)JSFUtils.findComponentInRoot("slKwd");
        slKwd.resetValue();

        resetKwds();

        doQuery();
    }

    private void resetKwds() {
        setStartDateKwd(null);
        setEndDateKwd(null);
        setDateKwd(null);
        setStartSalaryKwd(null);
        setEndSalaryKwd(null);
        setFirstNameKwd(null);
        setLastNameKwd(null);
        setJobIdKwd(null);
        setDepartmentNameKwd(null);
        setPhoneNOKwd(null);
        setSalaryKwd(null);
        setSalaryOption(SALARY_OPTION.EQUALS);
    }

To download the sample application go to the following link: ColumnFiltersApp.zip