Database look-ups and XQuery Transformations

Sample Number





This sample demonstrates the usage of UltraESB to lookup databases and do XQuery transformations

Use Case

I want to look-up values from a database and enrich the message coming in with an XQuery transformation, to respond back to the client. I also want to use the ETag and process the messages as per the ETag of the in coming messages.

XQuery support could be enabled by placing the Saxon-HE 9.7.0-1 jar and Saxon-HE-xqj 9.7.0-1 Jar file on the classpath - e.g. at lib/custom directory.

Sample Configuration

The configuration uses a Java class sequence for easier debugging that may help one to develop a configuration when starting out. For complex and rather static (the logic doesn’t change over time frequently) Java based sequences it is recommended that you keep the sequence as a Java class sequence for manageability.

Sample configuration with datasource and XQery support

 1<u:proxy id="subscription-mock">
 2    <u:transport id="http-8280"/>
 3    <u:target>
 4        <u:inSequence>
 5            <u:class name=""/>
 6        </u:inSequence>
 7    </u:target>
10<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
11    <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/>
12    <property name="url" value="jdbc:derby://localhost:1529/../modules/sample/target/unittestdb"/>
13    <property name="username" value="admin"/>
14    <property name="password" value="admin"/>
17<bean id="xq" class="org.adroitlogic.ultraesb.core.helper.XQuerySupportImpl">
18    <constructor-arg ref="fileCache"/>

The unit test expects a "SUBSCRIPTION" table to be available from the Database, and for a GET request with a "If-None-Match" header (i.e. ETag), checks the latest known ETag from the table. If the client tag matches the value on the database, a HTTP 304 - Not Modified response is returned. If the ETags mismatch, or was not sent by the client, a Spring SimpleJdbcTemplate is used to query the SUBSCRIPTION table, and extract the matching record as a Domain object "Subscription".

To invoke the XQuery samples/resources/sample1.xq defined as follows, we now move the expected variables into a Map from the subscription domain object, and invoke the XQuery.

declare variable $id as xs:string external;
declare variable $startdate as xs:string external;
declare variable $enddate as xs:string external;
declare variable $category as xs:int external;
declare variable $username as xs:string external;

<m:subscription xmlns:m="http://mock.samples">

The transformMessage() method expects a Message as a parameter, and writes the transformation output - expected as an XML document - as its payload

 1public class XQueryMocker implements JavaClassSequence {
 3    private final ParameterizedRowMapper<Subscription> mapper = new ParameterizedRowMapper<Subscription>() {
 4        public Subscription mapRow(ResultSet rs, int rowNum) throws SQLException {
 5            Subscription sub = new Subscription();
 6   = rs.getString("id");
 7            sub.startdate = rs.getTimestamp("startdate");
 8            sub.enddate = rs.getTimestamp("enddate");
 9            sub.category = rs.getInt("category");
10            sub.username = rs.getString("username");
11            sub.etag = rs.getString("etag");
12            return sub;
13        }
14    };
16    public void init(Configuration config) {
17        // any setup stuff
18    }
20    public void destroy() {
21        // any completion stuff
22    }
24    public void execute(Message msg, Mediation mediation) throws Exception {
26        Message res = msg.createDefaultResponseMessage();
27        String method = (String) msg.getMessageProperty(HttpConstants.METHOD);
29        if ("GET".equals(method)) {
31            // get etag from request, and query parameters
32            String clientETag = msg.getFirstTransportHeader("If-None-Match");
33            @SuppressWarnings("unchecked")
34            Map<String, String> params = (Map<String, String>) msg.getMessageProperty(HttpConstants.QUERY_PARAM_MAP);
36            if (clientETag != null) {
37                SimpleJdbcTemplate t = new SimpleJdbcTemplate(mediation.getDataSource("dataSource"));
38                String serverETag = t.queryForObject("SELECT ETAG FROM SUBSCRIPTION WHERE ID = ?", String.class, new Object[]{params.get("id")});
40                if (clientETag.equals(serverETag)) {
41                    mediation.sendResponse(msg, 304);
42                    return;
43                }
44            }
46            // if client did not send a if-none-match header, or if the etag sent did not match the latest on DB
47            SimpleJdbcTemplate t = new SimpleJdbcTemplate(mediation.getDataSource("dataSource"));
48            Subscription sub = t.queryForObject("SELECT * FROM SUBSCRIPTION WHERE ID = ?", mapper, params.get("id"));
49            Map<String, Object> vars = new HashMap<String, Object>();
50            if (sub != null) {
51                vars.put("id",;
52                vars.put("startdate", sub.startdate.toString());
53                vars.put("enddate", sub.enddate.toString());
54                vars.put("category", sub.category);
55                vars.put("username", sub.username);
56            }
58            XQuerySupport xq = mediation.getSpringBean("xq", XQuerySupport.class);
59            xq.transformMessage(res, "samples/resources/sample1.xq", vars);
60            if (sub != null) {
61                res.addTransportHeader("ETag", sub.etag);
62            }
63            mediation.sendResponse(res, 200);
65        } else if ("POST".equals(method)) {
67            XQuerySupport xq = mediation.getSpringBean("xq", XQuerySupport.class);
69            Map<String, Object> vars = new HashMap<String, Object>();
71            if (msg.getCurrentPayload() instanceof DOMMessage) {
72                vars.put("payload", (((DOMMessage) msg.getCurrentPayload()).getDocument()));
73            } else {
74                DOMMessage domMessage = new DOMMessage(msg.getCurrentPayload());
75                vars.put("payload", domMessage.getDocument());
76            }
78            String[][] ns = {{"m0", "http://mock.samples/"}};
79            String paid = mediation.extractAsStringUsingXPath(msg, "//m0:updateSubscription/m0:amountPaid", ns);
80            vars.put("balance", Double.valueOf(paid) + 1550.33);
82            xq.transformMessage(res, "samples/resources/sample2.xq", vars);
83            mediation.sendResponse(res, 200);
84        }
85    }
87    static class Subscription {
88        String id;
89        Date startdate;
90        Date enddate;
91        int category;
92        String username;
93        String etag;
94    }

We also show a sample POST request, where we transform the request payload into a suitable response by extracting request elements using XPath expressions within the XQuery definition samples/resources/sample2.xq as follows:

declare namespace m0="http://mock.samples/";
declare variable $payload as document-node() external;
declare variable $balance as xs:double external;

<m:confirmation xmlns:m="http://mock.samples">

Using XQuery would be one of the easiest ways to transform XML request payloads to response XML payloads, although XSLT, Velocity or any scripting languages or custom libraries or code may also be used. Note that we pass the request payload as the variable "$payload" to the above XQuery, and a hypothetical "balance" value as a Double variable.

Running the example

As this requires setting up a sample database, one could refer to the source code of the test case found under samples/src directory in the distribution, and execute it within your favorite IDE.

Note: Be sure to download Saxon-HE 9.7.0-1 jar and Saxon-HE-xqj 9.7.0-1 Jar, and place them under lib/custom before you run the example. You may need to update the Eclipse/NetBeans projects to detect this new Jar.

A GET request for http://localhost:8280/service/subscription-mock?id=12345 with a valiv ETag header (i.e. "If-None-Match: 686897696a7c876b7e") will return a HTTP 304 response, and a request for the second sample subscription value http://localhost:8280/service/subscription-mock?id=67890 will return a full response generated via the XQuery.

A POST request for updateSubscription XML payload will return the XQuery output as the response with some of the elements filled from the request elements, and hypothetical values.

In this topic
In this topic
Contact Us