Connecting a PostgreSQL Database With Apache Camel

The jdbc: component allows you to access a database using JDBC, whereas the Select query and operations (UPDATE, INSERT, DELETE etc.) send messages in bodies.

The sql: component allows you to work with databases using JDBC queries. The difference between this component and the JDBC component is that, with SQL, the query is a property of the endpoint, and it uses message payloads as parameters passed to the query.

Connecting to a Postgres Database Using the jdbc: Component

We will define the route that will be using JDBC components to access our database and will use an example of inserting data into a table. In this case, we will define the processor to build the insert query and pass it to the message body.

Adding POM dependencies:

<?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/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.learncamel</groupId>
    <artifactId>learncamel-activemq2db</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-core</artifactId>
        <version>2.19.2</version>
    </dependency>

        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-sql</artifactId>
            <version>2.19.2</version>
        </dependency>
    <!-- camel-jms dependency -->
    <dependency>
        <groupId>org.apache.camel</groupId>
        <artifactId>camel-jms</artifactId>
        <version>2.19.2</version>
    </dependency>

    <!-- Logging Jars -->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.12</version>
    </dependency>

    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-simple</artifactId>
        <version>1.7.12</version>
    </dependency>

        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-test</artifactId>
            <version>2.19.2</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.apache.camel</groupId>
            <artifactId>camel-jdbc</artifactId>
            <version>2.10.4</version>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1212</version>
        </dependency>

        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.5.4</version>
        </dependency>
    </dependencies>
</project>

Defining the route:

package com.learncamel.route.jdbc;

import org.apache.camel.builder.RouteBuilder;

public class DBPostgresRoute extends RouteBuilder{
    public void configure() throws Exception {

        from("direct:dbInput")
                .to("log:?level=INFO&showBody=true")
                .process(new InsertProcessor())
                .to("jdbc:myDataSource");
    }
}

Defining the processor

package com.learncamel.route.jdbc;

import org.apache.camel.Exchange;

public class InsertProcessor implements org.apache.camel.Processor {
    public void process(Exchange exchange) throws Exception {
        String input = (String) exchange.getIn().getBody();
        System.out.println("Input to be persisted : " + input);
        String insertQuery = "INSERT INTO messages values ( '1','" + input+"')";
        System.out.println("Insert Query is : " + insertQuery);
        exchange.getIn().setBody(insertQuery);
    }
}

Defining CamelContext and DataSource

Camel context:

@Override
public CamelContext createCamelContext() {

    String url = "jdbc:postgresql://localhost:5432/localdb";
    DataSource dataSource = setupDataSource(url);

    SimpleRegistry registry = new SimpleRegistry();
    registry.put("myDataSource", dataSource);

    CamelContext context = new DefaultCamelContext(registry);
    // plug in a seda component, as we don't really need an embedded broker
    return context;
}

Simple data source:

    private static DataSource setupDataSource(String connectURI) {
        BasicDataSource ds = new BasicDataSource();
        ds.setUsername("postgres");
        ds.setDriverClassName("org.postgresql.Driver");
        ds.setPassword("sqlserver");
        ds.setUrl(connectURI);
        return ds;
    }
}

Below is the list of packages we need to add to your class:

import org.apache.camel.CamelContext;
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.impl.DefaultCamelContext;
import org.apache.camel.impl.SimpleRegistry;
import org.apache.camel.test.junit4.CamelTestSupport;
import org.apache.commons.dbcp.BasicDataSource;

Connecting the Postgres Database Using the sql: Component

package com.learncamel.route.jdbc;

import org.apache.camel.builder.RouteBuilder;

public class DBPostgresRoute extends RouteBuilder{
    public void configure() throws Exception {

        from("direct:dbInput")
                .to("log:?level=INFO&showBody=true")
            .to("sql:select * from messages?dataSource=myDataSource")
                .to("log:?level=INFO&showBody=true");
    }
}

The dataSource value should be the same thing you added in the registry during CamelContext initialization. In this case, myDataSource is the value used with the SQL and JDBC components. 

Now, you know how to use the jdbc: and sql: component with Apache Camel.