Camel route to Microsoft SQL Server database with OSGI blueprint and Karaf

Microsoft SQL Server can often be found in the enterprises. A typical integration requirement is to send and receive message to/from a database table. In this post I’m looking at the minimum code required to define an Apache Camel route that runs in Karaf and delivers messages from a folder into a table.

This post is similar to the Camel route to WebSphere MQ with OSGI blueprint. You’ll find there a brief summary of how Karaf is used for hosting Java applications in general.

I’ll go over the steps to first setup Camel route prerequisites in Karaf and second define the file to SQL dataflow. If you want to run this example you’ll need a Microsoft SQL Server database with a simple Person table, and of course Karaf which can be downloaded and installed in a few minutes from Apache web site.

Start by running the command below to enable JDBC-related features and lay the foundation for using Microsoft SQL Server from Karaf.

karaf@root()>feature:install transaction jndi jdbc pax-jdbc-pool-dbcp2 pax-jdbc-config

Run the following command to install Microsoft SQL Server driver:

feature:install pax-jdbc-mssql

Download Microsoft JDBC Driver 4.2 for SQL Server (sqljdbc_4.2.8112.200_enu.tar.gz) from Microsoft Download Center. After unpacking the archive, search for and copy: a) sqljdbc42.jar to your Karaf deploy folder, and b) sqljdbc_auth.dll to your JDK bin folder (e.g. C:\Program Files\AdoptOpenJDK\openjdk-11.0.8_10\bin).

Run the following command and you should find the new Microsoft JDBC Driver registered.

karaf@root()> service:list DataSourceFactory

[org.osgi.service.jdbc.DataSourceFactory]
— — — — — — — — — — — — — — — — — — — — -
osgi.jdbc.driver.class = com.microsoft.sqlserver.jdbc.SQLServerDriver
osgi.jdbc.driver.name = Microsoft JDBC Driver for SQL Server
osgi.jdbc.driver.version = 7.4
service.bundleid = 157
service.id = 304
service.scope = singleton
Provided by :
Microsoft JDBC Driver for SQL Server (157)

Create Person table in your MS SQL Server database:

create table Person
(
FirstName char(100),
LastName char(100),
Email char(100)
)

In order to create an OSGI JDBC data source, add org.ops4j.datasource-person.cfg file to Karaf’s etc folder with the following content:

osgi.jdbc.driver.class=com.microsoft.sqlserver.jdbc.SQLServerDriver
osgi.jdbc.driver.name = mssql
url=jdbc:sqlserver://<sql-hostname>;instanceName=<your-sql-instance>;databaseName=<your-db>;integratedSecurity=true;
dataSourceName=Person

You can verify connectivity with your SQL Server database:

karaf@root()> jdbc:ds-list
Name | Service Id | Product | Version | URL | Status
Person | 283 | Microsoft SQL Server | 11.00.2100 | jdbc:sqlserver://<sql-hostname>\<your-sql-instance>:59273;[…];applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite; | OK

Next, add Camel features required by our integration:

karaf@root()>repo-add camel 3.4.0
karaf@root()>feature:install camel-sql

Finally, we define our route using XML DSL in the following OSGI blueprint sql-route.xml file:

<?xml version=”1.0" encoding=”UTF-8"?>
<blueprint xmlns=”http://www.osgi.org/xmlns/blueprint/v1.0.0"
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation=”http://www.osgi.org/xmlns/blueprint/v1.0.0 http://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd">

<reference id=”myDataSource” interface=”javax.sql.DataSource”
filter=”(osgi.jndi.service.name=Person)” availability=”mandatory” />

<camelContext id=” xml-to-mssql-sample” xmlns=”http://camel.apache.org/schema/blueprint">
<route id=”file2sql”>

<! — Step 1: Get record field values from XML input file. →
<from uri=”file:<your-sample-path>/in?noop=true”/>
<setProperty name=”lastName”><xpath resultType=”java.lang.String”>/load/lastName</xpath></setProperty>
<setProperty name=”firstName”><xpath resultType=”java.lang.String”>/load/firstName</xpath></setProperty>
<log message=”step1: loading ${exchangeProperty.lastName}.”/>

<! — Step 2: Load record into the table. →
<to uri=”sql:insert into Person (LastName, FirstName) VALUES (:#${exchangeProperty.lastName}, :#${exchangeProperty.firstName})?dataSource=myDataSource”/>
<log message=”step2: processed playload :${body}:”/>

</route>
</camelContext>

</blueprint>

And deploy the blueprint to Karaf:

karaf@root()>install -s blueprint:file:///<your-sample-path>/sql-route.xml

To check the camel route is running you can execute:

karaf@root()> camel:context-list
Context Status Total # Failed # Inflight # Uptime
— — — — — — — — — — — — — — — — — — — — — — —
xml-to-mssql-sample Started 0 0 0 4.999 seconds

To actually test the route drop the test file below into <your-sample-path>/in directory.

<load>
<lastName>Doe</lastName>
<firstName>John</firstName>
</load>

And check Person table content:

karaf@root()> jdbc:query Person “select * from Person”
Email | FirstName | LastName
— — — | John|Doe