Apache Camel Jdbc Component Insert

0

In this article we will see an example of Apache Camel’s Jdbc Component. We will use Camel’s Jdbc component to access JDBC APIs from a Camel route. The SQL queries and
operations are sent in the message body to URI jdbc:dataSourceName[?options].

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 Insert

To demonstrate the camel jdbc insert example, we first need to initialize the database.

<jdbc:initialize-database data-source="dataSource"
		enabled="true">
    <jdbc:script location="classpath:db-schema.sql" />
</jdbc:initialize-database>

Our schema contains just one table ‘Articles’. We

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 need a data source bean for 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>

We create the source of data we want to insert using bean org.apache.camel.component.dataset.SimpleDataSet.

	<bean id="sampleGenerator" class="org.apache.camel.component.dataset.SimpleDataSet">
		<property name="size" value="1" />
		<property name="defaultHeaders">
			<map>
				<entry key="name" value="Camel Jdbc Insert Component" />
				<entry key="category" value="Apache Camel" />
				<entry key="tags" value="Camel,DSL,Jdbc" />
			</map>
		</property>
	</bean>

The final piece is to build the camel route. We retrieve the data from uri dataset:sampleGenerator and then prepare the insert statement which we insert into the body.
The insert statement is then sent to dbc:dataSource?useHeadersAsParameters=true.

	<camelContext xmlns="http://camel.apache.org/schema/spring">
		<route id="sample-generator-route">
			<from uri="dataset:sampleGenerator" />
			<setBody>
				<constant>
					insert into articles(name, category, tags, author) values
					(:?name, :?category, :?tags, 'Admin');
				</constant>
			</setBody>
			<to uri="jdbc:dataSource?useHeadersAsParameters=true" />
			<to uri="log:insertLog?showHeaders=true" />
		</route>
	</camelContext>

Here is the complete spring XML context.

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: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="sampleGenerator" class="org.apache.camel.component.dataset.SimpleDataSet">
		<property name="size" value="1" />
		<property name="defaultHeaders">
			<map>
				<entry key="name" value="Camel Jdbc Insert Component" />
				<entry key="category" value="Apache Camel" />
				<entry key="tags" value="Camel,DSL,Jdbc" />
			</map>
		</property>
	</bean>

	<camelContext xmlns="http://camel.apache.org/schema/spring">
		<route id="sample-generator-route">
			<from uri="dataset:sampleGenerator" />
			<setBody>
				<constant>
					insert into articles(name, category, tags, author) values
					(:?name, :?category, :?tags, 'Admin');
				</constant>
			</setBody>
			<to uri="jdbc:dataSource?useHeadersAsParameters=true" />
			<to uri="log:insertLog?showHeaders=true" />
		</route>
	</camelContext>
	
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg index="0" ref="dataSource"></constructor-arg>
	</bean>
		
</beans>

Test Camel Jdbc Insert

We will load the spring context, create camelContext and start it.
Once data is inserted, we will use JdbcTemplate to query the table and verify whether row exists.

Article:

package com.javarticles.camel;

public class Article {
    private int id;
    private String name;
    private String author;
    private String category;
    private String tags;
    public int getId() {
        return id;
    }
    public void setId(int 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 name + ":" + author + ":" + category;
    }    
}

Here is the RowMapper implementation to convert row to Article bean.

ArticleRowMapper:

package com.javarticles.camel;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

public class ArticleRowMapper implements RowMapper<Article> {
    public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
        return QueryUtils.extractArticleFromRs(rs);
    }       
}

QueryUtils:

package com.javarticles.camel;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public final class QueryUtils {
    public static Article extractArticleFromRs(ResultSet rs) throws SQLException {
        Article article = new Article();
        article.setId(rs.getInt("ID"));
        article.setName(rs.getString("NAME"));
        article.setAuthor(rs.getString("AUTHOR"));
        article.setCategory(rs.getString("CATEGORY"));
        article.setTags(rs.getString("TAGS"));
        return article;
    }
    
    public static List<Article> extractArticleListFromRs(ResultSet rs) throws SQLException {
        List<Article> articleList = new ArrayList<Article>();
        while(rs.next()) {                
            articleList.add(extractArticleFromRs(rs));
        }
        return articleList;
    }
}

CamelJdbcInsertExampleUsingSpring:

package com.javarticles.camel;

import java.util.List;

import org.apache.camel.CamelContext;
import org.apache.camel.spring.SpringCamelContext;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class CamelJdbcInsertExampleUsingSpring {
    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(3000);
            JdbcTemplate jdbcTemplate = (JdbcTemplate) appContext.getBean("jdbcTemplate");
            List<Article> articles =jdbcTemplate.query("select * from articles", new ArticleRowMapper());
            System.out.println(articles);
        } finally {
            camelContext.stop();
        }
    }
}

Output:

23:21| INFO | MarkerIgnoringBase.java 95 | Exchange[ExchangePattern: InOnly, Headers: {breadcrumbId=ID-INMAA1-L1005-56101-1437587472381-0-1, CamelDataSetIndex=0, CamelJdbcUpdateCount=1, category=Apache Camel, name=Camel Jdbc Insert Component, tags=Camel,DSL,Jdbc}, BodyType: null, Body: [Body is null]]
23:21| INFO | MarkerIgnoringBase.java 95 | Sent: 1 messages so far. Last group took: 0 millis which is: ? messages per second. average: ?
[Camel Jdbc Insert Component:Admin:Apache Camel]
23:21| INFO | DefaultCamelContext.java 2660 | Apache Camel 2.15.2 (CamelContext: camel-1) is shutting down

Download the source code

This was an example about Apache Camel’s Jdbc Component.

You can download the source code here: camelJdbcComponentInsertExample.zip
Share.

Comments are closed.