ElSql Component
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>
This component can be used as a Transactional Client.
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 its a java.util.Map
2. 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. 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 | |
|
|
| Execute SQL batch update statements. See notes below on how the treatment of the inbound message body changes if this is set to |
|
|
| Reference to a |
|
|
| Sets additional options on the Spring NamedParameterJdbcTemplate that is used behind the scenes to execute the queries. For instance, |
|
|
| Delay in milliseconds between each poll. |
|
|
| Milliseconds before polling starts. |
|
|
| Set to |
|
|
| An integer value to define the maximum number of messages to gather per poll. By default, no maximum is set. |
|
|
| If |
|
|
| Whether to route a single empty Exchange if there was no data to poll. |
|
|
| 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. |
|
|
| 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. |
|
|
| After processing the entire batch, this query can be executed to bulk update rows etc. The query cannot have parameters. |
|
|
| If using |
|
|
| Make the output of consumer or producer to Tthe SelectList also supports mapping each row to a Java object as the SelectOne does (only step c). |
|
|
| Specify the full package and class name to use as conversion when |
| String | null | To store the result as a header instead of the message body. This allows to preserve the existing message body as-is. |
|
|
| 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 |
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 |
---|---|
| The number of rows updated for |
| The number of rows returned for |
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")
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")