Configure SQL datasource through properties in Camel Blueprint (within Karaf)

Question

Given a very simple Camel bundle for Karaf, generated with the camel-archetype-blueprint, I want to add a datasource that is configured via properties and not within the blueprint.xml.

I tried configuring a PropertiesComponent and accessing the property within the property value for the MySQL datasource in various ways, but none seems to work. When logging a message though, the properties are accessible.

How can a datasource be configured using parameter values from a properties file?

I especially need this to use the same datasource configuration for multiple bundles and distinguish between production/test environments. I thought about writing the properties with Maven during the build, depending on the target environment. Are there any other best practices on how to solve this datasource issue?

<?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
       http://camel.apache.org/schema/blueprint http://camel.apache.org/schema/blueprint/camel-blueprint.xsd">

    <bean id="properties" class="org.apache.camel.component.properties.PropertiesComponent">
        <property name="location" value="classpath:config.properties" />
    </bean>

    <bean id="dataSourceMySQL" class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
        <property name="url" value="jdbc:mysql://127.0.0.1/test_database" />
        <!-- This causes an error, as it tries to connect with
             `${mysqlUser}`@`localhost` without any evaluation -->
        <property name="user" value="${mysqlUser}" />
        <property name="password" value="${mysqlPassword}" />
    </bean>

    <service interface="javax.sql.DataSource" ref="dataSourceMySQL">
        <service-properties>
            <entry key="osgi.jndi.service.name" value="jdbc/mysqlDatasource" />
        </service-properties>
    </service>
    <bean id="sql" class="org.apache.camel.component.sql.SqlComponent">
        <property name="dataSource" ref="dataSourceMySQL" />
    </bean>

    <camelContext xmlns="http://camel.apache.org/schema/blueprint">
        <route id="messageQuery">
            <from uri="sql:SELECT * FROM messages" />
            <log message="The user property is: {{mysqlUser}}, the query result is: ${body}" />
        </route>
    </camelContext>

</blueprint>

Just for an overview, the project layout looks like this:

Gherardi

Can you please try to place your config.properties file one folder up under the src/main/resource – Waterfront

Isn't it that you need to place the FOLDER infant of your property file. You are using OSGI so maybe this folder is already scanned,but if it was just Spring you need to put folder infant of the file. – Waterfront

try <property name="location" value="class path:/OSGI-INF/config.properties"/> – Waterfront

Also replace ${mysqlUser} with {{mysqlUser}} – Waterfront

Answer 1

a) Datasource in bundle with xml and bundle properties

You could use bundle properties. The example below optionally uses a bundle configuration in etc/org.camel.demo.cfg:

<?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
       http://camel.apache.org/schema/blueprint http://camel.apache.org/schema/blueprint/camel-blueprint.xsd">

    <!-- etc/org.camel.demo.cfg -->
    <cm:property-placeholder persistent-id="org.camel.demo" xmlns:cm="http://aries.apache.org/blueprint/xmlns/blueprint-cm/v1.0.0">
        <cm:default-properties>
            <cm:property name="mysqlUser" value="root"/>
            <cm:property name="mysqlPassword" value=""/>
        </cm:default-properties>
    </cm:property-placeholder>

    <bean id="properties" class="org.apache.camel.component.properties.PropertiesComponent">
        <property name="location" value="classpath:config.properties" />
    </bean>

    <bean id="dataSourceMySQL" class="com.mysql.jdbc.jdbc2.optional.MysqlDataSource">
        <property name="url" value="jdbc:mysql://127.0.0.1/test_database" />
        <property name="user" value="${mysqlUser}" />
        <property name="password" value="${mysqlPassword}" />
    </bean>
</blueprint>

b) Shared datasource

Another option would be to use a shared datasource. Just deploy a blueprint file containing only the datasource (example below uses postgres).

You can combine this also with config properties as shown above.

Providing the OSGi service

<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0">

    <!-- use config properties if needed -->

    <bean id="dataSource" class="org.postgresql.ds.PGPoolingDataSource" destroy-method="close">
        <property name="serverName" value=":"/>
        <property name="user" value="postgres"/>
        <property name="password" value="postgres"/>
        <property name="dataSourceName" value="demo"/>
        <property name="initialConnections" value="2"/>
        <property name="maxConnections" value="4" />
    </bean>

    <service interface="javax.sql.DataSource" ref="dataSource">
        <service-properties>
            <entry key="osgi.jndi.service.name" value="jdbc/demo"/>
        </service-properties>
    </service>
</blueprint>

Referencing from another bundle

In your bundle you can then lookup the datasource osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/demo)

To use this datasource in a SqlComponent, a reference must be created like this:

<reference id="dataSource" interface="javax.sql.DataSource"
    filter="(osgi.jndi.service.name=jdbc/mysql)">
</reference>
<bean id="sql" class="org.apache.camel.component.sql.SqlComponent">
    <property name="dataSource" ref="dataSource" />
</bean>

Using the persistence.xml

Make sure you import org.demo.osgi.datasource.**. Here is a usage example with persistence.xml:

<persistence version="2.0"
             xmlns="http://java.sun.com/xml/ns/persistence" >

    <persistence-unit name="demo" transaction-type="JTA">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/demo)</jta-data-source>
        <mapping-file>META-INF/foo.xml</mapping-file>
    </persistence-unit>

</persistence>

c) Use jdbc-feature (optional)

The xml file from above can be created and managed using the jdbc feature. It depends a bit on your version if it is available or not:

JBossFuse:admin@545074693af1> features:install jdbc hibernate jndi
JBossFuse:admin@545074693af1> install mvn:org.postgresql/postgresql/9.4.1208
Bundle ID: 292
JBossFuse:admin@545074693af1> resolve 292
JBossFuse:admin@545074693af1> jdbc:create -t postgres -u postgres -p postgres  -url ${postgres.addr}:${postgres.port} demo

P.S.: If you want to remove the cleartext password from the config file use something like jasypt.

Xenophon

Nice answer thanks! Option 2 looks very promising, but is it also possible to create a camel SqlComponent and make it use the OSGi DataSource? – Gherardi

I'd imagine something like this, which unfortunately doesn't work: <property name="dataSource" ref="osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mysql)" /> – Gherardi

You need to declare a blueprint reference see an example here github.com/apache/servicemix/blob/master/examples/camel/… – Thermotaxis

Amazing, works. Thanks a lot! (Can award the bounty in 6 hours, and will give it to you) – Gherardi

Thanks!, I have updated the answer to include the camel-sql datasource reference (for future visitors) – Thermotaxis

Answer 2

Hello I can see two things. The first one is that you have not said in which folder in the class path the config file reside. Unless the OSGI-INF folder is not already scanned you should have :

The second thing is about how you reference the properties. Unless otherwise defined the property prefixToken and suffixToken are by default "{{" and "}}" http://camel.apache.org/properties.html

Which means that you need to replace your ${mysqlUser} with {{mysqlUser}}

Waterfront