Apache Camel: JDBC & SQL Components

The Camel JDBC component executes SQL statements using a DataSource from the registry, placing query results in the message body. The SQL component is a higher-level variant that supports named parameters, stored procedures, and batch processing. Both integrate naturally with Spring Boot's auto-configured DataSource.

Apache Camel

Setup & Dependencies
JDBC Component: SELECT Query
JDBC Component: INSERT / UPDATE
SQL Component with Named Parameters
SQL Stored Procedures
Polling a Database Table
JPA Component Overview

Setup & Dependencies

Add the JDBC and SQL starters alongside a JDBC driver. Spring Boot auto-configures a DataSource from application.properties.

XML

<dependency>
    <groupId>org.apache.camel.springboot</groupId>
    <artifactId>camel-jdbc-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.camel.springboot</groupId>
    <artifactId>camel-sql-starter</artifactId>
</dependency>

<!-- H2 for testing; replace with postgres / mysql driver in production -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Properties

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

JDBC Component: SELECT Query

Set the SQL query as the message body and send it to the JDBC endpoint. The component executes the query and replaces the body with a List<Map<String,Object>> of rows.

Java

import org.apache.camel.builder.RouteBuilder;
import org.springframework.stereotype.Component;

@Component
public class OrderQueryRoute extends RouteBuilder {

    @Override
    public void configure() {

        from("timer:poll?period=30000") // every 30 seconds
            .setBody(constant("SELECT id, product, status FROM orders WHERE status = 'PENDING'"))
            .to("jdbc:dataSource")      // 'dataSource' = Spring bean name
            .log("Pending orders: ${body.size}")
            .split(body())
                .log("Order: ${body}"); // each body is a Map<String,Object>
    }
}

The JDBC component endpoint uses the Spring bean name of the DataSource. With Spring Boot auto-configuration the bean is named dataSource by default.

JDBC Component: INSERT / UPDATE

For DML statements, set the SQL as the body and send to the JDBC endpoint. The component returns the update count in the header CamelJdbcUpdateCount.

Java

from("direct:createOrder")
    .process(exchange -> {
        Order order = exchange.getIn().getBody(Order.class);
        String sql = String.format(
            "INSERT INTO orders (id, product, quantity, status) VALUES ('%s','%s',%d,'%s')",
            order.getId(), order.getProduct(), order.getQuantity(), "CREATED");
        exchange.getIn().setBody(sql);
    })
    .to("jdbc:dataSource")
    .log("Rows inserted: ${header.CamelJdbcUpdateCount}");

Avoid building SQL strings with string concatenation in production: use the SQL component with named parameters to prevent SQL injection.

SQL Component with Named Parameters

The SQL component accepts a query in the endpoint URI and reads parameters from the message headers or body map, preventing SQL injection through prepared statements.

Java

from("direct:getOrderById")
    // headers provide named parameters — :?id matches header 'id'
    .to("sql:SELECT * FROM orders WHERE id = :#${header.orderId}?dataSource=dataSource")
    .log("Result: ${body}");

// INSERT with named parameters from a Map body
from("direct:insertOrder")
    .process(exchange -> {
        Map<String, Object> params = new HashMap<>();
        params.put("id", UUID.randomUUID().toString());
        params.put("product", "Laptop");
        params.put("qty", 2);
        params.put("status", "PENDING");
        exchange.getIn().setBody(params);
    })
    .to("sql:INSERT INTO orders (id, product, quantity, status) " +
        "VALUES (:#id, :#product, :#qty, :#status)?dataSource=dataSource");

SQL Stored Procedures

Call stored procedures with the SQL component using the callablestatement option. Input/output parameters are mapped via a List body.

Java

// Calling a stored procedure: CALL update_order_status(?, ?)
from("direct:updateStatus")
    .process(exchange -> {
        List<Object> params = new ArrayList<>();
        params.add(exchange.getIn().getHeader("orderId"));
        params.add("COMPLETED");
        exchange.getIn().setBody(params);
    })
    .to("sql:CALL update_order_status(:#${body[0]}, :#${body[1]})"
      + "?dataSource=dataSource&batch=false");

Polling a Database Table

Use the SQL consumer to continuously poll a table for new rows. The consumer.delay option sets the interval; consumer.onConsume updates the row so it isn't processed again.

Java

from("sql:SELECT * FROM orders WHERE status = 'NEW'"
    + "?dataSource=dataSource"
    + "&consumer.delay=5000"                              // poll every 5 s
    + "&consumer.onConsume=UPDATE orders SET status='PROCESSING' WHERE id = :#id")
    .log("Processing order: ${body}")
    .bean(OrderService.class, "process");

The consumer.onConsume SQL runs for each row consumed. It uses the row's own columns as parameters (e.g. :#id maps to the id column value), marking rows as processed atomically.

JPA Component Overview

The Camel JPA component reads and writes JPA-managed entities. It works with Spring-managed EntityManagerFactory beans and supports both consumer (polling) and producer (persist/merge) patterns.

XML

<dependency>
    <groupId>org.apache.camel.springboot</groupId>
    <artifactId>camel-jpa-starter</artifactId>
</dependency>

Java

// Consumer: poll for new OrderEntity rows
from("jpa:com.example.OrderEntity"
    + "?persistenceUnit=default"
    + "&consumer.delay=10000"
    + "&query=SELECT o FROM OrderEntity o WHERE o.status = 'NEW'")
    .log("New JPA order: ${body.id}")
    .bean(OrderService.class, "process");

// Producer: persist a new entity
from("direct:saveOrder")
    .to("jpa:com.example.OrderEntity");   // persists body as entity

Contents

Setup & Dependencies

JDBC Component: SELECT Query

JDBC Component: INSERT / UPDATE

SQL Component with Named Parameters

SQL Stored Procedures

Polling a Database Table

JPA Component Overview

Related articles