Chapter 326. SQL Component

WARNING:From Camel 2.11 onwards this component can create both consumer (e.g. from()) and producer endpoints (e.g. to()). In previous versions, it could only act as a producer.

INFO:This component can be used as a Transactional Client.

The SQL component uses the following endpoint URI notation:

sql:select * from table where id=# order by name[?options]

From Camel 2.11 onwards you can use named parameters by using :`#name_of_the_parameter` style as shown:

sql:select * from table where id=:#myId order by name[?options]

When using named parameters, Camel will lookup the names from, in the given precedence:
1. From message body if its a java.util.Map
2. From message headers

If a named parameter cannot be resolved, then an exception is thrown.

From Camel 2.14 onward you can use Simple expressions as parameters as shown:

sql:select * from table where id=:#${property.myId} order by name[?options]

Notice that the standard ? symbol that denotes the parameters to an SQL query is substituted with the # symbol, because the ? symbol is used to specify options for the endpoint. The ? symbol replacement can be configured on endpoint basis.

From Camel 2.17 onwards you can externalize your SQL queries to files in the classpath or file system as shown:

sql:classpath:sql/myquery.sql[?options]

And the myquery.sql file is in the classpath and is just a plain text

-- this is a comment
select *
from table
where
  id = :#${property.myId}
order by
  name

In the file you can use multilines and format the SQL as you wish. And also use comments such as the – dash line.

You can append query options to the URI in the following format, ?option=value&option=value&…​

The SQL component supports 3 options, which are listed below.

The SQL endpoint is configured using URI syntax:

sql:query

with the following path and query parameters:

The component supports 4 options, which are listed below.

The SQL component tries to convert the message body to an object of java.util.Iterator type and then uses this iterator to fill the query parameters (where each query parameter is represented by a # symbol (or configured placeholder) in the endpoint URI). If the message body is not an array or collection, the conversion results in an iterator that iterates over only one object, which is the body itself.

For example, if the message body is an instance of java.util.List, the first item in the list is substituted into the first occurrence of # in the SQL query, the second item in the list is substituted into the second occurrence of #, and so on.

If batch is set to true, then the interpretation of the inbound message body changes slightly – instead of an iterator of parameters, the component expects an iterator that contains the parameter iterators; the size of the outer iterator determines the batch size.

From Camel 2.16 onwards you can use the option useMessageBodyForSql that allows to use the message body as the SQL statement, and then the SQL parameters must be provided in a header with the key SqlConstants.SQL_PARAMETERS. This allows the SQL component to work more dynamic as the SQL query is from the message body.

For select operations, the result is an instance of List<Map<String, Object>> type, as returned by the JdbcTemplate.queryForList() method. For update operations, a NULL body is returned as the update operation is only set as a header and never as a body.

By default, the result is placed in the message body. If the outputHeader parameter is set, the result is placed in the header. This is an alternative to using a full message enrichment pattern to add headers, it provides a concise syntax for querying a sequence or some other small value into a header. It is convenient to use outputHeader and outputType together:

from("jms:order.inbox")
    .to("sql:select order_seq.nextval from dual?outputHeader=OrderId&outputType=SelectOne")
    .to("jms:order.booking");

From*Camel 2.18* onwards the producer supports outputType=StreamList that uses an iterator to stream the output of the query. This allows to process the data in a streaming fashion which for example can be used by the Splitter EIP to process each row one at a time, and load data from the database as needed.

from("direct:withSplitModel")
        .to("sql:select * from projects order by id?outputType=StreamList&outputClass=org.apache.camel.component.sql.ProjectModel")
        .to("log:stream")
        .split(body()).streaming()
            .to("log:row")
            .to("mock:result")
        .end();

When performing update operations, the SQL Component stores the update count in the following message headers:

When performing insert operations, the SQL Component stores the rows with the generated keys and number of these rown in the following message headers (Available as of Camel 2.12.4, 2.13.1):

*Available as of Camel 2.12.4, 2.13.1 and 2.14 *

If you insert data using SQL INSERT, then the RDBMS may support auto generated keys. You can instruct the SQL producer to return the generated keys in headers.
To do that set the header CamelSqlRetrieveGeneratedKeys=true. Then the generated keys will be provided as headers with the keys listed in the table above.

You can see more details in this unit test.

You can now set a reference to a DataSource in the URI directly:

sql:select * from table where id=# order by name?dataSource=myDS

In the sample below we execute a query and retrieve the result as a List of rows, where each row is a Map<String, Object> and the key is the column name.

First, we set up a table to use for our sample. As this is based on an unit test, we do it in java:

The SQL script createAndPopulateDatabase.sql we execute looks like as described below:

Then we configure our route and our sql component. Notice that we use a direct endpoint in front of the sql endpoint. This allows us to send an exchange to the direct endpoint with the URI, direct:simple, which is much easier for the client to use than the long sql: URI. Note that the DataSource is looked up up in the registry, so we can use standard Spring XML to configure our DataSource.

And then we fire the message into the direct endpoint that will route it to our sql component that queries the database.

We could configure the DataSource in Spring XML as follows:

 <jee:jndi-lookup id="myDS" jndi-name="jdbc/myDataSource"/>

   from("direct:projects")
     .setHeader("lic", constant("ASF"))
     .setHeader("min", constant(123))
     .to("sql:select * from projects where license = :#lic and id > :#min order by id")

   from("direct:projects")
     .to("sql:select * from projects where license = :#lic and id > :#min order by id")

Available as of Camel 2.14

In the given route below, we want to get all the project from the database. It uses the body of the exchange for defining the license and uses the value of a property as the second parameter.

from("direct:projects")
  .setBody(constant("ASF"))
  .setProperty("min", constant(123))
  .to("sql:select * from projects where license = :#${body} and id > :#${property.min} order by id")

from("sql:select * from projects where id = :#${bean:myIdGenerator.nextId}")
    .to("mock:result");

public static class MyIdGenerator {

    private int id = 1;

    public int nextId() {
        return id++;
    }

Available as of Camel 2.17

From Camel 2.17 onwards the SQL producer allows to use SQL queries with IN statements where the IN values is dynamic computed. For example from the message body or a header etc.

To use IN you need to:

An example explains this better. The following query is used:

-- this is a comment
select *
from projects
where project in (:#in:names)
order by id

In the following route:

from("direct:query")
    .to("sql:classpath:sql/selectProjectsIn.sql")
    .to("log:query")
    .to("mock:query");

Then the IN query can use a header with the key names with the dynamic values such as:

// use an array
template.requestBodyAndHeader("direct:query", "Hi there!", "names", new String[]{"Camel", "AMQ"});

// use a list
List<String> names = new ArrayList<String>();
names.add("Camel");
names.add("AMQ");

template.requestBodyAndHeader("direct:query", "Hi there!", "names", names);

// use a string separated values with comma
template.requestBodyAndHeader("direct:query", "Hi there!", "names", "Camel,AMQ");

The query can also be specified in the endpoint instead of being externalized (notice that externalizing makes maintaining the SQL queries easier)

from("direct:query")
    .to("sql:select * from projects where project in (:#in:names) order by id")
    .to("log:query")
    .to("mock:query");

Available as of Camel 2.7: In this section we will use the JDBC based idempotent repository.

TIP:*Abstract class* From Camel 2.9 onwards there is an abstract class org.apache.camel.processor.idempotent.jdbc.AbstractJdbcMessageIdRepository you can extend to build custom JDBC idempotent repository.

First we have to create the database table which will be used by the idempotent repository. For Camel 2.7, we use the following schema:

CREATE TABLE CAMEL_MESSAGEPROCESSED ( processorName VARCHAR(255),
messageId VARCHAR(100) )

In Camel 2.8, we added the createdAt column:

CREATE TABLE CAMEL_MESSAGEPROCESSED ( processorName VARCHAR(255),
messageId VARCHAR(100), createdAt TIMESTAMP )

WARNING:The SQL Server TIMESTAMP type is a fixed-length binary-string type. It does not map to any of the JDBC time types: DATE, TIME, or TIMESTAMP.

Customize the JdbcMessageIdRepository

Starting with Camel 2.9.1 you have a few options to tune the org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository for your needs:

Available as of Camel 2.6

INFO: Using JdbcAggregationRepository in Camel 2.6

In Camel 2.6, the JdbcAggregationRepository is provided in the camel-jdbc-aggregator component. From Camel 2.7 onwards, the JdbcAggregationRepository is provided in the camel-sql component.

JdbcAggregationRepository is an AggregationRepository which on the fly persists the aggregated messages. This ensures that you will not loose messages, as the default aggregator will use an in memory only AggregationRepository. The JdbcAggregationRepository allows together with Camel to provide persistent support for the Aggregator.

Only when an Exchange has been successfully processed it will be marked as complete which happens when the confirm method is invoked on the AggregationRepository. This means if the same Exchange fails again it will be kept retried until it success.

You can use option maximumRedeliveries to limit the maximum number of redelivery attempts for a given recovered Exchange. You must also set the deadLetterUri option so Camel knows where to send the Exchange when the maximumRedeliveries was hit.

You can see some examples in the unit tests of camel-sql, for example this test.

CREATE TABLE aggregation (
 id varchar(255) NOT NULL,
 exchange blob NOT NULL,
 constraint aggregation_pk PRIMARY KEY (id)
);
CREATE TABLE aggregation_completed (
 id varchar(255) NOT NULL,
 exchange blob NOT NULL,
 constraint aggregation_completed_pk PRIMARY KEY (id)
);

Available as of Camel 2.11

You can configure the JdbcAggregationRepository to store message body and select(ed) headers as String in separate columns. For example to store the body, and the following two headers companyName and accountName use the following SQL:

CREATE TABLE aggregationRepo3 (
 id varchar(255) NOT NULL,
 exchange blob NOT NULL,
 body varchar(1000),
 companyName varchar(1000),
 accountName varchar(1000),
 constraint aggregationRepo3_pk PRIMARY KEY (id)
);
CREATE TABLE aggregationRepo3_completed (
 id varchar(255) NOT NULL,
 exchange blob NOT NULL,
 body varchar(1000),
 companyName varchar(1000),
 accountName varchar(1000),
 constraint aggregationRepo3_completed_pk PRIMARY KEY (id)
);

And then configure the repository to enable this behavior as shown below:

<bean id="repo3"
  class="org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository">
  <property name="repositoryName" value="aggregationRepo3"/>
  <property name="transactionManager" ref="txManager3"/>
  <property name="dataSource" ref="dataSource3"/>
  <!-- configure to store the message body and following headers as text in the repo -->
  <property name="storeBodyAsText" value="true"/>
  <property name="headersToStoreAsText">
    <list>
      <value>companyName</value>
      <value>accountName</value>
    </list>
  </property>
</bean>

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler">
  <property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
</bean>
<bean id="nativeJdbcExtractor"
  class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"/>
<bean id="repo"
  class="org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository">
  <property name="transactionManager" ref="transactionManager"/>
  <property name="repositoryName" value="aggregation"/>
  <property name="dataSource" ref="dataSource"/>
  <!-- Only with Oracle, else use default -->
  <property name="lobHandler" ref="lobHandler"/>
</bean>

<bean id="repo"
class="org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository">
  <property name="transactionManager" ref="transactionManager"/>
  <property name="repositoryName" value="aggregation"/>
  <property name="dataSource" ref="dataSource"/>
  <property name="jdbcOptimisticLockingExceptionMapper" ref="myExceptionMapper"/>
</bean>
<!-- use the default mapper with extraFQN class names from our JDBC driver -->
<bean id="myExceptionMapper" class="org.apache.camel.processor.aggregate.jdbc.DefaultJdbcOptimisticLockingExceptionMapper">
  <property name="classNames">
    <util:set>
      <value>com.foo.sql.MyViolationExceptoion</value>
      <value>com.foo.sql.MyOtherViolationExceptoion</value>
    </util:set>
  </property>
</bean>

JdbcAggregationRepository uses two distinct transaction templates from Spring-TX. One is read-only and one is used for read-write operations.

However, when using JdbcAggregationRepository within a route that itself uses <transacted /> and there’s common PlatformTransactionManager used, there may be a need to configure propagation behavior used by transaction templates inside JdbcAggregationRepository.

Here’s a way to do it:

<bean id="repo"
class="org.apache.camel.processor.aggregate.jdbc.JdbcAggregationRepository">
  <property name="propagationBehaviorName" value="PROPAGATION_NESTED" />
</bean>

Propagation is specified by constants of org.springframework.transaction.TransactionDefinition interface, so propagationBehaviorName is convenient setter that allows to use names of the constants.

There’s special database that may cause problems with optimistic locking used by JdbcAggregationRepository. PostgreSQL marks connection as invalid in case of data integrity violation exception (the one with SQLState 23505). This makes the connection effectively unusable within nested transaction. Details can be found in this document.

org.apache.camel.processor.aggregate.jdbc.PostgresAggregationRepository extends JdbcAggregationRepository and uses special INSERT .. ON CONFLICT .. statement to provide optimistic locking behavior.

This statement is (with default aggregation table definition):

INSERT INTO aggregation (id, exchange) values (?, ?) ON CONFLICT DO NOTHING

Details can be found in PostgreSQL documentation.

When this clause is used, java.sql.PreparedStatement.executeUpdate() call returns 0 instead of throwing SQLException with SQLState=23505. Further handling is exactly the same as with generic JdbcAggregationRepository, but without marking PostgreSQL connection as invalid.

A starter module is available to spring-boot users. When using the starter, the DataSource can be directly configured using spring-boot properties.

# Example for a mysql datasource
spring.datasource.url=jdbc:mysql://localhost/test
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

To use this feature, add the following dependencies to your spring boot pom.xml file:

<dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-sql-starter</artifactId>
    <version>${camel.version}</version> <!-- use the same version as your Camel core version -->
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>${spring-boot-version}</version>
</dependency>

You should also include the specific database driver, if needed.