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.
- Create manifest file. For this example we will call it h2ext.mf. See below code listing.
- 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.
h2ext.mf
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.
Applies to
CQ5.2.1, CQ5.3
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
ดาวน์โหลด
Adobe MAX
การประชุมความคิดสร้างสรรค์
14-16 ต.ค. Miami Beach และออนไลน์
Adobe MAX
การประชุมความคิดสร้างสรรค์
14-16 ต.ค. Miami Beach และออนไลน์