[CONF] Apache Camel: JDBC (page edited)

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

[CONF] Apache Camel: JDBC (page edited)

Dhiraj Bokde (Confluence)

JDBC has been edited by willem jiang (Jan 15, 2009).

Change summary:

Added a simple Spring example for camel-jdbc

(View changes)

Content:

JDBC Component

The jdbc: component allows you to work with databases using JDBC queries and operations via SQL text as the message payload.
This component uses standard Java JDBC to work with the database, unlike the SQL Component component that uses spring-jdbc.

So far endpoints from this component could be used only as producers. It means that you cannot use them in from() statement.

URI format

jdbc:dataSourceName?options

This component only supports producer, meaning that you can not use routes with this component in the from type.

Options

Name Default Value Description
readSize 0 / 2000 The default maximum number of rows that can be read by a polling query. The default value is 2000 for Camel 1.5.0 or older. In newer releases the default value is 0.

Result

The result is returned in the OUT body as a ArrayList<HashMap<String, Object>> list object with the result. The List contains the list of rows and the Map contains each row with the string key as the column name.

Note: This component fetches ResultSetMetaData to be able to return the column name as the key in the Map.

If the query is an update query the update count is returned in the OUT header jdbc.updateCount

Samples

In the sample below 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, notice that we refer to the testdb datasource that was bound in the previous step:

// lets add 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://activemq.apache.org/camel/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>

And then we create the endpoint and sends the exchange containing the SQL query to execute in the in body. The result 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 receives the response from Camel
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("jstrachan", row.get("NAME"));
row = data.get(1);
assertEquals("cust2", row.get("ID"));
assertEquals("nsandhu", row.get("NAME"));

Sample - Polling the database every minute

If we want to poll a database using this component we need to combine this with a polling scheduler such as the Timer or Quartz etc.
In this sample we retrieve data from the database every 60th seconds.

from("timer://foo?period=60000").setBody(constant("select * from customer")).to("jdbc:testdb").to("activemq:queue:customers");

See Also