The Open Universe: Fuse and Spring DSL with JDBC and Postgresql

The Apache Camel JDBC component enables you to access databases through JDBC, where SQL queries and operations are sent in the message body. This component uses the standard JDBC API, unlike the SQL component, which uses spring-jdbc.

I installed the following products and built an example using the jdbc component by creating a Fuse Project with the camel-spring archetype. I also created a new database, user and table in Postgres.

PostgreSQL 9.2
pgAdmin III
Fedora 18
Fuse 6.0
Fuse IDE 6.0

The new project is created with a Spring DSL, sample messages and routes. I added a new when, log and 2 endpoints. A bean is used to setup the SQL statement to send to the datasource. The FuseIDE designer is shown below after the route was added.

The camel-context.xml is shown below. Notice the datasource and the OrderToSQLBean along with the route.

<?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:camel="http://camel.apache.org/schema/spring"
       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">
    <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.postgresql.Driver" />
        <property name="url" value="jdbc:postgresql://localhost:5432/camel" />
        <property name="username" value="cameluser" />
        <property name="password" value="camel" />
    </bean>
       <bean id="orderToSQL" class="com.mycompany.camel.spring.jdbc.OrderToSQLBean" />
<camelContext xmlns="http://camel.apache.org/schema/spring">
    <route>
        <description>here is a sample which processes the input files
         (leaving them in place - see the 'noop' flag)
         then performs content based routing on the message using XPath</description>
        <from uri="file:src/data?noop=true"/>
        <choice>
            <when>
                <xpath>/person/city = 'London'</xpath>
                <log message="UK message"/>
                <to uri="file:target/messages/uk"/>
            </when>
            <when>
                <xpath>/person/city = 'Charleston'</xpath>
                <log message="US message"/>
                <to uri="bean:orderToSQL?method=toSql"/>
                <to uri="jdbc:datasource"/>
            </when>
            <otherwise>
                <log message="Other message"/>
                <to uri="file:target/messages/others"/>
            </otherwise>
        </choice>
    </route>
</camelContext>
</beans>

I built the toSql method in the OrderToSQLBean.java to take the name information to put into the camel database. I created the main and dump methods for testing the connection to the database.

package com.mycompany.camel.spring.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.camel.language.XPath;

public class OrderToSQLBean {

    /**
    * @param args
    */
    public static void main(String[] args) {
        try {
            Class.forName("org.postgresql.Driver" );
            } catch (Exception e) {
            System.err.println("ERROR: failed to load JDBC driver.");
            e.printStackTrace();
            }
            try {
                Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/camel", "cameluser", "camel");
                Statement st = null;
                ResultSet rs = null;
                st = c.createStatement();
                rs = st.executeQuery("SELECT * FROM testtable;");
                dump(rs);
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

    }
    public static void dump(ResultSet rs) throws SQLException {
        ResultSetMetaData meta   = rs.getMetaData();
        int colmax = meta.getColumnCount();
        int i;
        Object o = null;
        for (; rs.next(); ) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);
                System.out.print(o.toString() + " ");
            }
            System.out.println(" ");
        }
    }
    public String toSql(@XPath("person/firstName") String firstName,
        @XPath("person/lastName") String lastName,
        @XPath("person/city") String city)
    {
        StringBuilder sb = new StringBuilder();
        sb.append("insert into people ");
        sb.append("(country, lastname, firstname, city) values (");
        sb.append("'US', ");
        sb.append("'").append(lastName).append("', ");
        sb.append("'").append(firstName).append("', ");
        sb.append("'").append(city).append("') ");
        return sb.toString();
    }

}

I added the dependencies in the pom.xml.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">

<modelVersion>4.0.0</modelVersion>

<groupId>com.mycompany</groupId>
<artifactId>camel-spring-jdbc</artifactId>
<packaging>jar</packaging>
<version>1.0.0-SNAPSHOT</version>
<name>A Camel Spring Route</name>
<url>http://www.myorganization.org</url>
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
   </properties>
<repositories>
    <repository>
      <id>release.fusesource.org</id>
      <name>FuseSource Release Repository</name>
      <url>http://repo.fusesource.com/nexus/content/repositories/releases</url>
      <snapshots>
        <enabled>false</enabled>
      </snapshots>
      <releases>
        <enabled>true</enabled>
      </releases>
    </repository>
    <repository>
      <id>snapshot.fusesource.org</id>
      <name>FuseSource Snapshot Repository</name>
      <url>http://repo.fusesource.com/nexus/content/repositories/snapshots</url>
      <snapshots>
        <enabled>true</enabled>
      </snapshots>
      <releases>
        <enabled>false</enabled>
      </releases>
    </repository>
</repositories>

<pluginRepositories>
    <pluginRepository>
      <id>release.fusesource.org</id>
      <name>FuseSource Release Repository</name>
      <url>http://repo.fusesource.com/nexus/content/repositories/releases</url>
      <snapshots>
        <enabled>false</enabled>
      </snapshots>
      <releases>
        <enabled>true</enabled>
      </releases>
    </pluginRepository>
    <pluginRepository>
      <id>snapshot.fusesource.org</id>
      <name>FuseSource Snapshot Repository</name>
      <url>http://repo.fusesource.com/nexus/content/repositories/snapshots</url>
      <snapshots>
        <enabled>true</enabled>
      </snapshots>
      <releases>
        <enabled>false</enabled>
      </releases>
    </pluginRepository>
</pluginRepositories>

<dependencies>
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-core</artifactId>
      <version>2.10.0.redhat-60024</version>
    </dependency>
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-spring</artifactId>
      <version>2.10.0.redhat-60024</version>
    </dependency>
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jdbc</artifactId>
        <version>2.10.0.redhat-60024</version>
    </dependency>
    <dependency>
        <groupId>hsqldb</groupId>
        <artifactId>hsqldb</artifactId>
        <version>1.8.0.7</version>
    </dependency>
    <dependency>
        <groupId>postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>9.1-901.jdbc4</version>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>3.0.7.RELEASE</version>
    </dependency>
    
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.6.6</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.6.6</version>
    </dependency>
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

    
    <dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-test-spring</artifactId>
      <version>2.10.0.redhat-60024</version>
      <scope>test</scope>
    </dependency>

</dependencies>

<build>
    <defaultGoal>install</defaultGoal>

    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>2.5.1</version>
        <configuration>
          <source>1.6</source>
          <target>1.6</target>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-resources-plugin</artifactId>
        <version>2.4.3</version>
        <configuration>
          <encoding>UTF-8</encoding>
        </configuration>
      </plugin>

      
      <plugin>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-maven-plugin</artifactId>
        <version>2.10.0.redhat-60024</version>
      </plugin>
    </plugins>
</build>
</project>

To test a message with a US city I created a new message3.xml to process.
<?xml version="1.0" encoding="UTF-8"?>
<person user="kenneth">
<firstName>Kenneth</firstName>
<lastName>Peeples</lastName>
<city>Charleston</city>
</person>

References:

https://access.redhat.com/site/documentation/en-US/JBoss_Fuse/6.0/html/EIP_Transaction_Guide/files/TxnManagers-Samples-JDBC.html

https://github.com/FuseByExample/camel-persistence-part1/
http://camel.apache.org/jdbc.html
http://www.if-not-true-then-false.com/2012/install-postgresql-on-fedora-centos-red-hat-rhel/
https://access.redhat.com/site/documentation/en-US/Fuse_IDE/7.1/html/Introduction_to_Fuse_IDE/files/IDEIntroEclipseCreate.html

Monday, June 3, 2013

Fuse and Spring DSL with JDBC and Postgresql