By kswaughs | Tuesday, September 13, 2016

Camel SQL Stored Example

Camel SQL Stored Component is used to call stored procedures.

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 to call stored procedures using an embedded derby database.

Step 1: DataSource Setup

db-schema.sql
CREATE TABLE books (
  BookId VARCHAR(10) NOT NULL,
  BookName VARCHAR(100) NOT NULL,
  author VARCHAR(50) NOT NULL,
  price VARCHAR(20),
  CreateDate VARCHAR(50) NOT NULL
);

// Inserting two books data
INSERT INTO books(BookId, BookName, Author, Price, CreateDate) VALUES 
('FICT3', 'The Ruins', 'Scott Smith', '$9', 'July 18, 2006');
INSERT INTO books(BookId, BookName, Author, Price, CreateDate) VALUES 
('FICT4', 'Velocity', 'Dean Koontz', '$11', 'July 20, 2006');

// Stored procedure to fetch all books
CREATE PROCEDURE GET_ALL_BOOKS() PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'com.kswaughs.db.util.BookStoredProcedure.findAllBooks';

// Stored procedure to fetch requested book
CREATE PROCEDURE GET_BOOK(IN book_name VARCHAR(100)) PARAMETER STYLE JAVA LANGUAGE JAVA MODIFIES SQL DATA DYNAMIC RESULT SETS 1 EXTERNAL NAME 'com.kswaughs.db.util.BookStoredProcedure.findBook';

Stored procedure implementation class is BookStoredProcedure. This is Apache Derby's way of implementation, purely written in Java. This has nothing to do with camel.

BookStoredProcedure.java
package com.kswaughs.db.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.support.JdbcUtils;

public class BookStoredProcedure {

    /**
     * Derby Stored Procedure implementation to read all books 
     * @param bookResults
     * @throws SQLException
     */
    public static void findAllBooks(ResultSet[] bookResults)
            throws SQLException {

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = DriverManager.getConnection("jdbc:default:connection");
            String sql = "select * from books";
            statement = connection.prepareStatement(sql);
            bookResults[0] = statement.executeQuery();
        } finally {
            
            JdbcUtils.closeConnection(connection);
        }

    }
    
    /**
     * Derby Stored Procedure implementation to read requested book
     * @param bookResults
     * @throws SQLException
     */
    public static void findBook(String book_name, ResultSet[] bookResults)
            throws SQLException {

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = DriverManager.getConnection("jdbc:default:connection");
            
            String query = "select * from books where BookName = ?";
                
            statement = connection.prepareStatement(query);
            statement.setString(1, book_name);
            
            bookResults[0] = statement.executeQuery();
        } finally {
            
            JdbcUtils.closeConnection(connection);
        }
    }
}

Step 2: Application Context Configuration

database-context.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"
    xmlns:cxf="http://camel.apache.org/schema/cxf" 
    xmlns:jaxrs="http://cxf.apache.org/jaxrs"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
       http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://camel.apache.org/schema/cxf
       http://camel.apache.org/schema/cxf/camel-cxf.xsd 
       http://cxf.apache.org/jaxrs
       http://cxf.apache.org/schemas/jaxrs.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
       http://www.springframework.org/schema/context 
       http://www.springframework.org/schema/context/spring-context.xsd ">

    <!-- this is the JDBC data source which uses an in-memory only Apache Derby database -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <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="" />
    </bean>

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

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

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

    <bean id="bookRouter" class="com.kswaughs.router.BookRouter" />
    
    <camelContext id="bookCtx" xmlns="http://camel.apache.org/schema/spring">
    
        <routeBuilder ref="bookRouter" />

    </camelContext>

</beans>

Step 3: Define Routers for calling two stored procedures using Java DSL

BookRouter.java
package com.kswaughs.router;

import org.apache.camel.builder.RouteBuilder;

public class BookRouter extends RouteBuilder {

    @Override
    public void configure() throws Exception {
        
         from("direct:sp")
            .to("sqlStored:GET_ALL_BOOKS()")
            .bean("bookMapper", "readAllBooks")
            .log("${body}");
        
        from("direct:sp-getbook")
            .bean("bookMapper", "buildReqMap")
            .to("sqlStored:GET_BOOK(VARCHAR :#BookName)")
            .bean("bookMapper", "readAllBooks")
            .log("${body}");
    }

}

Step 4: Create POJO class & Row mapper class

Book.java
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) {
        this.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;
    }

    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Book [bookId=");
        builder.append(bookId);
        builder.append(", bookName=");
        builder.append(bookName);
        builder.append(", author=");
        builder.append(author);
        builder.append(", price=");
        builder.append(price);
        builder.append(", createDate=");
        builder.append(createDate);
        builder.append("]");
        return builder.toString();
    }
}

BookMapper.java
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 {
    
    /**
     * Transforms input request to request map
     * @param bookName
     * @return
     */
    public Map<String, Object> buildReqMap(String bookName) {
        Map<String, Object> answer = new HashMap<String, Object>();
        answer.put("BookName", bookName);
        return answer;
    }

        /**
     * Transforms Resultsets into List of Books objects
     * @param resultSets
     * @return
     * @throws Exception
     */
    public List<Book> readAllBooks(Map<String, List<Map<String, String>>> resultSets) 
        throws Exception {
          
        List<Book> books = new ArrayList<Book>();
      
        System.out.println("resultSets:"+resultSets);
        
        Set<String>  keys = resultSets.keySet();
        
        for (String key : keys) {
           
           List<Map<String, String>> rsts = resultSets.get(key);
           
           for (Map<String, String>  rst: rsts) {
            
                Book book = new Book();
                
                book.setBookId(rst.get("BookId"));
                book.setBookName(rst.get("BookName"));
                book.setAuthor(rst.get("Author"));
                book.setPrice(rst.get("Price"));
                book.setCreateDate(rst.get("CreateDate"));
                
                books.add(book);
            }
         }
        
      return books;
    }
}

Step 5: Test the application

CamelBookApp.java
package com.kswaughs.app;

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 org.springframework.context.support.ClassPathXmlApplicationContext;

import com.kswaughs.db.util.Book;

public class CamelBookApp {

    public static void main(String[] args) {

        try {
            ApplicationContext springCtx = new ClassPathXmlApplicationContext(
                    "database-context.xml");

            CamelContext context = springCtx.getBean("bookCtx", CamelContext.class);
            
            context.start();
        
            ProducerTemplate producerTemplate = context.createProducerTemplate();
            
             // Fetch all books
            List<Book> resp = producerTemplate.requestBody("direct:sp",  null, List.class);
            System.out.println("SP resp:"+resp);
            
            // Fetch book by name
            List<Book> resp1 = producerTemplate
                 .requestBody("direct:sp-getbook",  "Velocity", List.class);
            System.out.println("SP resp1:"+resp1);
       
        } catch (Exception e) {
          
            e.printStackTrace();
        } 
    }
 
}

Console Logs

 
resultSets:{#result-set-1=[{BOOKID=FICT3, BOOKNAME=The Ruins, AUTHOR=Scott Smith, PRICE=$9, CREATEDATE=July 18, 2006}, {BOOKID=FICT4, BOOKNAME=Velocity, AUTHOR=Dean Koontz, PRICE=$11, CREATEDATE=July 20, 2006}]}
INFO|09/13/2016 11:39:18 980|[Book [bookId=FICT3, bookName=The Ruins, author=Scott Smith, price=$9, createDate=July 18, 2006], Book [bookId=FICT4, bookName=Velocity, author=Dean Koontz, price=$11, createDate=July 20, 2006]]
SP resp:[Book [bookId=FICT3, bookName=The Ruins, author=Scott Smith, price=$9, createDate=July 18, 2006], Book [bookId=FICT4, bookName=Velocity, author=Dean Koontz, price=$11, createDate=July 20, 2006]]
resultSets:{#result-set-1=[{BOOKID=FICT4, BOOKNAME=Velocity, AUTHOR=Dean Koontz, PRICE=$11, CREATEDATE=July 20, 2006}]}
INFO|09/13/2016 11:39:19 000|[Book [bookId=FICT4, bookName=Velocity, author=Dean Koontz, price=$11, createDate=July 20, 2006]]
SP resp1:[Book [bookId=FICT4, bookName=Velocity, author=Dean Koontz, price=$11, createDate=July 20, 2006]]

Maven dependencies

pom.xml
<properties>
    <spring.version>4.1.6.RELEASE</spring.version>
    <camelspring.version>2.16.0</camelspring.version>  
</properties>
    
<dependencies>
    <!-- Camel Dependencies -->
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-core</artifactId>
        <version>${camelspring.version}</version>
    </dependency>
     <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-cxf</artifactId>
        <version>${camelspring.version}</version>
    </dependency> 
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-spring</artifactId>
        <version>${camelspring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-sql</artifactId>
        <version>2.17.1</version>
    </dependency>
    <!-- End of Camel Dependencies -->

    <dependency>
        <groupId>commons-dbcp</groupId>
        <artifactId>commons-dbcp</artifactId>
        <version>1.2.2</version>
    </dependency>

    <dependency>
        <groupId>commons-pool</groupId>
        <artifactId>commons-pool</artifactId>
        <version>1.6</version>
    </dependency>
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derby</artifactId>
        <version>10.11.1.1</version>
    </dependency>
    
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${spring.version}</version>
    </dependency>    
</dependencies>

Recommend this on


1 comment:

  1. So, get inspired by some concepts below and think HP Stylus Pen of your business plan. Families that love delicious selfmade food are always looking for helpful 3D printed products for his or her kitchen. Thus, you will love to discover helpful baker’s cubes, measuring cups and spoons among our 3D printing project concepts. Brushing enamel every single day is extra enjoyable when you'll be able to|you possibly can} simply squeeze the toothpaste, even with some leftovers within the tube. A remarkable but easy squeezer can simplify your fight for the final bits of the paste. All you need are squeezer STL files and some of your time to commit to this handy 3D printer project.

    ReplyDelete