Using Camel JDBC Component

In this tutorial we will learn how to perform some basic JDBC operations using Camel JDBC Component.

The JDBC component enables you to access databases through JDBC, where SQL queries (SELECT) and operations (INSERT, UPDATE, etc) are sent in the message body. This component uses the standard JDBC API.

Let’s start from the Database. We will start a PostgreSQL Database using Docker and the following credentials:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 --name camel_jpa -e POSTGRES_USER=camel -e POSTGRES_PASSWORD=camel -e POSTGRES_DB=cameldb -p 5432:5432 postgres:10.5

Now for the Camel application. In the following MainApp, we will create a Timer component which triggers a Processor. The Processor will execute a SQL Statement

package com.example.camel.jdbc;

import org.apache.camel.CamelContext;
import org.apache.camel.builder.RouteBuilder;
import org.apache.camel.impl.DefaultCamelContext;
import org.apache.camel.support.DefaultRegistry;
import org.apache.commons.dbcp.*;
import javax.sql.DataSource;

public class MainApp {
  public static void main(String... args) throws Exception {
    String url = "jdbc:postgresql://localhost:5432/cameldb";
    DataSource dataSource = setupDataSource(url);
    DefaultRegistry reg = new DefaultRegistry();
    reg.bind("myDataSource", dataSource);
    CamelContext context = new DefaultCamelContext(reg);
    context.addRoutes(new MainApp().new MyRouteBuilder());
    context.start();
    Thread.sleep(5000);
    context.stop();
  }

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

  class MyRouteBuilder extends RouteBuilder {
    public void configure() {
      from("timer://foo?period=1000")
          .process(new SimpleProcessor())
          .to("jdbc:myDataSource")
          .to("log:output");
    }
  }
}

As you can see in the above code, a BasicDataSource definition is provided by the setupDataSource. 

The Connection pool connects to a PostgreSQL Database using the credentials contained in the main method of the class.

Finally here is the SimpleProcessor implementation which merely execute a check of the current time against the DB:

package com.example.camel.jdbc;

import org.apache.camel.Exchange;
import org.apache.camel.Processor;

public class SimpleProcessor implements Processor {
  public void process(Exchange exchange) throws Exception {
    exchange.getIn().setBody("select now()");
  }
}

You can run it as follows:

mvn clean install exec:java

In the output you should notice that the Body of the Route contains the result of the SQL Statement:

[mel-1) thread #1 - timer://foo] output                         INFO  Exchange[ExchangePattern: InOnly, BodyType: java.util.ArrayList, Body: [{now=2020-04-14 10:08:09.171638}]] [mel-1) thread #1 - timer://foo] output                         INFO  Exchange[ExchangePattern: InOnly, BodyType: java.util.ArrayList, Body: [{now=2020-04-14 10:08:10.033046}]] [mel-1) thread #1 - timer://foo] output                         INFO  Exchange[ExchangePattern: InOnly, BodyType: java.util.ArrayList, Body: [{now=2020-04-14 10:08:11.033471}]] [mel-1) thread #1 - timer://foo] output                         INFO  Exchange[ExchangePattern: InOnly, BodyType: java.util.ArrayList, Body: [{now=2020-04-14 10:08:12.033592}]] [mel-1) thread #1 - timer://foo] output                         INFO  Exchange[ExchangePattern: InOnly, BodyType: java.util.ArrayList, Body: [{now=2020-04-14 10:08:13.033893}]] 

Source code for this tutorial: https://github.com/fmarchioni/masterspringboot/tree/master/camel/camel-jdbc