SQL Example
Available as of Camel 2.11
This example is located in the examples/camel-example-sql directory of the Camel distribution.
There is a README.txt file with instructions how to run it.
If you use maven then you can easily compile and install the example from the command line:
About
This example shows how to exchange data using a shared database table.
The example has two Camel routes. The first route insert new data into the table, triggered by a timer to run every 5th second.
The second route pickup the newly inserted rows from the table, process the row(s), and mark the row(s) as processed when done; to avoid picking up the same rows again.
Implementation
In the camel-context.xml file in the src/main/resources/META-INF/spring folder we have the Spring XML file to setup and configure the database, as well the CamelContext.
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="url" value="jdbc:derby:memory:orders;create=true"/>
<property name="username" value=""/>
<property name="password" value=""/>
</bean>
<bean id="initDatabase" class="org.apache.camel.example.sql.DatabaseBean"
init-method="create" destroy-method="destroy">
<property name="dataSource" ref="dataSource"/>
</bean>
<bean id="sql" class="org.apache.camel.component.sql.SqlComponent">
<property name="dataSource" ref="dataSource"/>
</bean>
And then in the same file we setup our Camel application. At first we have a orderBean that we use in the routes to generate new orders and process orders as well.
<bean id="orderBean" class="org.apache.camel.example.sql.OrderBean"/>
<camelContext xmlns="http://camel.apache.org/schema/spring">
<propertyPlaceholder id="placeholder" location="classpath:sql.properties"/>
<route id="generateOrder-route">
<from uri="timer:foo?period=5s"/>
<transform>
<method ref="orderBean" method="generateOrder"/>
</transform>
<to uri="sql:{{sql.insertOrder}}"/>
<log message="Inserted new order ${body[id]}"/>
</route>
<!-- route that process the orders by picking up new rows from the database
and when done processing then update the row to mark it as processed -->
<route id="processOrder-route">
<from uri="sql:{{sql.selectOrder}}?consumer._onConsume_={{sql.markOrder}}"/>
<to uri="bean:orderBean?method=processOrder"/>
<log message="${body}"/>
</route>
</camelContext>
Notice how we have externalized the SQL queries, and use Camels property placeholder to refer to the sql.properties file.
## sql that insert new orders
sql.insertOrder=insert into orders (id, item, amount, description, processed) values (:#id, :#item, :#amount, :#description, false)
## sql that select all unprocessed orders
sql.selectOrder=select * from orders where processed = false
## sql that update the order as being processed
sql.markOrder=update orders set processed = true where id = :#id
Running the example
This example can be run from the command line
Press ctrl + c to stop the example.
Running the example in OSGi such as Apache Karaf / ServiceMix
From Apache Karaf / ServiceMix shell you can install this example. You would need to install Camel first in Apache Karaf.
Camel is installed out of the box in Apache ServiceMix.
features:install camel-example-sql
When the example is running in Karaf / ServiceMix you can see in the log when it processes the orders, using
See Also
