Apache Camel Jdbc Select Example

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:

1. Maven 3.2.3
2. Apache Camel 2.15.1
3. Spring 4.1.5.RELEASE
4. 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:

1. camel-core – camel core components like timer, bean etc
2. slf4j-api – in case you want to use log
3. slf4j-log4j12 – if you want to use log4j as the slf4j implementation
4. camel-stream – for printing the messages to console
5. spring-context and spring-core – for spring support
6. camel-spring – include it if you want to define route in spring
7. camel-jdbc – To access JDBC APIs from Camel route
8. mysql-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.