In this article, I will show you how to poll data using Camel JDBC Component. In my last article on Jdbc Component, I showed you how to convert the XML data into individual rows and insert into the database. The way Camel’s JDBC component works is the SQL queries and operations are sent in the message body and then the SQL command is executed against the registered datasource using JDBC endpoint jdbc:dataSourceName[?options]
. You can append query options to the URI in the following format, ?option=value&option=value&...
.
Because a JDBC endpoint accepts a command, it won’t be used as a consumer, so, you can’t use it in a from
DSL statement. In this article, we will retrieve data using a select SQL statement as the command message. The retrieved data is processed using a bean and the processed result will be added as the outgoing message on the exchange.
This example uses the following frameworks:
- Maven 3.2.3
- Apache Camel 2.15.1
- Spring 4.1.5.RELEASE
- Eclipse as the IDE, version Luna 4.4.1.
Dependencies
We are just relying camel’s core components, the spring based components and the logger component in case you want to log something so our pom.xml
consists of:
camel-core
– camel core components like timer, bean etcslf4j-api
– in case you want to use logslf4j-log4j12
– if you want to use log4j as the slf4j implementationcamel-stream
– for printing the messages to consolespring-context
andspring-core
– for spring supportcamel-spring
– include it if you want to define route in springcamel-jdbc
– To access JDBC APIs from Camel routemysql-connector-java
– MySQL Driver
pom.xml:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.javarticles.camel</groupId> <artifactId>camelContentBasedRouting</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.apache.camel</groupId> <artifactId>camel-core</artifactId> <version>2.15.2</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.12</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.12</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.1.5.RELEASE</version> </dependency> <dependency> <groupId>org.apache.camel</groupId> <artifactId>camel-spring</artifactId> <version>2.15.2</version> </dependency> <dependency> <groupId>org.apache.camel</groupId> <artifactId>camel-stream</artifactId> <version>2.15.2</version> </dependency> <dependency> <groupId>org.apache.camel</groupId> <artifactId>camel-jdbc</artifactId> <version>2.15.2</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>4.1.5.RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.1.5.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.26</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.1.5.RELEASE</version> </dependency> </dependencies> </project>
Camel JDBC Select Example
Because a JDBC endpoint accepts a command, it won’t be used as a consumer, so, you can’t use it in a from
DSL statement. In this article, we will retrieve data using a select SQL statement as the command message. The retrieved data is processed using a bean and the processed result will be added as the outgoing message on the exchange.
To demonstrate the camel jdbc Select example, we first need to initialize the database.
Our schema contains just one table ‘Articles’.
db-schema.sql:
drop table if exists `articles`; CREATE TABLE `articles` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `NAME` VARCHAR(100) NOT NULL, `CATEGORY` VARCHAR(50) NOT NULL, `TAGS` VARCHAR(100) NOT NULL, `AUTHOR` VARCHAR(50) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
We also insert some sample data which we will retrieve using select statement.
db-test-data.sql:
insert into articles(id, name, category, tags, author) values (1, "JdbcTemplate Example", "spring", "spring,jdbcTemplate", "Joe"); insert into articles(id, name, category, tags, author) values (2, "Camel JMS Example", "camel", "camel,jms", "Sam"); insert into articles(id, name, category, tags, author) values (3, "Camel JDBC Example", "camel", "camel,jdbc", "Joe");
We will initialize the database using spring <jdbc:initialize-database>.
<jdbc:initialize-database data-source="dataSource" enabled="true"> <jdbc:script location="classpath:db-schema.sql" /> <jdbc:script location="classpath:db-test-data.sql" /> </jdbc:initialize-database>
We also need a data source bean for the spring and camel jdbc component to connect to database.
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost/test" /> <property name="username" value="root" /> <property name="password" value="mnrpass" /> </bean>
The route starts with a timer component which will poll the table every 2 seconds.
<route> <from uri="timer://timer1?period=2s" /> ... </route>
Next, we will set the SELECT statement to be run to the body.
<setBody> <constant> SELECT * FROM ARTICLES WHERE CATEGORY='Camel' </constant> </setBody>
The retrieved rows may result into more than one row. Suppose you want to process each row, you need to split the output and then send it to a row processor bean.
<split> <simple>${body}</simple> <log message="process row ${body}"/> <to uri="bean:rowProcessor" /> <to uri="stream:out"/> </split>
Here is the row processor which converts the row in Map
form into POJO Article
.
RowProcessor:
package com.javarticles.camel; import java.util.Map; import org.apache.camel.Exchange; import org.apache.camel.Processor; public class RowProcessor implements Processor { public void process(Exchange exchange) throws Exception { Map<String, Object> row = exchange.getIn().getBody(Map.class); System.out.println("Processing " + row); Article article = new Article(); article.setAuthor((String) row.get("AUTHOR")); article.setCategory((String) row.get("CATEGORY")); article.setName((String) row.get("NAME")); article.setTags((String) row.get("TAGS")); article.setId((Long) row.get("ID")); exchange.getOut().setBody(article); } }
The article bean:
Article:
package com.javarticles.camel; public class Article { private long id; private String name; private String author; private String category; private String tags; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public String getCategory() { return category; } public void setCategory(String category) { this.category = category; } public String getTags() { return tags; } public void setTags(String tags) { this.tags = tags; } public String toString() { return "id:" + id + ":" + "name:" + name + ":" + "author:" + author + ":" + "category:" + category; } }
The spring context file:
applicationContext.xml:
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd http://camel.apache.org/schema/spring http://camel.apache.org/schema/spring/camel-spring.xsd"> <jdbc:initialize-database data-source="dataSource" enabled="true"> <jdbc:script location="classpath:db-schema.sql" /> <jdbc:script location="classpath:db-test-data.sql" /> </jdbc:initialize-database> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost/test" /> <property name="username" value="root" /> <property name="password" value="mnrpass" /> </bean> <bean id="rowProcessor" class="com.javarticles.camel.RowProcessor"> </bean> <camelContext xmlns="http://camel.apache.org/schema/spring"> <route> <from uri="timer://timer1?period=2s" /> <log message="select from articles"/> <setBody> <constant> SELECT * FROM ARTICLES WHERE CATEGORY='Camel' </constant> </setBody> <to uri="jdbc:dataSource" /> <log message="split rows"/> <split> <simple>${body}</simple> <log message="process row ${body}"/> <to uri="bean:rowProcessor" /> <to uri="stream:out"/> </split> </route> </camelContext> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg index="0" ref="dataSource"></constructor-arg> </bean> </beans>
Run JDBC Select Example
We just need to load the spring context, create camel context and sleep for 1 or 2 seconds for the route to run through.
CamelJdbcSelectExampleUsingSpring:
package com.javarticles.camel; import org.apache.camel.CamelContext; import org.apache.camel.spring.SpringCamelContext; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; public class CamelJdbcSelectExampleUsingSpring { public static final void main(String[] args) throws Exception { ApplicationContext appContext = new ClassPathXmlApplicationContext( "applicationContext.xml"); CamelContext camelContext = SpringCamelContext.springCamelContext( appContext, false); try { camelContext.start(); Thread.sleep(2000); } finally { camelContext.stop(); } } }
You can see the select statement is executed, the retrieved rows are sent to the row processor which in turn converts the row into a POJO.
Output:
18:07| INFO | DefaultCamelContext.java 2454 | Apache Camel 2.15.2 (CamelContext: camel-1) started in 0.000 seconds 18:07| INFO | MarkerIgnoringBase.java 95 | select from articles 18:07| INFO | MarkerIgnoringBase.java 95 | split rows 18:07| INFO | MarkerIgnoringBase.java 95 | process row {ID=2, NAME=Camel JMS Example, CATEGORY=camel, TAGS=camel,jms, AUTHOR=Sam} Processing {ID=2, NAME=Camel JMS Example, CATEGORY=camel, TAGS=camel,jms, AUTHOR=Sam} id:2:name:Camel JMS Example:author:Sam:category:camel 18:07| INFO | MarkerIgnoringBase.java 95 | process row {ID=3, NAME=Camel JDBC Example, CATEGORY=camel, TAGS=camel,jdbc, AUTHOR=Joe} Processing {ID=3, NAME=Camel JDBC Example, CATEGORY=camel, TAGS=camel,jdbc, AUTHOR=Joe} id:3:name:Camel JDBC Example:author:Joe:category:camel 18:07| INFO | DefaultCamelContext.java 2660 | Apache Camel 2.15.2 (CamelContext: camel-1) is shutting down
Download source code
This was an example about Apache Camel Jdbc Select Example.