Samples - 6.3

Talend ESB Mediation Developer Guide

EnrichVersion
6.3
EnrichProdName
Talend Data Fabric
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for ESB
Talend Real-Time Big Data Platform
task
Design and Development
EnrichPlatform
Talend ESB

In the following example, we fetch the rows from the customer table.

First we register our datasource in the Camel registry as testdb :

JndiRegistry reg = super.createRegistry();
reg.bind("testdb", ds);
return reg;

Then we configure a route that routes to the JDBC component, so the SQL will be executed. Note how we refer to the testdb datasource that was bound in the previous step:

// let's add a simple route
public void configure() throws Exception {
   from("direct:hello").to("jdbc:testdb?readSize=100");
}

Or you can create a DataSource in Spring like this:

<camelContext id="camel" xmlns="http://camel.apache.org/schema/spring">
   <route>
      <from uri="timer://kickoff?period=10000"/>
      <setBody>
         <constant>select * from customer</constant>
      </setBody>
      <to uri="jdbc:testdb"/>
      <to uri="mock:result"/>
   </route>
</camelContext>

<!-- Just add a demo to show how to 
     bind a date source for Camel in Spring-->
<bean id="testdb" 
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
   <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
   <property name="url" value="jdbc:hsqldb:mem:camel_jdbc" />
   <property name="username" value="sa" />
   <property name="password" value="" />
</bean>

We create an endpoint, add the SQL query to the body of the IN message, and then send the exchange. The result of the query is returned in the OUT body:

// first we create our exchange using the endpoint
Endpoint endpoint = context.getEndpoint("direct:hello");
Exchange exchange = endpoint.createExchange();
// then we set the SQL on the in body
exchange.getIn().setBody("select * from customer order by ID");

// now we send the exchange to the endpoint, and receive Camel response
Exchange out = template.send(endpoint, exchange);

// assertions of the response
assertNotNull(out);
assertNotNull(out.getOut());
ArrayList<HashMap<String, Object>> data = out.getOut().getBody(
   ArrayList.class);
assertNotNull("out body could not be converted to an ArrayList - was: "
   + out.getOut().getBody(), data);
assertEquals(2, data.size());
HashMap<String, Object> row = data.get(0);
assertEquals("cust1", row.get("ID"));
assertEquals("jbloggs", row.get("NAME"));
row = data.get(1);
assertEquals("cust2", row.get("ID"));
assertEquals("nsandhu", row.get("NAME"));

If you want to work on the rows one by one instead of the entire ResultSet at once you need to use the Splitter EIP such as:

In Camel 2.13.x or older

from("direct:hello")
   // here we split the data from the testdb into new messages 
   // one by one so the mock endpoint will receive a message
   // per row in the table 
   .to("jdbc:testdb").split(body()).to("mock:result");

In Camel 2.14.x or newer

from("direct:hello")
// here we split the data from the testdb into new messages one by one
// so the mock endpoint will receive a message per row in the table
// the StreamList option allows to stream the result of the query without creating a List of rows
// and notice we also enable streaming mode on the splitter
.to("jdbc:testdb?outputType=StreamList")
  .split(body()).streaming()
  .to("mock:result");