Search Tutorials


Spring Boot + Apache Camel SQL component + MySQL - Hello World Example | JavaInUse
     
   

Spring Boot + Apache Camel SQL component + MySQL - Hello World Example

In previous tutorial we had implemented Spring Boot + Apache Camel JDBC Component + MySQL Example  for inserting and retrieving records from MySQL.  In this tutorial we will be making use of Spring Boot and Apache Camel SQL Component for inserting and retrieving records from MySQL.
The SQL component allows us to work with databases using JDBC queries. The difference between this SQL component and JDBC component is that in case of SQL the query is a property of the endpoint and it uses message payload as parameters passed to the query. So it is much easier to use parameterized queries. In case of JDBC component we had to pass the query in camel body. Also the SQL component uses spring-jdbc behind the scenes for  the actual SQL handling, while JDBC component uses the standard JDBC API. So Camel SQL component is much advanced as we can also make use of features like Spring transaction, which we will see in next tutorial.

Apache Camel Spring Boot SQL Component
     

Video

This tutorial is explained in the below Youtube Video.

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 Calling and Consuming Webservices using Apache Camel Apache Camel Tutorial - Send SMTP Email Using Gmail Apache Camel Tutorial - SEDA component Hello World Example Apache Camel Tutorial - Idempotent Consumer using MemoryIdempotentRepository and FileIdempotentRepository Spring Boot + Apache Camel + RabbitMQ - Hello World Example Spring Boot + Apache Camel JDBC component + MySQL - Hello World Example Spring Boot + Apache Camel SQL component + MySQL - Hello World Example Spring Boot + Apache Camel SQL component + Transaction Management Example
The Maven Project is as follows-  
Apache Camel Spring Boot SQL Component Maven
  The pom.xml is as follows. We make use of the camel-sql dependency.
<?xml version="1.0" encoding="UTF-8"?>
<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.javainuse</groupId>
	<artifactId>boot-camel-sql</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>boot-sql</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-spring-boot</artifactId>
			<version>2.17.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.camel</groupId>
			<artifactId>camel-sql</artifactId>
			<version>2.17.0</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

</project>
In a previous tutorial we had seen the advantages of make using Spring Boot JDBC.  One advantage is that we do not need to create any datasource bean. It get automatically created for us.
In the application.properties file specify the datasource properties
spring.datasource.url=jdbc:mysql://localhost/bootdb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.platform=mysql
spring.datasource.initialization-mode=always
Create the schema-mysql.sql file and specify the initialization scripts. Spring Boot JDBC will automatically run this initialization scripts on startup.
DROP TABLE IF EXISTS employee;

CREATE TABLE employee (
  empId VARCHAR(10) NOT NULL,
  empName VARCHAR(100) NOT NULL
);

Create the Employee Domain class
package com.javainuse.model;

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 + "]";
	}

}

   
Create the Service class where will be configuring the camel route. This service class will extend Apache Camel RouteBuilder.  Also in the service class we autowire the datasource bean created for us by Spring Boot JDBC.
We will be configuring 2 routes -
  • Insert Route -  Using this route we will be inserting the employee details in the MySQL database. As we are making use of camel sql component, we will be making a call to sql endpoint, where query is defined as the property. If the query needs parameters we need to pass these to the camel sql endpoint as map.
  • Select Route -  Using this route we retrieve the employee details from the MySQL database. We then return the list of employees.
package com.javainuse.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.component.sql.SqlComponent;
import org.apache.camel.spring.spi.SpringTransactionPolicy;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;

import com.javainuse.model.Employee;

@Service
public class EmployeeServiceImpl extends RouteBuilder {

	@Autowired
	DataSource dataSource;

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

    	//define the SQL Component bean which will be used as an endpoint in our route
	@Bean
	public SqlComponent sql(DataSource dataSource) {
		SqlComponent sql = new SqlComponent();
		sql.setDataSource(dataSource);
		return sql;
	}

	@Override
	public void configure() throws Exception {
        
       		 //Insert Route
		from("direct:insert").log("Processing message: ").setHeader("message", body()).process(new Processor() {
			public void process(Exchange xchg) throws Exception {
			    //take the Employee object from the exchange and create the parameter map
				Employee employee = xchg.getIn().getBody(Employee.class);
				Map<String, Object> employeeMap = new HashMap<String, Object>();
				employeeMap.put("EmpId", employee.getEmpId());
				employeeMap.put("EmpName", employee.getEmpName());
				xchg.getIn().setBody(employeeMap);
			}
		}).to("sql:INSERT INTO employee(EmpId, EmpName) VALUES (:#EmpId, :#EmpName)");
        
        // Select Route
        from("direct:select").to("sql:select * from employee").process(new Processor() {
			public void process(Exchange xchg) throws Exception {
			//the camel sql select query has been executed. We get the list of employees.
				ArrayList<Map<String, String>> dataList = (ArrayList<Map<String, String>>) xchg.getIn().getBody();
				List<Employee> employees = new ArrayList<Employee>();
				System.out.println(dataList);
				for (Map<String, String> data : dataList) {
					Employee employee = new Employee();
					employee.setEmpId(data.get("empId"));
					employee.setEmpName(data.get("empName"));
					employees.add(employee);
				}
				xchg.getIn().setBody(employees);
			}
		});
        
	}
}

Create the Controller class from where will be calling the Camel route using the ProducerTemplate.
We expose two REST endpoints in this class -
  • GET API to fetch all employees from mysql database. This calls the camel select route.
  • POST API to insert employee details in mysql database. This calls the camel insert route with the Employee details to be inserted.
package com.javainuse.controller;

import java.util.List;

import org.apache.camel.ProducerTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.javainuse.model.Employee;

@RestController
public class EmployeeController {

	@Autowired
	ProducerTemplate producerTemplate;

	@RequestMapping(value = "/employees", method = RequestMethod.GET)
	public List<Employee> getAllEmployees() {
		List<Employee> employees = producerTemplate.requestBody("direct:select", null, List.class);
		return employees;

	}

	@RequestMapping(value = "/employees", consumes = "application/json", method = RequestMethod.POST)
	public boolean insertEmployee(@RequestBody Employee emp) {
		producerTemplate.requestBody("direct:insert", emp, List.class);
		return true;
	}
}
Finally create the Spring Boot Main class with the SpringBootApplication Annotation
package com.javainuse;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootJdbcApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootJdbcApplication.class, args);
	}
}
Start the application.
Send a POST request to insert new employee detail-
Apache Camel Spring Boot POST Request
  Send a GET request to retrieve all employee details-
Apache Camel Spring Boot GET Request
 

Download Source Code

Download it -
Spring Boot + Apache Camel SQL component + MySQL

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