...
| Div |
|---|
| class | confluenceTableSmall |
|---|
|
Option | Type | Default | Description |
|---|
batch
| boolean
| false
| Camel 2.7.5, 2.8.4 and 2.9: Execute SQL batch update statements. See notes below on how the treatment of the inbound message body changes if this is set to true. | dataSourceRef
| String
| null
| Deprecated and will be removed in Camel 3.0: Reference to a DataSource to look up in the registry. Use dataSource=#theName instead. | dataSource
| String
| null
| Camel 2.11: Reference to a DataSource to look up in the registry. | placeholder
| String
| #
| Camel 2.4: Specifies a character that will be replaced to ? in SQL query. Notice, that it is simple String.replaceAll() operation and no SQL parsing is involved (quoted strings will also change). This replacement is only happening if the endpoint is created using the SqlComponent. If you manually create the endpoint, then use the expected ? sign instead. | usePlaceholder | boolean | true | Camel 2.17: Sets whether to use placeholder and replace all placeholder characters with ? sign in the SQL queries. | template.<xxx>
| | null
| Sets additional options on the Spring JdbcTemplate that is used behind the scenes to execute the queries. For instance, template.maxRows=10. For detailed documentation, see the JdbcTemplate javadoc documentation. | allowNamedParameters
| boolean
| true
| Camel 2.11: Whether to allow using named parameters in the queries. | processingStrategy
| | | Camel 2.11: SQL consumer only: Allows to plugin to use a custom org.apache.camel.component.sql.SqlProcessingStrategy to execute queries when the consumer has processed the rows/batch. | prepareStatementStrategy
| | | Camel 2.11: Allows to plugin to use a custom org.apache.camel.component.sql.SqlPrepareStatementStrategy to control preparation of the query and prepared statement. | consumer.delay
| long
| 500
| Camel 2.11: SQL consumer only: Delay in milliseconds between each poll. | consumer.initialDelay
| long
| 1000
| Camel 2.11: SQL consumer only: Milliseconds before polling starts. | consumer.useFixedDelay
| boolean
| false
| Camel 2.11: SQL consumer only: Set to true to use fixed delay between polls, otherwise fixed rate is used. See ScheduledExecutorService in JDK for details. | maxMessagesPerPoll
| int
| 0
| Camel 2.11: SQL consumer only: An integer value to define the maximum number of messages to gather per poll. By default, no maximum is set. | useIterator
| boolean
| true
| Camel 2.11: SQL consumer only: 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. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | routeEmptyResultSet
| boolean
| false
| Camel 2.11: SQL consumer only: Whether to route a single empty Exchange if there was no data to poll. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | onConsume
| String
| null
| Camel 2.11: SQL consumer only: 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. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | onConsumeFailed
| String
| null
| Camel 2.11: SQL consumer only: 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. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | onConsumeBatchComplete
| String
| null
| Camel 2.11: SQL consumer only: After processing the entire batch, this query can be executed to bulk update rows etc. The query cannot have parameters. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | expectedUpdateCount
| int
| -1
| Camel 2.11: SQL consumer only: If using consumer.onConsume then this option can be used to set an expected number of rows being updated. Typically you may set this to 1 to expect one row to be updated. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | breakBatchOnConsumeFail
| boolean
| false
| Camel 2.11: SQL consumer only: 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. Notice in Camel 2.15.x or older you need to prefix this option with consumer., eg consumer.useIterator=true. | alwaysPopulateStatement
| boolean
| false
| Camel 2.11: SQL producer only: If enabled then the populateStatement method from org.apache.camel.component.sql.SqlPrepareStatementStrategy is always invoked, also if there is no expected parameters to be prepared. When this is false then the populateStatement is only invoked if there is 1 or more expected parameters to be set; for example this avoids reading the message body/headers for SQL queries with no parameters. | separator
| char
| ,
| Camel 2.11.1: The separator to use when parameter values is taken from message body (if the body is a String type), to be inserted at # placeholders. Notice if you use named parameters, then a Map type is used instead. | outputType
| String
| SelectList
| Camel 2.12.0: 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. From Camel 2.14.1 onwards the SelectList also supports mapping each row to a Java object as the SelectOne does (only step c). From Camel 2.18 onwards there is a new StreamList outputType that streams the result of the query using an Iterator. It can be used with the Splitter EIP in streaming mode to process the ResultSet in streaming fashion. This StreamList do not support batch mode, but you can use outputClass to map each row to a class. | outputClass
| String
| null
| Camel 2.12.0: Specify the full package and class name to use as conversion when outputType=SelectOne. | outputHeader
| String | null | Camel 2.15: To store the result as a header instead of the message body. This allows to preserve the existing message body as-is. | parametersCount
| int
| 0
| Camel 2.11.2/2.12.0 If set greater than zero, then Camel will use this count value of parameters to replace instead of querying via JDBC metadata API. This is useful if the JDBC vendor could not return correct parameters count, then user may override instead. | noop
| boolean
| false
| Camel 2.12.0 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 | useMessageBodyForSql | boolean | false | Camel 2.16: Whether to use the message body as the SQL and then headers for parameters. If this option is enabled then the SQL in the uri is not used. The SQL parameters must then be provided in a header with the key CamelSqlParameters. This option is only for the producer. | 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 |
|
...
Available as of Camel 2.7: In this section we will use the JDBC based idempotent repository.
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 )
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.
...
Second we need to setup a javax.sql.DataSource in the spring XML file:
{snippet:id=e1|lang=xml|url=camel/trunk/components/camel-sql/src/test/resources/org/apache/camel/processor/idempotent/jdbc/spring.xml}
...
And finally we can create our JDBC idempotent repository in the spring XML file as well:
{snippet:id=e2|lang=xml|url=camel/trunk/components/camel-sql/src/test/resources/org/apache/camel/processor/idempotent/jdbc/spring.xml}
...
A customized org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository could look like:
{snippet:id=e1|lang=xml|url=camel/trunk/components/camel-sql/src/test/resources/org/apache/camel/processor/idempotent/jdbc/customized-spring.xml}
...
| Info |
|---|
| title | 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.
...
Here is the SQL query used to create the tables, just replace "aggregation" with your aggregator repository name.
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) );
...
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>
...
Here is the declaration for Oracle:
<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>
...
Here is an example, where we define 2 extra FQN class names from the JDBC vendor.
<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 extra FQN 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>
...
