Red Hat Training

A Red Hat training course is available for Red Hat Fuse

Chapter 44. ElSql

ElSql Component

Available as of Camel 2.16
The elsql: component is an extension to the existing SQL Component that uses ElSql to define the SQL queries.
This component uses spring-jdbc behind the scenes for the actual SQL handling.
Maven users will need to add the following dependency to their pom.xml for this component: 
<dependency>
    <groupId>org.apache.camel</groupId>
    <artifactId>camel-elsql</artifactId>
    <version>x.x.x</version>
    <!-- use the same version as your Camel core version -->
</dependency>
Note
This component can be used as a Transactional Client.
The ElSql component uses the following endpoint URI notation: 
elsql:elSqlName:resourceUri[?options]
You can append query options to the URI in the following format, ?option=value&option=value&...
The parameters to the SQL queries are named parameters in the elsql mapping files, and maps to corresponding keys from the Camel message, in the given precedence:
1. Camel 2.16.1: from message body if Simple expression.
2. from message body if its a java.util.Map
3. from message headers
If a named parameter cannot be resolved, then an exception is thrown.

Options

Option
Type
Default
Description
resourceUri String null Required The resource file which contains the elsql SQL statements to use. You can specify multiple resources separated by comma. The resources are loaded on the classpath by default, you can prefix with file: to load from file system. Notice you can set this option on the component and then you do not have to configure this on the endpoint.
elSqlConfig null To use a specific configured ElSqlConfig. It may be better to use the databaseVendor option instead.
databaseVendor Default To use a vendor specific ElSqlConfig. The possible values are: Default, Postgres, HSql, MySql, Oracle, SqlServer2008, Veritca
batch
boolean
false
Execute SQL batch update statements. See notes below on how the treatment of the inbound message body changes if this is set to true.
dataSource
String
null
Reference to a DataSource to look up in the registry.
template.<xxx>
null
Sets additional options on the Spring NamedParameterJdbcTemplate that is used behind the scenes to execute the queries. For instance, template.maxRows=10. For detailed documentation, see the NamedParameterJdbcTemplate javadoc documentation.
consumer.delay
long
500
Delay in milliseconds between each poll.
consumer.initialDelay
long
1000
Milliseconds before polling starts.
consumer.useFixedDelay
boolean
false
Set to true to use fixed delay between polls, otherwise fixed rate is used. See ScheduledExecutorService in JDK for details.
maxMessagesPerPoll
int
0
An integer value to define the maximum number of messages to gather per poll. By default, no maximum is set.
consumer.useIterator
boolean
true
If true each row returned when polling will be processed individually. If false the entire java.util.List of data is set as the IN body.
consumer.routeEmptyResultSet
boolean
false
Whether to route a single empty Exchange if there was no data to poll.
consumer.onConsume
String
null
After processing each row then this query can be executed, if the Exchange was processed successfully, for example to mark the row as processed. The query can have parameter.
consumer.onConsumeFailed
String
null
After processing each row then this query can be executed, if the Exchange failed, for example to mark the row as failed. The query can have parameter.
consumer.onConsumeBatchComplete
String
null
After processing the entire batch, this query can be executed to bulk update rows etc. The query cannot have parameters.
consumer.breakBatchOnConsumeFail
boolean
false
If using consumer.onConsume and it fails, then this option controls whether to break out of the batch or continue processing the next row from the batch.
outputType
String
SelectList
Make the output of consumer or producer to SelectList as List of Map, or SelectOne as single Java object in the following way: a) If the query has only single column, then that JDBC Column object is returned. (such as SELECT COUNT( * ) FROM PROJECT will return a Long object. b) If the query has more than one column, then it will return a Map of that result. c) If the outputClass is set, then it will convert the query result into an Java bean object by calling all the setters that match the column names. It will assume your class has a default constructor to create instance with. d) If the query resulted in more than one rows, it throws an non-unique result exception.
Tthe SelectList also supports mapping each row to a Java object as the SelectOne does (only step c).
outputClass
String
null
Specify the full package and class name to use as conversion when outputType=SelectOne.
outputHeader
String null
To store the result as a header instead of the message body. This allows to preserve the existing message body as-is.
noop
boolean
false
If set, will ignore the results of the SQL query and use the existing IN message as the OUT message for the continuation of processing
transacted boolean false Camel 2.16.2: SQL consumer only:Enables or disables transaction. If enabled then if processing an exchange failed then the consumer break out processing any further exchanges to cause a rollback eager

Result of the query

For select operations, the result is an instance of List<Map<String, Object>> type, as returned by the JdbcTemplate.queryForList() method. For update operations, the result is the number of updated rows, returned as an Integer.
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:

Header values

When performing update operations, the SQL Component stores the update count in the following message headers:
Header
Description
CamelSqlUpdateCount
The number of rows updated for update operations, returned as an Integer object.
CamelSqlRowCount
The number of rows returned for select operations, returned as an Integer object.

Sample

In the given route below, we want to get all the projects from the projects table. Notice the SQL query has 2 named parameters, :#lic and :#min.
Camel will then lookup for these parameters from the message body or message headers. Notice in the example above we set two headers with constant value for the named parameters: 
   from("direct:projects")
     .setHeader("lic", constant("ASF"))
     .setHeader("min", constant(123))
     .to("elsql:projects:com/foo/orders.elsql")
And the elsql mapping file 
@NAME(projects)
  SELECT *
  FROM projects
  WHERE license = :lic AND id > :min
  ORDER BY id
Though if the message body is a java.util.Map then the named parameters will be taken from the body. 
   from("direct:projects")
     .to("elsql:projects:com/foo/orders.elsql")
In from Camel 2.16.1 onwards you can use Simple expressions as well, which allows to use an OGNL like notation on the message body, where it assumes to have getLicense and getMinimum methods: 
@NAME(projects)
  SELECT *
  FROM projects
  WHERE license = :${body.license} AND id > :${body.minimum}
  ORDER BY id

See also

  • SQL Component
  • MyBatis
  • JDBC