= ElSQL Component
:doctitle: ElSQL
:shortname: elsql
:artifactid: camel-elsql
:description: Use ElSql to define SQL queries. Extends the SQL Component.
:since: 2.16
:supportlevel: Stable
:component-header: Both producer and consumer are supported
//Manually maintained attributes
:camel-spring-boot-name: elsql

*Since Camel {since}*

*{component-header}*

The ELSQL component is an extension to the existing
xref:sql-component.adoc[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.

This component can be used as a
xref:eips:transactional-client.adoc[Transactional Client].

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>
----

The SQL component uses the following endpoint URI notation:

----
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. from message body if xref:languages:simple-language.adoc[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.

// component-configure options: START

// component-configure options: END

// component options: START
include::partial$component-configure-options.adoc[]
include::partial$component-endpoint-options.adoc[]
// component options: END

// endpoint options: START

// endpoint options: END


== 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:

[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.
|===

=== 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,sql]
----
@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")
----

== Using expression parameters in producers

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,sql]
----
@NAME(projects)
  SELECT *
  FROM projects
  WHERE license = :${body.license} AND id > :${body.minimum}
  ORDER BY id
----

=== Using expression parameters in consumers

When using the ElSql component as consumer, you can now also use expression parameters (simple language)
to build dynamic query parameters, such as calling a method on a bean to retrieve an id, date or something.

For example in the sample below we call the nextId method on the bean myIdGenerator:

[source,sql]
----
@NAME(projectsByIdBean)
  SELECT *
  FROM projects
  WHERE id = :${bean#myIdGenerator.nextId}
----

IMPORTANT: Notice in the bean syntax above, we must use `#` instead of `:` in the simple expression.
This is because Spring query parameter parser is in-use which will separate parameters on colon.
Also pay attention that Spring query parser will invoke the bean twice for each query.

And the bean has the following method:

[source,java]
----
public static class MyIdGenerator {

    private int id = 1;

    public int nextId() {
        // spring will call this twice, one for initializing query and 2nd for actual value
        id++;
        return id / 2;
    }
----

Notice that there is no existing `Exchange` with message body and headers, so
the simple expression you can use in the consumer are most useable for calling
bean methods as in this example.



include::spring-boot:partial$starter.adoc[]
