Chapter 165. SQL Component

public class DatasourceProducer {
    @Resource(name = "java:jboss/datasources/ExampleDS")
    DataSource dataSource;

    @Produces
    @Named("wildFlyExampleDS")
    public DataSource getDataSource() {
        return dataSource;
    }
}

@ApplicationScoped
@ContextName("camel-sql-cdi-context")
@Startup
public class CdiRouteBuilder extends RouteBuilder {
    @Override
    public void configure() throws Exception {
        from("sql:select name from information_schema.users?dataSource=wildFlyExampleDS")
        .to("direct:end");
    }
}

sql:select * from table where id=# order by name[?options]

sql:select * from table where id=:#myId order by name[?options]

sql:select * from table where id=:#${property.myId} order by name[?options]

sql:classpath:sql/myquery.sql[?options]

-- this is a comment
select *
from table
where
  id = :#${property.myId}
order by
  name

from("jms:order.inbox")
	.to("sql:select order_seq.nextval from dual?outputHeader=OrderId&outputType=SelectOne")
	.to("jms:order.booking");

sql:select * from table where id=# order by name?dataSourceRef=myDS

// this is the database we create with some initial data for our unit test
db = new EmbeddedDatabaseBuilder()
    .setType(EmbeddedDatabaseType.DERBY).addScript("sql/createAndPopulateDatabase.sql").build();

create table projects (id integer primary key, project varchar(10), license varchar(5));
insert into projects values (1, 'Camel', 'ASF');
insert into projects values (2, 'AMQ', 'ASF');
insert into projects values (3, 'Linux', 'XXX');

from("direct:simple")
    .to("sql:select * from projects where license = # order by id?dataSourceRef=jdbc/myDataSource")
    .to("mock:result");

MockEndpoint mock = getMockEndpoint("mock:result");
mock.expectedMessageCount(1);

// send the query to direct that will route it to the sql where we will execute the query
// and bind the parameters with the data from the body. The body only contains one value
// in this case (XXX) but if we should use multi values then the body will be iterated
// so we could supply a List<String> instead containing each binding value.
template.sendBody("direct:simple", "XXX");

mock.assertIsSatisfied();

// the result is a List
List<?> received = assertIsInstanceOf(List.class, mock.getReceivedExchanges().get(0).getIn().getBody());

// and each row in the list is a Map
Map<?, ?> row = assertIsInstanceOf(Map.class, received.get(0));

// and we should be able the get the project from the map that should be Linux
assertEquals("Linux", row.get("PROJECT"));

 <jee:jndi-lookup id="myDS" jndi-name="jdbc/myDataSource"/>

   from("direct:projects")
     .setHeader("lic", constant("ASF"))
     .setHeader("min", constant(123))
     .to("sql:select * from projects where license = :#lic and id > :#min order by id")

   from("direct:projects")
     .to("sql:select * from projects where license = :#lic and id > :#min order by id")

-- this is a comment
select *
from projects
where project in (:#in:names)
order by id

from("direct:query")
    .to("sql:classpath:sql/selectProjectsIn.sql")
    .to("log:query")
    .to("mock:query");

// use an array
template.requestBodyAndHeader("direct:query", "Hi there!", "names", new String[]{"Camel", "AMQ"});


// use a list
List<String> names = new ArrayList<String>();
names.add("Camel");
names.add("AMQ");

template.requestBodyAndHeader("direct:query", "Hi there!", "names", names);


// use a string separated values with comma
template.requestBodyAndHeader("direct:query", "Hi there!", "names", "Camel,AMQ");

from("direct:query")
    .to("sql:select * from projects where project in (:#in:names) order by id")
    .to("log:query")
    .to("mock:query");

 CREATE TABLE CAMEL_MESSAGEPROCESSED (
   processorName VARCHAR(255),
   messageId VARCHAR(100),
   createdAt TIMESTAMP
 )

<jdbc:embedded-database id="dataSource" type="DERBY" />

<bean id="messageIdRepository" class="org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository">
	<constructor-arg ref="dataSource" />
	<constructor-arg value="myProcessorName" />
</bean>

<camel:camelContext>
	<camel:errorHandler id="deadLetterChannel" type="DeadLetterChannel" deadLetterUri="mock:error">
		<camel:redeliveryPolicy maximumRedeliveries="0" maximumRedeliveryDelay="0" logStackTrace="false" />
	</camel:errorHandler>
	
	<camel:route id="JdbcMessageIdRepositoryTest" errorHandlerRef="deadLetterChannel">
		<camel:from uri="direct:start" />
		<camel:idempotentConsumer messageIdRepositoryRef="messageIdRepository">
			<camel:header>messageId</camel:header>
			<camel:to uri="mock:result" />
		</camel:idempotentConsumer>
	</camel:route>
</camel:camelContext>

<bean id="messageIdRepository" class="org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository">
	<constructor-arg ref="dataSource" />
	<constructor-arg value="myProcessorName" />
	<property name="tableExistsString" value="SELECT 1 FROM CUSTOMIZED_MESSAGE_REPOSITORY WHERE 1 = 0" />
	<property name="createString" value="CREATE TABLE CUSTOMIZED_MESSAGE_REPOSITORY (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP)" />
	<property name="queryString" value="SELECT COUNT(*) FROM CUSTOMIZED_MESSAGE_REPOSITORY WHERE processorName = ? AND messageId = ?" />
	<property name="insertString" value="INSERT INTO CUSTOMIZED_MESSAGE_REPOSITORY (processorName, messageId, createdAt) VALUES (?, ?, ?)" />
	<property name="deleteString" value="DELETE FROM CUSTOMIZED_MESSAGE_REPOSITORY WHERE processorName = ? AND messageId = ?" />
</bean>

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

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

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

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

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