How to Configure a JDBC Datasource

Question

My CQ5 application needs to connect to an external RDBMS. How do I configure and connect to a JDBC DataSource?

Answer, Resolution

As an example we will create a JDBC DataSource to connect to H2 database engine

Install the JDBC driver for your database.

If your database vendor provides a JDBC driver jar as an OSGi bundle then you can deploy it directly into Felix Console.
If OSGi bundle with your driver is not available you can add the jar to the shared classpath of your application server, for example crx-quickstart/server/lib/container under CQSE, and create an Extension Bundle.
- Create manifest file. For this example we will call it h2ext.mf. See below code listing.
  Note that the blank line at the end of a jar manifest file is required.
- Create a jar file with above manifest using the following command.
```
jar -cfm org.h2.jdbc.driver.extension-1.1.119.jar h2ext.mf 
```
- Install org.h2.jdbc.driver.extension-1.1.119.jar using Felix Console. This should add org.h2 package to the export list of the System Bundle.
- Please refer to section 3.15, Extension Bundles, of the OSGi Service Platform Core for more information about Extension Bundles.
Another option is to embed the jdbc driver into another OSGi bundle. If you are using CRXDE to create an OSGi bundle you can put the driver jar into the bundle lib directory. CRXDE will automatically embed it.
Another option is to add the jar to the shared classpath of your application server and use org.osgi.framework.bootdelegation property in sling.propeties to load the driver class. Click here for more information. For example if you are using H2 database, copy h2-1.1.119.jar to crx-quickstart/server/lib/container. Then add the line "sling.bootdelegation.class.org.h2.Driver=org.h2" to sling.properties.

Bundle-ManifestVersion: 2
Bundle-SymbolicName: org.h2.jdbc.driver.extension
Bundle-Version: 1.1.119
Fragment-Host: system.bundle; extension:=framework
Bundle-Name: H2 JDBC Driver Extension
Bundle-Description: Bundle exporting H2 JDBC Driver
Export-Package: org.h2

Configure JDBC Connection Pool

Naviagte to the Cofiguration Manager in the Felix console (http://localhost:4502/system/console/configMgr). Use JDBC Connection Pool configuration factory to create a new connection pool. For Example it might have the following settings.

JDBC driver class: org.h2.Driver
JDBC connection URI: jdbc:h2:~/test
Usename: sa
Password:
Validation query:
Pool size: 10
Pool wait (msec): 100
Datasource name: h2DataSource

Get an instance of the DataSource.

Once configured the DataSource instance is acquired by calling getDataSource(String datasourceName) method of DataSourcePool service (com.day.commons.datasource.poolservice.DataSourcePool) and passing it the datasource name.

OSGi Component Example

In this example we are creating a simple OSGi service which can be used to get an instance of a DataSource. This example is not realistic, since it basically reinvents the same API as com.day.commons.datasource.poolservice.DataSourcePool itself without adding any functionality. But it shows how DataSourcePool can be used in your own code.

Lets first create an interface for our service.

package com.day.test.datasource;
import javax.sql.DataSource;

public interface DatasourceUtil {
    public DataSource getDataSource(String dataSourceName);
}

Now the implementation. As you can see besides hiding the DataSourceNotFoundException it does not add any value :).

package com.day.test.datasource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;

import com.day.commons.datasource.poolservice.DataSourceNotFoundException;
import com.day.commons.datasource.poolservice.DataSourcePool;

/**
 *
 * This class is an example if how DataSourcePool can be used to obtain the DataSource.
 *
 * @scr.component immediate="true" metatype="no"
 * @scr.service interface="DatasourceUtil"
 * @scr.property name="service.description" value="Data Source lookup example"
 * @scr.property name="service.vendor" value="Day Software"
 */

public class DatasourceUtilImpl implements DatasourceUtil {
    private static final Logger log = LoggerFactory.getLogger(
            DatasourceUtilImpl.class);

    /** @scr.reference policy="static" */
    private DataSourcePool dataSourceService;

    public DataSource getDataSource(String dataSourceName) {
        log.info("Using DataSourcePool service lookup " +
                "to get connection pool " + dataSourceName); 
        DataSource dataSource = null;
        try {
            dataSource = (DataSource) dataSourceService.getDataSource(
                    dataSourceName);
        } catch (DataSourceNotFoundException e) {
            log.error("Unable to find datasource {}.", dataSourceName, e);
        }
        return dataSource;
    }
}

This class is using OSGi Declarative Services specification to get an instance of DataSourcePool service and then calls its getDataSource(dataSourceName) to get the DataSource. Maven SCR Plugin is using the the scr java annotations to generate the XML descriptors for OSGi Declarative Services. The attached zip file contain the example POM.

You can download a complete example here: datasourceutil.zip. It includes a maven POM to build it. In order to build this project using POM provided please refer to How to use CQ5 as maven repository.

JSP Example

This is a CRX package that contains a JSP example - datasource-example-jsp.zip. It"s a CQ component that can be added to any page with a paragraph system. It can be used to test your DataSource connection.

5.3 Note

Added is a sample bundle to build with CRXDE / CRXDE Lite that contains the oracle driverinstall the oracle_example_driver.zip, have a look at the /apps/oracle.In the Activator.java it explicitly load the driver class (it may not be required in future releases).Make sure you have the latest day.commons.datasource.jdbcpool (1.0.8) installed before using it.