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

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

Search Tutorials

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

Popular Posts

See Also