[[ElSql-ElSqlComponent]]
ElSql Component
~~~~~~~~~~~~~~~

*Available as of Camel 2.16*

The *elsql:* component is an extension to the existing
link:sql-component.html[SQL Component] that uses
https://github.com/OpenGamma/ElSql[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:

[source,xml]
------------------------------------------------------------
<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>
------------------------------------------------------------

[Info]
====
This component can be used as a
http://camel.apache.org/transactional-client.html[Transactional Client].
====

The SQL component uses the following endpoint URI notation:

[source,java]
-----------------------------------
sql: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 link:simple.html[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.

[[ElSql-Options]]
Options
^^^^^^^

[width="100%",cols="10%,10%,10%,70%",options="header",]
|=======================================================================
|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
http://java.sun.com/j2se/1.5.0/docs/api/java/util/concurrent/ScheduledExecutorService.html[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 link:exchange.html[Exchange] if there
was no data to poll.

|`consumer.onConsume` |`String` |`null` |After processing each row then this query can be executed, if the
link:exchange.html[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
link:exchange.html[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.
The 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
|=======================================================================

[[ElSql-Resultofthequery]]
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:

[[ElSql-Headervalues]]
Header values
^^^^^^^^^^^^^

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

[width="100%",cols="10%,90%",options="header",]
|=======================================================================
|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.
|=======================================================================

[[ElSql-Sample]]
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:

[source,java]
-----------------------------------------------
   from("direct:projects")
     .setHeader("lic", constant("ASF"))
     .setHeader("min", constant(123))
     .to("elsql:projects:com/foo/orders.elsql")
-----------------------------------------------

And the https://github.com/OpenGamma/ElSql[elsql] mapping file

[source,java]
------------------------------------
@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.

[source,java]
-----------------------------------------------
   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:

[source,java]
------------------------------------------------------------
@NAME(projects)
  SELECT *
  FROM projects
  WHERE license = :${body.license} AND id > :${body.minimum}
  ORDER BY id
------------------------------------------------------------

[[ElSql-SeeAlso]]
See Also
^^^^^^^^

* link:configuring-camel.html[Configuring Camel]
* link:component.html[Component]
* link:endpoint.html[Endpoint]
* link:getting-started.html[Getting Started]

* link:sql-component.html[SQL Component]
* link:mybatis.html[MyBatis]
* link:jdbc.html[JDBC]

