In my previous article, I showed you a very basic example of JDBC Insert using the dataSet bean. In this article, we will use XML as the source of data and use XPath to retrieve each row. Since XML contains many rows, we will have to split the XML into individual rows using XPath. The data retrieved will be used to create the insert command and set it to the body. JDBC component takes the body of the message as a command and is sent to URI jdbc:dataSourceName[?options] for its execution.
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-contextandspring-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 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 will send the articles information as XML data.
<blog>
<article>
<category>spring integration</category>
<title>SpringInt Splitter</title>
<tags>spring,spring integration</tags>
</article>
....
</blog>
We will use XPath to split the XML into individual rows:
<split>
<xpath>/blog/article</xpath>
...
</split>
Let’s look into the camel route. We will consume the data from URI direct:xmlsource.
<camelContext xmlns="http://camel.apache.org/schema/spring"> <camelContext xmlns="http://camel.apache.org/schema/spring"> <route> <from uri="direct:xmlsource" /> <split> <xpath>/blog/article</xpath> <to uri="bean:xpathRowPopulator" /> <to uri="stream:out"/> <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" /> </split> </route> </camelContext>
We create the source of data we want to insert using bean org.apache.camel.component.dataset.SimpleDataSet.
<bean id="xpathRowPopulator" class="com.javarticles.camel.XPathRowPopulator"/>
The bean:xpathRowPopulator uri is a bean endpoint, it uses XPath to parse an incoming order message with a body. The column values are set to the inbound message as headers.
XPathRowPopulator:
package com.javarticles.camel;
import org.apache.camel.Exchange;
import org.apache.camel.language.XPath;
public class XPathRowPopulator {
public void process(@XPath("article/category") String category,
@XPath("article/title") String title,
@XPath("article/tags") String tags, Exchange exchange) throws Exception {
exchange.getIn().setHeader("name", title);
exchange.getIn().setHeader("category", category);
exchange.getIn().setHeader("tags", tags);
}
}
The data in this order is then converted to an SQL statement.
<setBody>
<constant>
insert into articles(name, category, tags, author) values
(:?name, :?category, :?tags, 'Admin');
</constant>
</setBody>
This insert SQL statement is set as the body of a message and will be passed into the JDBC endpoint. Note that Camel will set the CamelJdbcUpdateCount header to the number of rows updated
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.ProducerTemplate;
import org.apache.camel.impl.DefaultProducerTemplate;
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 CamelJdbcInsertXPathExample {
public static void main(String[] args) throws Exception {
final String articlesXml = "<blog><article><category>spring integration</category><title>SpringInt Splitter</title><tags>spring,spring integration</tags></article>"
+ "<article><category>java</category><title>Lambda</title><tags>java8,lambda</tags></article>"
+ "<article><category>camel</category><title>Camel Multicast</title><tags>camel,eip</tags></article>"
+ "<article><category>Hibernate</category><title>Hibernate Spring Inetgartion</title><tags>java persistence,spring,hibernate</tags></article></blog>";
ApplicationContext appContext = new ClassPathXmlApplicationContext(
"applicationContext.xml");
CamelContext camelContext = SpringCamelContext.springCamelContext(
appContext, false);
try {
ProducerTemplate template = new DefaultProducerTemplate(
camelContext);
camelContext.start();
template.start();
template.sendBody("direct:xmlsource", articlesXml);
Thread.sleep(3000);
JdbcTemplate jdbcTemplate = (JdbcTemplate) appContext
.getBean("jdbcTemplate");
List<Article> articles = jdbcTemplate.query(
"select * from articles", new ArticleRowMapper());
System.out.println("Select * from Articles");
System.out.println(articles);
} finally {
camelContext.stop();
}
}
}
Output:
15:51| INFO | XPathBuilder.java 1203 | Created default XPathFactory com.sun.org.apache.xp[email protected] <article><category>spring integration</category><title>SpringInt Splitter</title><tags>spring,spring integration</tags></article>15:51| INFO | MarkerIgnoringBase.java 95 | Exchange[ExchangePattern: InOnly, Headers: {breadcrumbId=ID-INMAA1-L1005-49520-1437646919312-0-1, CamelJdbcUpdateCount=1, category=spring integration, name=SpringInt Splitter, tags=spring,spring integration}, BodyType: null, Body: [Body is null]] <article><category>java</category><title>Lambda</title><tags>java8,lambda</tags></article>15:51| INFO | MarkerIgnoringBase.java 95 | Exchange[ExchangePattern: InOnly, Headers: {breadcrumbId=ID-INMAA1-L1005-49520-1437646919312-0-1, CamelJdbcUpdateCount=1, category=java, name=Lambda, tags=java8,lambda}, BodyType: null, Body: [Body is null]] <article><category>camel</category><title>Camel Multicast</title><tags>camel,eip</tags></article>15:51| INFO | MarkerIgnoringBase.java 95 | Exchange[ExchangePattern: InOnly, Headers: {breadcrumbId=ID-INMAA1-L1005-49520-1437646919312-0-1, CamelJdbcUpdateCount=1, category=camel, name=Camel Multicast, tags=camel,eip}, BodyType: null, Body: [Body is null]] <article><category>Hibernate</category><title>Hibernate Spring Inetgartion</title><tags>java persistence,spring,hibernate</tags></article>15:51| INFO | MarkerIgnoringBase.java 95 | Exchange[ExchangePattern: InOnly, Headers: {breadcrumbId=ID-INMAA1-L1005-49520-1437646919312-0-1, CamelJdbcUpdateCount=1, category=Hibernate, name=Hibernate Spring Inetgartion, tags=java persistence,spring,hibernate}, BodyType: null, Body: [Body is null]] Select * from Articles [SpringInt Splitter:Admin:spring integration, Lambda:Admin:java, Camel Multicast:Admin:camel, Hibernate Spring Inetgartion:Admin:Hibernate]
Download the source code
This was an example about Apache Camel’s JDBC Insert using XPath.
You can download the source code here: camelJdbcComponentInsertXPathExample.zip
