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>
Related Links