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