Using Camel JDBC and SQL Components

If you’re developing an application that needs to interact with a database, Camel JDBC and SQL Component are powerful tools that can help you easily integrate database access into your project.

In this tutorial, we’ll guide you through the process of using Camel JDBC and SQL Component to access a database, step-by-step. We’ll also cover important concepts and best practices along the way.

Set up and configuration

Firstly, we need a Database where we will execute the SQL Statements. For this purpose, we can start a PostgreSQL Database by using Docker:

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

Next, we will set up our Dependencies in the Camel Project:

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

<dependency>
      <groupId>org.apache.camel</groupId>
      <artifactId>camel-sql</artifactId>
</dependency>

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

• You need the camel-jdbc dependency if you want to use the JDBC Component
• On the the other hand, you need to include the camel-sql dependency if you want to use the SQL Component.
• In both cases, you will also need the Driver for your Database version.

Using the 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.

Next, we will create a sample Camel application to demonstrate how to use the JDBC Component. In the following MainApp, we will create a Timer component which triggers a Processor. The Processor will execute a SQL Statement:

public class JDBCExample {


    public static void main(String... args) throws Exception {
        // use Camels Main class

        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 JDBCExample().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:

Using the SQL Component

The camel-sql library offers a component that utilizes JDBC queries via the spring-jdbc dependency. By default, this component returns query results as a list of Map objects. Unlike the JDBC component, which utilizes the Camel message body for queries, the SQL component leverages the Camel endpoint to specify the query. The choice between using the JDBC or the SQL component depends on the specific integration use case. For instance, the SQL component is more convenient to use and maintain for static or simple queries that require only a few parameters.

Here is an example that uses the SQL Component:

public class SQLExample {


    public static void main(String... args) throws Exception {
        // use Camels Main class

        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 SQLExample().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")
            .to("sql:select now()")
            .to("log:output");

        }
    }
}

Conclusion

In this tutorial, we’ve walked through the process of using Camel JDBC and SQL Component to access a database. We covered the steps of adding dependencies to pom.xml, configuring the database connection, defining the SQL query, and running the application.

With these tools, you can easily integrate database access into your project and create powerful, data-driven applications.

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