Define the sql script file for creating a tabled named employees.
CREATE TABLE employees (
empId VARCHAR(10) NOT NULL,
empName VARCHAR(100) NOT NULL
);
Next define properties file for inserting and retrieving
data from the table.
sql.insertEmployee=INSERT INTO employees(EmpId, EmpName) VALUES (:#EmpId, :#EmpName)
sql.getAllEmployees=select * from employees
Define the domain class Employee for holding the data to
be stored in the db.
package com.javainuse.domain;
public class Employee {
private String empId;
private String empName;
public String getEmpId() {
return empId;
}
public void setEmpId(String empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
@Override
public String toString() {
return "Employee [empId=" + empId + ", empName=" + empName + "]";
}
}
Define the EmployeeMapper class as follows-
package com.javainuse.util;
import com.javainuse.domain.Employee;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class EmployeeMapper {
public Map<String, Object> getMap(Employee employee) {
Map<String, Object> answer = new HashMap<String, Object>();
answer.put("EmpId", employee.getEmpId());
answer.put("EmpName", employee.getEmpName());
return answer;
}
public List<Employee> readEmployees(List<Map<String, String>> dataList) {
System.out.println("data:" + dataList);
List<Employee> employees = new ArrayList<Employee>();
for (Map<String, String> data : dataList) {
Employee employee = new Employee();
employee.setEmpId(data.get("EmpId"));
employee.setEmpName(data.get("EmpName"));
employees.add(employee);
}
return employees;
}
}
Define the route using Java DSL as follows-
package com.javainuse.router;
import org.apache.camel.builder.RouteBuilder;
public class EmployeeRouter extends RouteBuilder {
@Override
public void configure() throws Exception {
from("direct:insert").log("Inserted new Employee").beanRef("employeeMapper", "getMap")
.to("sqlComponent:{{sql.insertEmployee}}");
from("direct:select").to("sqlComponent:{{sql.getAllEmployees}}")
.beanRef("employeeMapper", "readEmployees").log("${body}");
}
}
Define the configuration file as follows. Using the
configuration file we configure the database, execute the create SQL
DB script file, load the Java DSL routes.
<?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 ">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/cameldb" />
<property name="username" value="root" />
<property name="password" value="rida" />
</bean>
<jdbc:initialize-database data-source="dataSource"
enabled="true">
<jdbc:script location="classpath:db-schema.sql" />
</jdbc:initialize-database>
<bean id="sqlComponent" class="org.apache.camel.component.sql.SqlComponent">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="employeeMapper" class="com.javainuse.util.EmployeeMapper" />
<bean id="employeeRouter" class="com.javainuse.router.EmployeeRouter" />
<camelContext id="employeeContext" xmlns="http://camel.apache.org/schema/spring">
<propertyPlaceholder id="placeholder" location="classpath:sql.properties" />
<routeBuilder ref="employeeRouter" />
</camelContext>
</beans>
Finally we load the config file and call the routes
defined.
package com.javainuse.main;
import com.javainuse.domain.Employee;
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;
public class CamelMain {
public static void main(String[] args) {
try {
ApplicationContext springCtx = new ClassPathXmlApplicationContext("database-context.xml");
CamelContext context = springCtx.getBean("employeeContext", CamelContext.class);
context.start();
ProducerTemplate producerTemplate = context.createProducerTemplate();
// Insert Employee 1
Employee emp1 = getEmployee1();
String resp = producerTemplate.requestBody("direct:insert", emp1, String.class);
// Insert Employee 2
Employee emp2 = getEmployee2();
resp = producerTemplate.requestBody("direct:insert", emp2, String.class);
// Get Employee of inserted employees
List<Employee> employees = producerTemplate.requestBody("direct:select", null, List.class);
System.out.println("employees:" + employees);
}
catch (Exception e) {
e.printStackTrace();
}
}
private static Employee getEmployee1() {
Employee emp = new Employee();
emp.setEmpId("empId1");
emp.setEmpName("emp1");
return emp;
}
private static Employee getEmployee2() {
Employee emp = new Employee();
emp.setEmpId("empId2");
emp.setEmpName("emp2");
return emp;
}
}
Run the application as a java application.
Download Source Code
Download it -
Apache Camel SQL Queries Example
Example
See Also
Spring Boot Hello World Application- Create simple controller and jsp view using Maven
Spring Boot Tutorial-Spring Data JPA
Spring Boot + Simple Security Configuration
Pagination using Spring Boot Simple Example
Spring Boot + ActiveMQ Hello world Example
Spring Boot + Swagger Example Hello World Example
Spring Boot + Swagger- Understanding the various Swagger Annotations
Spring Boot Main Menu
Spring Boot Interview Questions
