JDBC

jdbc:dataSourceName[?options]

jdbc:dataSourceName

  from("direct:projects")
     .setHeader("lic", constant("ASF"))
     .setHeader("min", constant(123))
     .setBody("select * from projects where license = :?lic and id > :?min order by id")
     .to("jdbc:myDataSource?useHeadersAsParameters=true")

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
                           http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://camel.apache.org/schema/spring
                           http://camel.apache.org/schema/spring/camel-spring.xsd
                           http://www.springframework.org/schema/jdbc
                           http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
                           http://activemq.apache.org/schema/core
                           http://activemq.apache.org/schema/core/activemq-core.xsd">

  <!--  PROPERTY PLACEHOLDERS  -->
  <!--
       You may use property placeholders data store configuration
       for to do that, you can resolve this properties with srping
  -->
  <bean id="db" class="org.apache.camel.spring.spi.BridgePropertyPlaceholderConfigurer">
    <property name="location" value="classpath:db.properties"/>
  </bean>

  <!-- DATA STORE CONFIGURATION -->

  <!--
       In this example the DataStore is a postgres database, you can change the jdbc connector.
       Also, the property testWhileIdle and validationQuery keep connection open.
  -->

  <bean id="DataStore" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://${DB_HOST}/${DB_NAME}"/>
    <property name="username" value="${DB_USER}" />
    <property name="password" value="${DB_PASS}" />
    <property name="initialSize" value="4"/>
    <property name="maxActive" value="15"/>
    <property name="maxIdle" value="16"/>
    <property name="minIdle" value="8"/>
    <property name="timeBetweenEvictionRunsMillis" value="1800"/>
    <property name="minEvictableIdleTimeMillis" value="1800"/>
    <property name="testOnBorrow" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="testOnReturn" value="true"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="maxWait"  value="1000"/>
    <property name="removeAbandoned" value="true"/>
    <property name="logAbandoned" value="true"/>
    <property name="removeAbandonedTimeout" value="30000"/>
  </bean>

  <!-- You shuould write a file with an script to initialize your database.
  If you have nothing to do with your database, then you can write just a
  validation query like 'SELECT *; '-->
  <jdbc:initialize-database data-source="DataStore" enabled="true">
    <jdbc:script location="classpath:datastore-schema.sql" />
  </jdbc:initialize-database>

  <camelContext xmlns="http://camel.apache.org/schema/spring">

    <!-- CAMEL ROUTE -->
    <route id="Reporter">
      <from uri="direct:to-datastore"/>
      <!-- this example is done with camel-jdbc but you can make it work with camel-sql -->
      <setBody>
        <simple>insert into test '${body[value]}'</simple>
      </setBody>
      <to uri="jdbc:DataStore"/>
    </route>
</camelContext>

from("direct:hello")
// here we split the data from the testdb into new messages one by one
// so the mock endpoint will receive a message per row in the table
// the StreamList option allows to stream the result of the query without creating a List of rows
// and notice we also enable streaming mode on the splitter
.to("jdbc:testdb?outputType=StreamList")
  .split(body()).streaming()
  .to("mock:result");

from("timer://foo?period=60000")
  .setBody(constant("select * from customer"))
  .to("jdbc:testdb")
  .to("activemq:queue:customers");

from("timer://MoveNewCustomersEveryHour?period=3600000")
    .setBody(constant("select * from customer where create_time > (sysdate-1/24)"))
    .to("jdbc:testdb")
    .split(body())
        .process(new MyCustomerProcessor()) //filter/transform results as needed
        .setBody(simple("insert into processed_customer values('${body[ID]}','${body[NAME]}')"))
        .to("jdbc:testdb");