JDBC (Databases)


Learn how to load values from a database and inject them into the application.

Database driver

Go to Data Integration > Libraries and upload the database driver.

Database connection

On the left menu, click Connectors and create a new SQL connector with one of the drivers available in the library.

Note: You can use properties to define the string attributes (databaseName and password in this example).

TLS configuration

TLS connection should be be configured on JDBC server side and non-encrypted communication should be forbidden.

Camel routes

Only a single route is necessary, on the left menu, click Routes and create a new one with the following content:

Camel routes
<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">

    <route>
        <!-- Trigger only once -->
        <from uri="timer:single?repeatCount=1"/>

        <!-- Define the proper SQL request -->
        <setBody>
            <constant>SELECT [name] FROM [sysusers]</constant>
        </setBody>

        <!-- Execute the request -->
        <to uri="jdbc:lyon"/>

        <!-- Split per row and print the user name -->
        <split>
            <simple>${body}</simple>
            <log message="user: ${body[name]}"/>
        </split>
    </route>

</routes>

With named parameters:

Camel routes
<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">

    <!-- Named parameters in named headers -->
    <route>
        <!-- Trigger only once -->
        <from uri="timer:single?repeatCount=1"/>

        <!-- Set named parameter as header -->
        <setHeader headerName="myVariable">
            <simple>Decision Insight</simple>
        </setHeader>

        <!-- Define the proper SQL request -->
        <setBody>
            <constant>SELECT [name] FROM [sysusers] where [name] = :?myVariable</constant>
        </setBody>

        <!-- Execute the request -->
        <to uri="jdbc:mssql?useHeadersAsParameters=true"/>
        <log message="${body}"/>
    </route>
    
    <!-- Named parameters stored in map -->
    <route>
        <!-- Trigger only once -->
        <from uri="timer:single?repeatCount=1"/>
        <!-- Set named parameter as header -->
        <setHeader headerName="CamelJdbcParameters">
            <u:map-create>
                <u:map-entry key="myVariable">
                    <simple>Decision Insight</simple>
                </u:map-entry>
            </u:map-create>
        </setHeader>
        <!-- Define the proper SQL request -->
        <setBody>
            <constant>SELECT [name] FROM [sysusers] where [name] = :?myVariable</constant>
        </setBody>
        <!-- Execute the request -->
        <to uri="jdbc:mssql?useHeadersAsParameters=true"/>
        <log message="${body}"/>
    </route>
</routes>

Configure Database connection parameters dynamically in Routes 

The database connection parameters can be configured dynamically inside routes by setting these properties (like Password, user and serverName e.t.c ) from some external source which can be a configuration file or an external API.

For example, if we want to dynamically retrieve the password from an external csv file instead of specifying it in Properties, then the password does not need to be configured while creating a database connector. In the example below we are creating a connector for a database hosted on local system: 

On the left menu, click Routes and create a new one as shown below:

Camel routes
<routes xmlns="http://camel.apache.org/schema/spring" xmlns:u="http://www.systar.com/aluminium/camel-util">
    <route>
        <!-- Define the file component to read configuration file -->
        <from uri="file:F:\Test\?fileName=Configuration.csv&amp;noop=true"/>

        <!-- Define a header to hold password read from configuration file -->
        <setHeader headerName="Password">
            <simple>${body}</simple>
        </setHeader>

        <!-- Use bean component to call setPassword method on 'jdbcdatabase' -->
        <to uri="bean:jdbcdatabase?method=setPassword(${header.Password})"/>

        <!-- Start second route when database password is configured-->
        <transform>
            <simple>${camelContext.startRoute("requestSqlServer")}</simple>
        </transform>
    </route>
    <route autoStartup="false" id="requestSqlServer">
		 <!-- Trigger only once -->
        <from uri="timer:single?repeatCount=1"/>
        
		<!-- Define the proper SQL request -->
        <setBody>
            <constant>SELECT [batchno] FROM [btbatchmst]</constant>
        </setBody>

        <!-- Execute the request -->
        <to uri="jdbc:jdbcdatabase"/>

        <!-- Split per row and print the batch number -->
        <split>
            <simple>${body}</simple>
            <log message="batchno: ${body[batchno]}"/>
        </split>
    </route>
</routes>

Note: Here, we are retrieving only the password from the csv configuration file but we can read multiple fields to completely externalize all the connection parameters. By using the setURL method on database bean (as shown above) all the connection parameters can be configured at once.

 

Related Links