Sample - 6.3

Talend ESB Mediation Developer Guide

Talend Data Fabric
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for ESB
Talend Real-Time Big Data Platform
Design and Development
Talend ESB

In the sample below we execute a query and retrieve the result as a List of rows, where each row is a Map<String, Object and the key is the column name.

First, we set up a table to use for our sample. As this is based on an unit test, we'll do it using java code:

// this is the database we create with some initial data for our unit test
jdbcTemplate.execute("create table projects (id integer primary key,"
   + "project varchar(10), license varchar(5))");
jdbcTemplate.execute("insert into projects values (1, 'Camel', 'ASF')");
jdbcTemplate.execute("insert into projects values (2, 'AMQ', 'ASF')");
jdbcTemplate.execute("insert into projects values (3, 'Linux', 'XXX')");

Then we configure our route and our sql component. Notice that we use a direct endpoint in front of the sql endpoint. This allows us to send an exchange to the direct endpoint with the URI, direct:simple, which is much easier for the client to use than the long sql: URI. Note that the DataSource is looked up up in the registry, so we can use standard Spring XML to configure our DataSource.

   .to("sql:select * from projects where license=# order by id?

And then we fire the message into the direct endpoint that will route it to our sql component that queries the database.

MockEndpoint mock = getMockEndpoint("mock:result");
// send the query to direct that will route it to the sql where we will 
// execute the query and bind the parameters with the data from the body. 
// The body only contains one value in this case (XXX) but if we should 
// use multiple values then the body will be iterated so we could supply 
// a List<String> instead containing each binding value.
template.sendBody("direct:simple", "XXX");


// the result is a List
List received = assertIsInstanceOf(
   List.class, mock.getReceivedExchanges().get(0).getIn().getBody());

// and each row in the list is a Map
Map row = assertIsInstanceOf(Map.class, received.get(0));

// and we should be able the get the project 
// from the map that should be Linux
assertEquals("Linux", row.get("PROJECT"));

We could configure the DataSource in Spring XML as follows:

<jee:jndi-lookup id="myDS" jndi-name="jdbc/myDataSource"/>