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

Free Event on ADF Mobile in Germany: ‘Going Mobile – auf Geschäftsanwendungen zugreifen – mit Oracle ADF Mobile’

Aside

English message follows!

Oracle hält einen kostenlosen 1 Tages Hands on Workshop zum Thema “Going Mobile – auf Geschäftsanwendungen zugreifen – mit Oracle ADF Mobile” in Frankfurt-Dreieich ab. Dies ist sicher interessant für alle ADF Mobile Entwickler, die auf dem neusten Stand der Technik bleiben Wollen. Meldet Euch an, die Teilnehmerzahl ist begrenzt.
Weitere Informationen findet man unter den im Quote angegebenen Links.

START English Message
Oracle holds a free ADF Mobile 1 day Hands-On workshop in Frankfurt, Germany on 2nd, June 2014. If you like to learn about ‘Going Mobile – auf Geschäftsanwendungen zugreifen – mit Oracle ADF Mobile‘ the event is for you. Please register as soon as possible as the number of attendees is restricted.

The event is held in German language!
END English Message

Going Mobile – auf Geschäftsanwendungen zugreifen – mit Oracle ADF Mobile – 1 Tages Hands on Workshop
Immer häufiger gibt es die Anforderungen aus den Fachabteilungen wie Vertrieb oder Logistik, auch vom Tablet oder Smartphone auf existierende Anwendungen wie CRM oder ERP zugreifen zu wollen.
Mittels Oracle ADF Mobile können Sie dies schnell umsetzen. Entwickler mit Java oder ADF Kentnissen können ihr Wissen sofort einbringen.
Aber auch Entwickler ohne Programmierkenntnisse können Oracle Mobile sofort produktiv nutzen.
In diesem Hands-On Workshop erlernen Sie das Basiswissen zum Umgang mit den Oracle Technologien Oracle ADF Mobile sowie dem JDeveloper und Weblogic Server. Sie werden im Workshop eine mobile Anwendung entwickeln und auf Ihrem Smartphone oder Tablet (Android, iOS) ablauffähig installieren.
Mehr zu Oracle Mobile auf dieser Seite.

Zeit: 2. Juli 2014 – Beginn 10:00h – Ende: 17:00h
Ort der Veranstaltung:
Frankfurt-Dreiech
Oracle Deutschland B.V. & Co KG
Robert-Bosch-Straße 5
63303 Dreieich
Anmeldelink hier. Die Teilnahme ist kostenlos. Die Teilnehmerzahl ist begrenzt.
Systemvoraussetzungen für den Workshop finden Sie hier.