Integration Gateway: Understanding REST data source [15]: $orderby

[CarlosRoggan]

This  blog is about Integration Gateway (IGW) in SAP Mobile Platform 3.0 (SMP).

It shows how to implement the $orderby capability in an OData service based on Integration Gateway.

In my previous Blog, I’ve already described how to implement the system query option $top.

Everything described there, motivation, prerequisites,  preparation etc, is valid here as well.

So let us skip all introductory text and go directly to the implementation.

Implement $orderby

Background

When requesting a list of entities from a service, it is up to the service implementation to decide in which order they are presented. This can depend on the backend data source - anyways, it is undefined.

But the user of an OData service might want to be able to specify the order, according to his needs.

For example, a usual case would be that the list of entities is sorted as per default by its ID number, but for a user, the ID is not relevant and he would prefer a sorting e.g. by the name

OData supports this requirement with the system query option $orderby

It is specified as follows:

$orderby=<propertyName>

The order can be ascending or descending:

$orderby=<propertyName> asc

$orderby=<propertyName> desc

If not specified, the default is ascending.

See here for more details (section 4.2.)

Note:

As of the OData specification, the $orderby system query option can be applied to multiple properties.

In that case, the value is specified as comma-separated list.

Example:

https://localhost:8083/gateway/odata/SAP/<yourService>/Companies?$orderby=COUNTRY desc, NAME asc

In this example, all companies should be displayed, and they should be sorted by their country in descending order. Additionally, within each country, they should be sorted by their name in ascending order.

In order to support such complex sorting, the OData service implementation has to make use of the ExpressionVisitor concept.

We aren't using it in the present blog, because the ExpressionVisitor is explained in the $filter blog

Implementation

Just like in the previous blog we get the data from the backend REST service and we modify it such that it has the xml structure that is expected by the Integration Gateway framework.

In order to modify the structure, we parse the xml and create a Document object.

This instance of Document is used to apply the system query options in the present blog.

After refining the Document (apply system query options), it is converted back to a string, which is then set as body to the Message object.

The step of applying the $orderby can be divided into the following sub-steps:

1. Analyze the URI, retrieve the value of the $orderby

2. Convert the list of DOM nodes into a java.util.ArrayList

3. Do the sorting on the ArrayList

4. Convert the ArrayList back to DOM nodes

Note:

This approach seems easier to me to showcase the $orderby. Of course, one could think also of other approaches, that would be e.g. more performant

Step 1: The URI

What we intend to achieve in this section is to get the name of the property (which should be used for sorting) from the URI.

Example:

We have a list of companies and we want to sort it by the country.

The URL to be invoked:

https://localhost:8083/gateway/odata/SAP/<yourService>/Companies?$orderby=COUNTRY

The name of the property that we need: COUNTRY

Possible values to be sorted: DE, IN, US, etc

The system query option $orderby is more powerful than the query options covered in the previous blogs.

So here we need a few more lines of code to get the desired information.

First, as usual, obtain the OrderByExpression instance from the UriInfo

Check if it is null.

Then ask it for its orders.

UriInfo uriInfo = (UriInfo) message.getHeaders().get("UriInfo");

OrderByExpression orderByExpression = uriInfo.getOrderBy();

…

List orders = orderByExpression.getOrders();

Here we get a java.util.List, because – as mentioned above, the $orderby expression can be formed by multiple properties.

We don’t need to check if the list is null or empty, because this is checked by the OData library.

So we directly access the first entry of the list.

As mentioned above, we’re keeping this tutorial simple, so we don’t support sorting by more than one property.

OrderExpression orderExpression = orders.get(0);

We need a few more lines to retrieve the property name

    CommonExpression commonExpression = orderExpression.getExpression();

    String sortProperyName = ((PropertyExpression)commonExpression).getPropertyName();

Step 2: The list to be sorted

The decision to be taken here is: Do we want to sort manually, or do we want to use Collections.sort() ?

The second option is easier, but the disadvantage is that it can be applied only to an instance of java.util.List

But what we have is an xml DOM, an instance of org.w3c.dom.NodeList with child nodes

So we have to convert the NodeList into an ArrayList

We have to move all the nodes of our xml DOM into the ArrayList.

    //For using standard sorter, we need an ArrayList instead of NodeList

    List<Element> companyList = new ArrayList<Element>();

    // move all company nodes into the ArrayList

    Node entitySetNode = document.getFirstChild();

    NodeList companiesNodeList = entitySetNode.getChildNodes();

    for(int i = 0; i < companiesNodeList.getLength(); i++){

        Node companyNode = companiesNodeList.item(i);

        if(companyNode.getNodeType() == Node.ELEMENT_NODE){

            Element companyElement = (Element)companyNode;

            // populate the ArrayList

            companyList.add(companyElement);

        }

    }

Step 3: The sorting

Now we have an ArrayList containing the xml nodes which contain the data.

Based on that, we can implement a java.util.Comparator

This comparator will be then be used by the Collections.sort() method.

What does our comparator have to do?

It has to compare the values of a given property.

Example:

If the following URL is invoked:

https://localhost:8083/gateway/odata/SAP/<yourService>/Companies?$orderby=COUNTRY

then all the values of the property COUNTRY have to be compared.

For example, our comparator will have to compare DE with IN and with US, etc

Our ArrayList that has to be sorted, contains instances of (company-) Node, so we have to extract the desired property from it, and then read the value of the property.

Note: in our example, we can cast the org.w3c.dom.Node instance to an org.w3c.dom.Element, which makes it easier to handle.

Since we’re comparing strings, we can delegate the actual comparison to the String class

    String value1 = element1.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

    String value2 = element2.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

    // we delegate the sorting to the native sorter of the java.lang.String class

    compareResult = value1.compareTo(value2);

There’s one more thing to consider here:

In our model, all properties are of type Edm.String

I don’t want to discuss here if it is meaningful to assign type Edm.String instead of Edm.int32 to the ID property;-)

In our comparator, we just treat the ID property differently.

If we would let the native String comparator do the compare job for numbers, we would get wrong results:

For a String-comparator the “5” is higher than e.g. “46”, which for numbers is wrong.

So in case of property ID, we convert the String to an Integer and let the Integer class do the comparison.

                if(sortProperyName.equals("ID")){

                    Integer integerValue1 = new Integer(value1);

                    Integer integerValue2 = new Integer(value2);              

                    // we delegate the sorting to the native sorter of the Integer class

                    compareResult = integerValue1.compareTo(integerValue2);

               }

One last implementation to be done:

OData supports sorting in ascending or descending way.

We have to get this information from the orderExpression and evaluate it.

Depending on the value, the result has to be reverse.

Our comparator has to return a positive int if the first value is bigger than the second.

So if the sort order is descending, we just convert the positive value into a negative value.

                        // if 'desc' is specified in the URI, change the order of the list

                        SortOrder sortOrder = orderExpression.getSortOrder()

                        if (sortOrder.equals(SortOrder.desc)){

                            return - compareResult; // just convert the result to negative value to change the order

                        }

Step 4: The final DOM

So far, we now have a sorted ArrayList.

But we need our instance of org.w3c.dom.Document to have sorted child nodes.

So our next step is to move the entries of the ArrayList back to the Document.

This can be done in several ways, I’ve decided to replace the existing parent node (which contains children in wrong order) with a newly created node that contains the children in the correct order.

So we have to

1. create a new node that represents the EntitySet

    Element newEmptyEntitySetNode = document.createElement("Companies");

2. populate it with child nodes that are in the (sorted) ArrayList

    Iterator iterator = companyList.iterator();

    while (iterator.hasNext()) {

        Element companyElement = (Element)iterator.next();

        newEmptyEntitySetNode.appendChild(companyElement);

    }

3. Finally replace the old EntitySet-node with the new one

    document.replaceChild(newEmptyEntitySetNode, entitySetNode);

The complete implementation of $orderby

def Document applyOrderby(Document document, Message message){

    // check the URI for the $orderby option

    UriInfo uriInfo = (UriInfo) message.getHeaders().get("UriInfo");

    OrderByExpression orderByExpression = uriInfo.getOrderBy();

    if(orderByExpression == null){

        // if orderByExpression is null, then there's no $orderby in the URI, so do nothing

        return document;

    }

    List orders = orderByExpression.getOrders(); // for multiple properties for ordering

    OrderExpression orderExpression = orders.get(0); // simple implementation. Better: use ExpressionVisitor

    CommonExpression commonExpression = orderExpression.getExpression();

    if(! (commonExpression instanceof PropertyExpression)){

        return document;

    }

    // the name of the property, which should be used for sorting (e.g. sort by company name)

    String sortProperyName = ((PropertyExpression)commonExpression).getPropertyName();

    //For using standard sorter, we need an ArrayList instead of NodeList

    List<Element> companyList = new ArrayList<Element>();

    // move all company nodes into the ArrayList

    Node entitySetNode = document.getFirstChild();

    NodeList companiesNodeList = entitySetNode.getChildNodes();

    for(int i = 0; i < companiesNodeList.getLength(); i++){

        Node companyNode = companiesNodeList.item(i);

        if(companyNode.getNodeType() == Node.ELEMENT_NODE){

            Element companyElement = (Element)companyNode;

            // populate the ArrayList

            companyList.add(companyElement);

        }

    }

    // now do the sorting for the ArrayList

    Collections.sort(companyList, new Comparator<Element>(){

        public int compare( Element element1,  Element element2) {

            int compareResult = 0;

       

            String value1 = element1.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

            String value2 = element2.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

            // we need special treatment for the ID, which is of type string, but for correct order we need the number

            if(sortProperyName.equals("ID")){

                Integer integerValue1 = new Integer(value1);

                Integer integerValue2 = new Integer(value2);

           

                // we delegate the sorting to the native sorter of the Integer class

                compareResult = integerValue1.compareTo(integerValue2);

            }else{

                // we delegate the sorting to the native sorter of the java.lang.String class

                compareResult = value1.compareTo(value2);

            }

            // if 'desc' is specified in the URI, change the order of the list

            SortOrder sortOrder = orderExpression.getSortOrder();

            if (sortOrder.equals(SortOrder.desc)){

                return - compareResult; // just convert the result to negative value to change the order

            }

       

            return compareResult;

        }

    });

    // now that the sorting is done, convert the (sorted) ArrayList back to NodeList

    // first create a new entitySet node

    Element newEmptyEntitySetNode = document.createElement("Companies");

    // then add all child nodes

    Iterator iterator = companyList.iterator();

    while (iterator.hasNext()) {

        Element companyElement = (Element)iterator.next();

        newEmptyEntitySetNode.appendChild(companyElement);

    }

    // then replace the old entitySet node with the new node (containing the sorted children)

    document.replaceChild(newEmptyEntitySetNode, entitySetNode);

    //Finally, return the document with sorted child nodes

    return document;

}

The complete custom script

The following sample code contains the full content of the custom script (Groovy).

The content is the same as the script file attached to this blog.

import java.nio.charset.StandardCharsets

import javax.xml.parsers.DocumentBuilder

import javax.xml.parsers.DocumentBuilderFactory

import javax.xml.parsers.ParserConfigurationException

import javax.xml.transform.OutputKeys

import javax.xml.transform.Transformer

import javax.xml.transform.TransformerConfigurationException

import javax.xml.transform.TransformerException

import javax.xml.transform.TransformerFactory

import javax.xml.transform.dom.DOMSource

import javax.xml.transform.stream.StreamResult

import org.apache.olingo.odata2.api.uri.UriInfo

import org.apache.olingo.odata2.api.uri.expression.CommonExpression

import org.apache.olingo.odata2.api.uri.expression.OrderByExpression

import org.apache.olingo.odata2.api.uri.expression.OrderExpression

import org.apache.olingo.odata2.api.uri.expression.PropertyExpression

import org.apache.olingo.odata2.api.uri.expression.SortOrder;

import org.w3c.dom.Document

import org.w3c.dom.Element

import org.w3c.dom.Node

import org.w3c.dom.NodeList

import org.xml.sax.InputSource

import org.xml.sax.SAXException

import com.sap.gateway.ip.core.customdev.logging.ILogger

import com.sap.gateway.ip.core.customdev.logging.LogMessage

import com.sap.gateway.ip.core.customdev.util.Message

def Message processRequestData(message) {

    return message;

}

def Message processResponseData(message) {

    message = (Message)message;

    String bodyString = (String) message.getBody();

    /* CONVERT PAYLOAD */

    InputSource inputSource = new InputSource(new ByteArrayInputStream(bodyString.getBytes(StandardCharsets.UTF_8)));

    inputSource.setEncoding("UTF-8"); // note: this is REQUIRED, because the input has a BOM, and has encoding UTF-16, which can't be parsed

    Document document = loadXMLDoc(inputSource);

    // now do the refactoring: throw away useless nodes from backend payload

    document = refactorDocument(document);

    // handle system query options

    document = applyOrderby(document, message);

    // convert the modified DOM back to string

    String structuredXmlString = toStringWithoutProlog(document, "UTF-8");

    /* FINALLY */

    message.setBody(structuredXmlString);

    return message;

}

def Document loadXMLDoc(InputSource source) {

    DocumentBuilder parser;

    try {

        parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();

    } catch (ParserConfigurationException e) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Error: failed to create parser: ParserConfigurationException");

        return null;

    }

    // now parse

    try {

        return parser.parse(source);

    } catch (SAXException e) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Exception ocurred while parsing source: SAXException");

        return null;

    }

}

def Document refactorDocument(Document document){

    //find nodes

    Node rootElement = document.getFirstChild();

    Node asxValuesNode = rootElement.getFirstChild();

    Node proddataNode = asxValuesNode.getFirstChild();

    NodeList snwdNodeList = proddataNode.getChildNodes();

    //rename all nodes of the feed

    document.renameNode(proddataNode, proddataNode.getNamespaceURI(), "Companies");

    for(int i = 0; i < snwdNodeList.getLength(); i++){

        Node snwdNode = snwdNodeList.item(i);

        document.renameNode(snwdNode, snwdNode.getNamespaceURI(), "Company");

    }

    //replace node

    Node cloneNode = proddataNode.cloneNode(true);

    document.replaceChild(cloneNode, rootElement);

    return document;

}

/**

* Transforms the specified document into a String representation.

* Removes the xml-declaration (<?xml version="1.0" ?>)

* @param encoding should be UTF-8 in most cases

*/

def String toStringWithoutProlog(Document document, String encoding) {

    // Explicitly check this; otherwise this method returns just an XML Prolog

    if (document == null) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Error: the given document is null.");

        return null;

    }

    TransformerFactory transformerFactory = TransformerFactory.newInstance();

    Transformer t = null;

    try {

        t = transformerFactory.newTransformer();

    } catch (TransformerConfigurationException e) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Error: TransformerConfigurationException while creating Transformer...");

        return null;

    }

    t.setOutputProperty(OutputKeys.METHOD, "xml");

    t.setOutputProperty(OutputKeys.INDENT, "yes");

    t.setOutputProperty(OutputKeys.ENCODING, encoding);

    t.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");

    t.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");

    OutputStream os = new ByteArrayOutputStream();

    OutputStreamWriter osw = null;

    try {

        osw = new OutputStreamWriter(os, encoding);

    } catch (UnsupportedEncodingException e) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Error: UnsupportedEncodingException while creating OutputStreamWriter...");

        return null;

    }

    BufferedWriter bw = new BufferedWriter(osw);

    try {

        t.transform(new DOMSource(document), new StreamResult(bw));

    } catch (TransformerException e) {

        ((ILogger)log).logErrors(LogMessage.TechnicalError, "Error: TransformerException while transforming to String...");

        return null;

    }

    return os.toString();

}

def Document applyOrderby(Document document, Message message){

    // check the URI for the $orderby option

    UriInfo uriInfo = (UriInfo) message.getHeaders().get("UriInfo");

    OrderByExpression orderByExpression = uriInfo.getOrderBy();

    if(orderByExpression == null){

        // if orderByExpression is null, then there's no $orderby in the URI, so do nothing

        return document;

    }

    List orders = orderByExpression.getOrders(); // for multiple properties for ordering

    if(orders == null && orders.size() <= 0){

        return document;

    }

    OrderExpression orderExpression = orders.get(0); // simple implementation. Better: use ExpressionVisitor

    CommonExpression commonExpression = orderExpression.getExpression();

    if(! (commonExpression instanceof PropertyExpression)){

        return document;

    }

    // the name of the property, which should be used for sorting (e.g. sort by company name)

    // example: for $orderby=NAME we'll have the string NAME in the variable sortPropertyName

    String sortProperyName = ((PropertyExpression)commonExpression).getPropertyName();

    //For using standard sorter, we need an ArrayList instead of NodeList

    List<Element> companyList = new ArrayList<Element>();

    // move all company nodes into the ArrayList

    Node entitySetNode = document.getFirstChild();

    NodeList companiesNodeList = entitySetNode.getChildNodes();

    for(int i = 0; i < companiesNodeList.getLength(); i++){

        Node companyNode = companiesNodeList.item(i);

        if(companyNode.getNodeType() == Node.ELEMENT_NODE){

            Element companyElement = (Element)companyNode;

            // populate the ArrayList

            companyList.add(companyElement);

        }

    }

    // now do the sorting for the ArrayList

    Collections.sort(companyList, new Comparator<Element>(){

        public int compare( Element element1,  Element element2) {

            int compareResult = 0;

       

            String value1 = element1.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

            String value2 = element2.getElementsByTagName(sortProperyName).item(0).getFirstChild().getNodeValue();

            // we need special treatment for the ID, which is of type string, but for correct order we need the number

            // the string comparer rates 5 higher as 46

            if(sortProperyName.equals("ID")){

                Integer integerValue1 = new Integer(value1);

                Integer integerValue2 = new Integer(value2);

           

                // we delegate the sorting to the native sorter of the Integer class

                compareResult = integerValue1.compareTo(integerValue2);

            }else{

                // we delegate the sorting to the native sorter of the java.lang.String class

                compareResult = value1.compareTo(value2);

            }

            // if 'desc' is specified in the URI, change the order of the list

            SortOrder sortOrder = orderExpression.getSortOrder();

            if (sortOrder.equals(SortOrder.desc)){

                return - compareResult; // just convert the result to negative value to change the order

            }

       

            return compareResult;

        }

    });

    // now that the sorting is done, convert the (sorted) ArrayList back to NodeList

    // first create a new entitySet node

    Element newEmptyEntitySetNode = document.createElement("Companies");

    // then add all child nodes

    Iterator iterator = companyList.iterator();

    while (iterator.hasNext()) {

        Element companyElement = (Element)iterator.next();

        newEmptyEntitySetNode.appendChild(companyElement);

    }

    // then replace the old entitySet node with the new node (containing the sorted children)

    document.replaceChild(newEmptyEntitySetNode, entitySetNode);

    //Finally, return the document with sorted child nodes

    return document;

}