JDeveloper 11g R1: Advanced Multi Column Table Sort

A question on the JDeveloper and ADF Community Space found my attention. A user asked how to sort an af:table after more then one column.
Well, there is the official way, which Frank Nimphius’s bloged about in ‘Declarative multi-column sort for ADF bound tables’.
However this declarative approach needs the user to select the columns and their sort order. In most cases the sort after a second column is driven by the use case specification. A sample would be that the departments tables should normally be sorted after the column selected by the user, but then the data should always be sorted by the department name inside the first sort.
The image below shows the Departments table sorted first after the LocationId and inside the LocationId sorted by the DepartmentName.

Departments sorted after LocationID and DepartmentName

Departments sorted after LocationID and DepartmentName

Now lets see how to implement this. There are some possible solutions:

  1. add a sort criterion in a managed bean
  2. add a sort Criterion in the ViewObject
  3. a combination of 1) and 2)

All solutions have their advantages and disadvantages. Let’s start with the managed bean approach. This is pretty simple as we only need to add sortListener to the af:table which is pointing a bean method. In the sample below we are using the departments table where we wire up the secondary sort to the DepartmentName column.

<af:table value="#{bindings.DepartmentsView.collectionModel}" var="row" rows="#{bindings.DepartmentsView.rangeSize}"
...
    sortListener="#{DepartmentSearchBean.sortTableListener}">
...

And the sortTableListener in the bean

    public void sortTableListener(SortEvent sortEvent) {
        //log the selected column (just for information)
        List<SortCriterion> criteria = sortEvent.getSortCriteria();
        for (SortCriterion sc : criteria) {
            logger.info("Sort after: " + sc.getProperty());
        }
        // Create new SortCriterion for DepartmentName in ascending order
        SortCriterion scNew = new SortCriterion("DepartmentName", true);
        // Add it to the list
        criteria.add(scNew);
        // and apply it back to the table
        Object object = sortEvent.getSource();
        RichTable table = (RichTable) object;
        table.setSortCriteria(criteria);
        logger.info("----------------------END----------------------");
    }

That’s all we need to do to get the output from the first image. You’ll notice, that both columns are showing the sort icon. Only the one for the DepartmentName can’t change to descending order as we wired things up to always sort in ascending order. From the users point of view this can be disturbing as it’s not obvious why this happens.

For the second solution we use the model layer instead of the view layer. Here we implement the ViewObjectImpl class of the EmployeesView and overwrite the setOrderByOrSortBy(…) method. This is the method the framework calls when you click on a header on the table to sort it.
Now we can hard wire the secondary sort column, as we did in the managed bean. However, let’s think about how to make this more flexible. A nice add on is that we can use the custom properties of each table attribute to define the secondary sort column. This way we can decide which columns to sort after for each of the attributes available. We can even decide to add more then one column for secondary and third sort.

The overwritten setOrderByOrSortBy method looks for the custom property named ‘SECONDARY_SORT’ and if found, creates a new SortCriterion with the column name give in the custom property. This new sort criterion is then added to the list of SortCriteria.

    @Override
    public String setOrderByOrSortBy(SortCriteria[] sortCriteria) {
        SortCriteriaImpl scNew = null;
        // iterate current sort criteria
        for (int i = 0; i < sortCriteria.length; i++) {
            logger.info("Sort: " + sortCriteria[i].getAttributeName());
            // check for SECONDARY_SORT propertie on each attribute
            int attributeIndexOf = this.getAttributeIndexOf(sortCriteria[i].getAttributeName());
            AttributeDef attributeDef = this.getAttributeDef(attributeIndexOf);
            Object object = attributeDef.getProperty("SECONDARY_SORT");
            if (object != null) {
                logger.info("Secondary sort:" + object.toString());
                scNew = new SortCriteriaImpl(object.toString(), false);
            }
        }

        if (scNew != null) {
            // Create a new array for the added criteria
            SortCriteria scNewArray[] = new SortCriteria[sortCriteria.length + 1];
            for (int j = 0; j < sortCriteria.length; j++) {
                scNewArray[j] = sortCriteria[j];
            }

            // add the new criteria
            scNewArray[sortCriteria.length] = scNew;
            //and exceute the search
            return super.setOrderByOrSortBy(scNewArray);
        }
        
        return super.setOrderByOrSortBy(sortCriteria);
    }

The image blow shows the result for the employees table which is first sorted after the ManagerId and then after the FirstName of the employee.

Sort after ManagerId and LastName

Sort after ManagerId and LastName

As you see, only the ManagerId column shows the sort icon. The secondary sort column, FirstName, doesn’t show the sort icon.

You can download the sample application, which uses the HR DB schema from the ADF-EMG ADF Samples Repository: BlogAdvancedTableSort.zip

JDeveloper: Fitler Table on Transient Column

This blog entry is based on a question on the JDeveloper and ADF OTN forum. The use case is that a entity (EO) based view object (VO) is shown as a editable table on a page. One column should show a checkbox which is used to select rows on the VO. On an user action, a button pressed in this sample, the selected rows should be displayed in an other table (read only in this case).

We build this use case using JDeveloper 11gR2 (11.1.2.2.0) using the HR schema. As we only want to show how to solve this use case, we only need the ‘Countries’ table. The sample can be downloaded using the link provided at the end of this blog.

There are two problems to solve here:

  1. showing a checkbox to select rows
  2. filter the second table to only show the marked rows

The solution for the first quest is outlined in ADF Code Corner article 99. Multi Table Row Selection for Deferred Delete by Frank Nimphius. We use a transient attribute on the Countries EO, making sure that the transient radio button is selected

Adding a Transient Attribute

Adding a Transient Attribute

We name the new attribute ‘Selected’ and choose Boolean as type for it. Now open the Counties VO and also add an attribute, this time from the EO

Add Attribute to VO from EO

Add Attribute to VO from EO

New Attribute 'Selected' in VO

New Attribute ‘Selected’ in VO

Now open the UI Hints tab and select ‘Check Box’ as ‘Control Type’. You can also add a label to be used for the attribute in the ui.

Set UI Hints

Set UI Hints

This concludes the first quest in the model layer. The second problem is to only show those rows in a second table which are marked (check box selected) in the first table. For the selection we added a new transient attribute ‘Selected’. This boolean attribute we now use to create a view criteria (VC) to filter the row set.
Open the VO CountrieView and select the ‘Query’ node. Add a VC using the green plus sign, name the VC ‘CountriesSelectedVC’ and select the transient ‘Selected’ attribute to equal ‘true’, which is the value the check box get if it’s marked. It’s essential that we set the ‘Query Execution Mode’ to ‘In Memory’ as the VC uses transient data. The framework give you a warning if you don’t obey this rule.

ViewCriteria to Filter Selected Rows

ViewCriteria to Filter Selected Rows

Now that we are able to filter all rows which have the ‘Selected’ attribute set, we have to set up the application modules data model. We use the CountriesView VO to show all rows including the check box to select some or all rows and use the same CountriesView VO, now with the VC applied, to show only the marked rows.
For this we add the CountriesView VO once to the data model as ‘CountriesView1’ and then add the same VO again to the data model as ‘CountriesViewSelected’. To apply the VC ‘CountriesSelectedVC’we defined earlier, we select the ‘Edit’ button in the top right corner of the data model. In the dialog we select the CountriesSelectedVC VC and shuffle it to the selected side. This adds the defined VC as where clause to the VO.

Add VC to VO

Add VC to VO

If you run the model project in the application module tester you’ll see that it works as expected.

The final task is to set up the UI. We use a single page and put a splitter onto a panel stretch layout. The top part shows the editable table with the check box, the lower splitter facet the row which are selected in the upper table. The image below shows the running application

Final Application after Start

Final Application after Start

Select some rows

Some Rows are Selected

Some Rows are Selected

After selecting some rows by marking the check box we need to issue an action to see the result. Click the ‘refresh’ button which is bound to the ‘execute’ method of the CountriesViewSelected view from the Data Control. All left to do is to set up a partial trigger in the table showing the selected records pointing to the ‘refresh’ button.

<af:table value="#{bindings.CountriesViewSelected.collectionModel}" var="row"
          rows="#{bindings.CountriesViewSelected.rangeSize}"
          emptyText="#{bindings.CountriesViewSelected.viewable ? 'No data to display.' : 'Access Denied.'}"
          fetchSize="#{bindings.CountriesViewSelected.rangeSize}" rowBandingInterval="0"
          id="t2" partialTriggers="::ctb1">

Now the final result look like

Final Application

Final Application

You can download the sample workspace, build with JDeveloper 11.1.2.2.0 and depending on the HR db schema, from here: BlogFilterTableOnColumn.zip.doc
Please rename the file to ‘.zip’ after downloading it!

I set up a second version of the sample which uses the toolbar button set to partial submit. At first it looks like the button in the toolbar doesn’t work as there is no change in the selected table section. The problem is that the check box which selects the rows doesn’t auto submit the values. So the query executed by the toolbar button only sees the old marks. If you set the selectBooleanCheckbox autoSubmit property to true it works as expected.
The new version of the sample can be loaded here BlogFilterTableOnColumnV2.zip

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.

Pimp up an af:query to show the result table in an af:panelCollection

A user on the JDev Forum asked an interesting question on how to show the result of an af:query (e.g. dropped as ‘Query Panel with Table’).
A quick check showed that dropping a named criteria as ‘Query Panel with Table’ produces af:panelGroupLayout containing a af:panelHeader for the af:query component and an af:table for the result of the query.
Here is a sample of a drop as ‘Query Panel with Table’ (some details are omitted to save space):

                    <af:panelGroupLayout layout="vertical" id="pgl1">
                        <af:panelHeader text="Employees" id="ph1">
                            <af:query id="qryId1" headerText="Search" disclosed="true"...
                                      resultComponentId="::resId1"/>
                        </af:panelHeader>
                        <af:table id="resId1" value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                  rows="#{bindings.EmployeesView1.rangeSize}" ...>
                            <af:column ...>
                            </af:column>
                            <af:column ...>
                            </af:column>
                            ...
                        </af:table>
                    </af:panelGroupLayout>

The user asked for functionality like hiding columns or detachment of the table to get a full screen mode. In short he wanted to use the functions of a af:panelCollection together with the automatic setup of af:query and the result table.

This can easily be done by surrounding the af:table component by a af:panelCollection and rewiring the ‘resultComponentId’ property of the af:query, as the af:panelCollection is a naming container.
In JDev select the table with a right click and select ‘Surround with…’

Surround Table with panel collection

and search for the ‘Panel Collection’

Select 'Panel Collection'

and click ‘OK’ to finish this step.
Now we need to change the ‘resultComponentId’ property of the af:query component to account for new the naming container which is added through the af:panelCollection component.
Select the af:query in the design view or in the structure window and open the property inspectors common tab

Change af:query
As you can see JDev shows an error for the ‘resultComponentId’ property as the target has changes its naming container. To change it click the small arrow down at the right hand side of the property, select ‘Edit…’. In the next Dialog look for the af:table component inside the af:panelCollection and select it. Click ‘OK’ to finish the change.
the resulting code looks like (some details are omitted to save space):

                    <af:panelGroupLayout layout="vertical" id="pgl1">
                        <af:panelHeader text="Employees" id="ph1">
                            <af:query id="qryId1" headerText="Search" disclosed="true"
                                      ...
                                      resultComponentId="::pc1:resId1"/>
                        </af:panelHeader>
                        <af:panelCollection id="pc1">
                            <f:facet name="menus"/>
                            <f:facet name="toolbar"/>
                            <f:facet name="statusbar"/>
                            <af:table value="#{bindings.EmployeesView1.collectionModel}" var="row"
                                      ...
                                      rowSelection="single" id="resId1">
                                <af:column sortProperty="#{bindings.EmployeesView1.hints.EmployeeId.name}"
                                           ...
                                </af:column>
                                ...
                                <af:column sortProperty="#{bindings.EmployeesView1.hints.PhoneNumber.name}"
                                ...
                                </af:column>
                            </af:table>
                        </af:panelCollection>
                    </af:panelGroupLayout>

When you run the code you are able to detach the table and hide columns

Resulting page