Apache Camel Tutorial- Integrate with MySQL DB using SQL query | JavaInUse







Apache Camel Tutorial- Integrate with MySQL DB using SQL query

In this post we will use SQL queries with Apache Camel to fetch and upate from mysql table.

Apache Camel - Table of Contents

File Transfer Using Java DSL Apache Camel Apache Camel Java DSL + Spring Integration Hello World Example Apache Camel Exception Handling Using Simple Example Apache Camel Redelivery policy using example Integrate Apache Camel and ActiveMQ EIP patterns using Apache Camel Apache Camel Tutorial- Integrate Spring Boot+ Apache Camel Apache Camel Tutorial- Integrate with MySQL DB using SQL query Apache Camel EIP - Splitter and Aggregator pattern Apache Camel Unit Testing Apache Camel + Spring + Quartz Hello World Example Camel application deployment on JBoss Fuse Apache Camel + Apache CXF SOAP Webservices Apache Camel + JAX-RS REST Webservice Apache Camel + CXFRS REST Webservice Apache Camel Routing Slip EIP Pattern Apache Camel Dynamic Router Pattern Apache Camel Load Balancer EIP Pattern Apache Camel Interceptors Apache Camel + Kafka Hello World Example Apache Camel - Marshalling/Unmarshalling XML/JSON Data Example

Video

This tutorial is explained in the below Youtube Video.

Lets Begin

The maven project will be as follows-

Apache Camel MYSQL Tutorial

Define the pom.xml as follows-
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.test</groupId>
<artifactId>camel-data-mysql</artifactId>
<version>0.0.1-SNAPSHOT</version>

	<properties>
		<spring.version>4.1.6.RELEASE</spring.version>
		<camelspring.version>2.16.0</camelspring.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-core</artifactId>
			<version></version>
		</dependency>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-cxf</artifactId>
			<version></version>
		</dependency>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-spring</artifactId>
			<version></version>
		</dependency>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-sql</artifactId>
			<version>2.17.1</version>
		</dependency>

		<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.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version></version>
		</dependency>
	</dependencies>
</project>
	
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("");
    }

}

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.

Apache Camel MYSQL Example


Apache Camel MYSQL Output

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