By kswaughs | Tuesday, August 30, 2016

Camel SQL Component Example

Camel SQL Component is used to perform database operations using JDBC queries.

As a developer, You have to write only a mapper class to transform input/output parameters to your pojo class.

In this example, we will see how to use this component for insert and read books from an embedded derby database.

Step 1: DataSource Setup

  BookName VARCHAR(100) NOT NULL,
  author VARCHAR(50) NOT NULL,
  price VARCHAR(20),
  CreateDate VARCHAR(50) NOT NULL

Step 2: Externalize the sql queries in a file

sql.insertBook=INSERT INTO books(BookId, BookName, Author, Price, CreateDate) VALUES (:#BookId, :#BookName, :#Author, :#Price, :#CreateDate)

sql.getAllBooks=select * from books

Step 3: Application Context Configuration

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns=""
    xsi:schemaLocation=" ">

    <!-- this is the JDBC data source which uses an in-memory only Apache Derby database -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        <property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver" />
        <property name="url" value="jdbc:derby:memory:orders;create=true" />
        <property name="username" value="" />
        <property name="password" value="" />

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

    <!-- configure the Camel SQL component to use the JDBC data source -->
    <bean id="sqlComponent" class="org.apache.camel.component.sql.SqlComponent">
        <property name="dataSource" ref="dataSource" />

    <bean id="bookMapper" class="com.kswaughs.db.util.BookMapper" />

    <bean id="bookRouter" class="com.kswaughs.router.BookRouter" />
    <camelContext id="bookCtx" xmlns="">
        <!-- use Camel property placeholder loaded from the given file -->
        <propertyPlaceholder id="placeholder" location="" />

        <routeBuilder ref="bookRouter" />



Step 4: Define Routers for Insert & Read using Java DSL
package com.kswaughs.router;

import org.apache.camel.builder.RouteBuilder;

public class BookRouter extends RouteBuilder {

    public void configure() throws Exception {
            .log("Inserted new Book")
            .bean("bookMapper", "getMap")
            .bean("bookMapper", "readBooks")


If you want to configure routes using spring DSL, replace camelContext section from database-context.xml with below configuration.

<camelContext id="bookCtx" xmlns="">

        <!-- use Camel property placeholder loaded from the given file -->
        <propertyPlaceholder id="placeholder" location="" />

        <!-- route that generate new orders and insert them in the database -->
        <route id="insertBook-route">
            <from uri="direct:insert" />
            <log message="Inserted new Book" />
                <method ref="bookMapper" method="getMap" />
            <to uri="sqlComponent:{{sql.insertBook}}" />

        <route id="getAllBooks-route">
            <from uri="direct:select" />
            <to uri="sqlComponent:{{sql.getAllBooks}}" />
            <to uri="bean:bookMapper?method=readBooks" />
            <log message="${body}" />


Step 5: Create POJO class & Row mapper class
package com.kswaughs.beans;

public class Book {
    private String bookId;
    private String bookName;
    private String author;
    private String price;
    private String createDate;
    public String getBookId() {
        return bookId;
    public void setBookId(String bookId) {
        this.bookId = bookId;
    public String getBookName() {
        return bookName;
    public void setBookName(String bookName) {
        this.bookName = bookName;
    public String getAuthor() {
        return author;
    public void setAuthor(String author) { = author;
    public String getPrice() {
        return price;
    public void setPrice(String price) {
        this.price = price;
    public String getCreateDate() {
        return createDate;
    public void setCreateDate(String createDate) {
        this.createDate = createDate;

    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Book [bookId=");
        builder.append(", bookName=");
        builder.append(", author=");
        builder.append(", price=");
        builder.append(", createDate=");
        return builder.toString();
package com.kswaughs.db.util;

import com.kswaughs.beans.Book;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BookMapper {
    public Map<String, Object> getMap(Book book) {
        Map<String, Object> answer = new HashMap<String, Object>();
        answer.put("BookId", book.getBookId());
        answer.put("BookName", book.getBookName());
        answer.put("Author", book.getAuthor() );
        answer.put("Price", book.getPrice());
        answer.put("CreateDate", book.getCreateDate());
        return answer;

   public List<Book> readBooks( List<Map<String, String>>  dataList) {
        List<Book> books = new ArrayList<Book>();
        for (Map<String, String> data : dataList) {
            Book book = new Book();
       return books;

Step 6: Test the application

import com.kswaughs.beans.Book;

import java.util.Date;
import java.util.List;

import org.apache.camel.CamelContext;
import org.apache.camel.ProducerTemplate;
import org.springframework.context.ApplicationContext;

import com.kswaughs.db.util.Book;

public class CamelBookApp {

    public static void main(String[] args) {

        try {
            ApplicationContext springCtx = new ClassPathXmlApplicationContext(

            CamelContext context = springCtx.getBean("bookCtx", CamelContext.class);
            ProducerTemplate producerTemplate = context.createProducerTemplate();
            // Insert book 1
            Book book1 = buildBook1();
            String resp = producerTemplate.requestBody("direct:insert",  book1, String.class);
            // Insert book 2
            Book book2 = buildBook2();
            resp = producerTemplate.requestBody("direct:insert",  book2, String.class);
            // Read all books
            List<Book> resp1 = producerTemplate
                     .requestBody("direct:select",  null, List.class);
        } catch (Exception e) {
    private static Book buildBook1() {
        Book book = new Book();
        book.setBookName("Rogue Lawyer");
        book.setAuthor("John Grisham");
        book.setCreateDate(new Date().toString());
        return book;

    private static Book buildBook2() {
        Book book = new Book();
        book.setBookName("Doctor Sleep");
        book.setAuthor("Stephen King");
        book.setCreateDate(new Date().toString());
        return book;

Console Logs

INFO|08/30/2016 11:05:26 918|Refreshing startup date [Tue Aug 30 11:05:26 IST 2016]; root of context hierarchy
INFO|08/30/2016 11:05:26 966|Loading XML bean definitions from class path resource [database-context.xml]
INFO|08/30/2016 11:05:29 385|Executing SQL script from class path resource [db-schema.sql]
INFO|08/30/2016 11:05:29 529|Executed SQL script from class path resource [db-schema.sql] in 144 ms.
INFO|08/30/2016 11:05:29 806|Apache Camel 2.16.0 (CamelContext: bookCtx) is starting
INFO|08/30/2016 11:05:29 807|JMX is enabled
INFO|08/30/2016 11:05:29 961|Loaded 198 type converters
INFO|08/30/2016 11:05:30 005|Runtime endpoint registry is in extended mode gathering usage statistics of all incoming and outgoing endpoints (cache limit: 1000)
INFO|08/30/2016 11:05:30 127|AllowUseOriginalMessage is enabled. If access to the original message is not needed, then its recommended to turn this option off as it may improve performance.
INFO|08/30/2016 11:05:30 127|StreamCaching is not in use. If using streams then its recommended to enable stream caching. See more details at
INFO|08/30/2016 11:05:30 211|Route: route1 started and consuming from: Endpoint[direct://insert]
INFO|08/30/2016 11:05:30 213|Route: route2 started and consuming from: Endpoint[direct://select]
INFO|08/30/2016 11:05:30 213|Total 2 routes, of which 2 is started.
INFO|08/30/2016 11:05:30 214|Apache Camel 2.16.0 (CamelContext: bookCtx) started in 0.408 seconds
INFO|08/30/2016 11:05:30 217|Apache Camel 2.16.0 (CamelContext: bookCtx) is starting
INFO|08/30/2016 11:05:30 217|Total 2 routes, of which 2 is started.
INFO|08/30/2016 11:05:30 217|Apache Camel 2.16.0 (CamelContext: bookCtx) started in 0.000 seconds
INFO|08/30/2016 11:05:30 228|Inserted new Book
resp:{BookName=Rogue Lawyer, Price=$10, Author=John Grisham, BookId=FICT1, CreateDate=Tue Aug 30 11:05:30 IST 2016}
INFO|08/30/2016 11:05:30 297|Inserted new Book
resp:{BookName=Doctor Sleep, Price=$9, Author=Stephen King, BookId=FICT2, CreateDate=Tue Aug 30 11:05:30 IST 2016}
data:[{BOOKID=FICT1, BOOKNAME=Rogue Lawyer, AUTHOR=John Grisham, PRICE=$10, CREATEDATE=Tue Aug 30 11:05:30 IST 2016}, {BOOKID=FICT2, BOOKNAME=Doctor Sleep, AUTHOR=Stephen King, PRICE=$9, CREATEDATE=Tue Aug 30 11:05:30 IST 2016}]
INFO|08/30/2016 11:05:30 328|[Book [bookId=FICT1, bookName=Rogue Lawyer, author=John Grisham, price=$10, createDate=Tue Aug 30 11:05:30 IST 2016], Book [bookId=FICT2, bookName=Doctor Sleep, author=Stephen King, price=$9, createDate=Tue Aug 30 11:05:30 IST 2016]]
resp1:[Book [bookId=FICT1, bookName=Rogue Lawyer, author=John Grisham, price=$10, createDate=Tue Aug 30 11:05:30 IST 2016], Book [bookId=FICT2, bookName=Doctor Sleep, author=Stephen King, price=$9, createDate=Tue Aug 30 11:05:30 IST 2016]]

Maven dependencies

    <!-- Camel Dependencies -->
    <!-- End of Camel Dependencies -->



Recommend this on

No comments:

Post a Comment