Apache Camel: REST CRUD and the SQL Component

We already did a first post about Apache Camel with a mock as a backend, which you can see here. Today we’ll see how to change that mock for a real database, configure a DataSource, or even a connection pool.

To start we will modify the dependencies that we need. Principally, three things:
- spring-boot-starter-jdbc: The library that will facilitate us the use of the DB and the creation of the DataSource.
- mysql-connector-java: The DB driver.
- camel-sql: The Apache Camel component that will allow us to make SQL queries.

The next step will be to configure the DataSource that allows us the connections with the database. This can be done in the Spring application.properties file.

As you can see we have also added the queries that will be used later in the code.

We’re going to start with a simple method, changing the mock that we had for a SQL query. To do this we will use the apache-camel SQL component.

With the component, we can make SQL queries directly using the configured DataSource. These queries can be written, either by writing them in the method or by invoking them from the properties file with the help of the curly brackets.

In the previous case, we indicated the output with the outType method, which will allow us to format the output easily. For the case of an Insert or Delete method, in which we don’t want to return a payload we can indicate erase it with the setBody method and the code of the answer with setHeader.

In general it’s a very similar example to the one that we used a mock for backend. But as we’re going to use JDBC and not JPA, properties such as name or author can only be obtained directly from the header or body of the message. And so we’ll have to remove the type method, which converts the input payload into an object.

One last trick, if we want to use a connection pool we only need to indicate it in the application.properties file through the property spring.datasource.type. We’ll look at the example with HikariCP:

spring.datasource.type=com.zaxxer.hikari.HikariDataSource