Connecting to SQL Databases

You are reading the AEM 5.6 version of Connecting to SQL Databases.
This documentation is also available for the following versions: CQ 5.5

Access an external SQL database to so that your CQ applications can interact with the data:

Create or obtain an OSGi bundle that that exports the JDBC driver package.
Configure a JDBC data source pool provider.
Obtain a data source object and create the connection in your code.

Bundling the JDBC Database Driver

Some database vendors provide JDBC drivers in an OSGi bundle, for example MySQL. If the JDBC driver for your database is not available as an OSGi bundle, obtain the driver JAR and wrap it in an OSGi bundle. The bundle must export the packages that are required for interacting with the database server. The bundle must also import the packages that it references.

The following example uses the Bundle plugin for Maven to wrap the HSQLDB driver in an OSGi bundle. The POM instructs the plugin to embed the hsqldb.jar file that is identified as a dependency. All org.hsqldb packages are exported.

The plugin automatically determines which packages to import and lists them in the MANIFEST.MF file of the bundle. If any of the packages are not available on the CQ server, the bundle will not start upon installing. Two possible solutions are as follows:

Indicate in the POM that the packages are optional. Use this solution when the JDBC connection does not actually require the package members. Use the Import-Package element to indicate optional packages as in the following example:
<Import-Package>org.jboss.*;resolution:=optional,*</Import-Package>
Wrap the JAR files that contain the packages in an OSGi bundle that exports the packages, and deploy the bundle. Use this solution when the package members are required during code execution.

Knowledge of the source code enables you to decide which solution to use. You can also try either solution and perform testing to validate the solution.

POM that bundles hsqldb.jar

<project xmlns="http://maven.apache.org/POM/4.0.0" 
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
		xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  
  <groupId>com.adobe.example.myapp</groupId>
  <artifactId>hsqldb-jdbc-driver-bundle</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>wrapper-bundle-hsqldb-driver</name>
  <url>www.adobe.com</url>
  <description>Exports the HSQL JDBC driver</description>
  <packaging>bundle</packaging>
  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.felix</groupId> 
        <artifactId>maven-bundle-plugin</artifactId>
        <version>1.4.3</version> 
        <extensions>true</extensions> 
        <configuration> 
         <instructions> 
            <Embed-Dependency>*</Embed-Dependency>
            <_exportcontents>org.hsqldb.*</_exportcontents>
          </instructions>
        </configuration> 
      </plugin>
    </plugins>
  </build>
  <dependencies>
    <dependency>
      <groupId>hsqldb</groupId>
      <artifactId>hsqldb</artifactId>
      <version>2.2.9</version>
    </dependency>
  </dependencies>
</project>

The following links open the download pages for some popular database products:

Configuring the JDBC Connection Pool Service

Add a configuration for the JDBC Connections Pool service that uses the JDBC driver to create data source objects. Your application code uses this service to obtain the object and connect to the database.

JDBC Connections Pool (com.day.commons.datasource.jdbcpool.JdbcPoolService) is a factory service. If you require connections that use different properties, for example read-only or read/write access, create multiple configurations.

When working with CQ there are several methods of managing the configuration settings for such services; see Configuring OSGi for full details.

The following properties are available to configure a pooled connection service. The property names are listed as they appear in the Web Console. The corresponding name for a sling:OsgiConfig node appears in parentheses. Example values are shown for an HSQLDB server and a database that has an alias of mydb:

JDBC Driver Class (jdbc.driver.class): The Java class to use that implements the java.sql.Driver interface, for example org.hsqldb.jdbc.JDBCDriver. The data type is String.
JDBC Connection URI (jdbc.connection.uri): The URL of the database to use to create the connection, for example jdbc:hsqldb:hsql//10.36.79.223:9001/mydb. The format of the URL must be valid for use with the getConnection method of the java.sql.DriverManager class. The data type is String.
Username (jdbc.username): The user name to use to authenticate with the database server. The data type is String.
Password (jdbc.password): The password to use for authentication of the user. The data type is String.
Validation Query (jdbc.validation.query): The SQL statement to use to verify that the connection is successful, for example select 1 from INFORMATION_SCHEMA.SYSTEM_USERS. The data type is String.
Readonly By Default (default.readonly): Select this option when you want the connection to provide read-only access. The data type is Boolean.
Autocommit By Default (default.autocommit): Select this option to create separate transactions for each SQL command that is sent to the database, and each transaction is automatically committed. Do not select this option when you are committing transactions explicitly in your code. The data type is Boolean.
Pool Size (pool.size): The number of simultaneous connections to be made available to the database. The data type is Long.
Pool wait (pool.max.wait.msec): The amount of time before a connection request times out. The data type is Long.
Datasource Name (datasource.name): The name of this data source. The data type is String.
Additional Service Properties (datasource.svc.properties): A set of name/value pairs that you want to append to the connection URL. The data type is String[].

The JDBC Connections Pool service is a factory. Therefore, if you use a sling:OsgiConfig node to configure the connection service, the name of the node must include the factory service PID followed by -alias. The alias that you use must be unique for all configuration nodess for that PID. An example node name is com.day.commons.datasource.jdbcpool.JdbcPoolService-myhsqldbpool.

Connecting to the Database

In your Java code, use the DataSourcePool service to obtain a javax.sql.DataSource object for the configuration that you created. The DataSourcePool service provides the getDataSource method that returns a DataSource object for a given data source name. As the method argument, use the value of the Datasource Name (or datasource.name) property that you specified for the JDBC Connections Pool configuration.

The following example JSP code obtains an instance of the hsqldbds data source, executes a simple SQL query, and displays the number of results that are returned.

JSP that performs a database lookup

<%@include file="/libs/foundation/global.jsp"%><%
%><%@page session="false"%><%
%><%@ page import="com.day.commons.datasource.poolservice.DataSourcePool" %><%
%><%@ page import="javax.sql.DataSource" %><%
%><%@ page import="java.sql.Connection" %><%
%><%@ page import="java.sql.SQLException" %><%
%><%@ page import="java.sql.Statement" %><%
%><%@ page import="java.sql.ResultSet"%><%
%><html>
<cq:include script="head.jsp"/>
<body>
<%DataSourcePool dspService = sling.getService(DataSourcePool.class);
  try {
     DataSource ds = (DataSource) dspService.getDataSource("hsqldbds");   
     if(ds != null) {
         %><p>Obtained the datasource!</p><%
         %><%final Connection connection = ds.getConnection();
          final Statement statement = connection.createStatement();
          final ResultSet resultSet = statement.executeQuery("SELECT * from INFORMATION_SCHEMA.SYSTEM_USERS"); 
          int r=0;
          while(resultSet.next()){
             r=r+1;
          } 
          resultSet.close();
          %><p>Number of results: <%=r%></p><%
      } 
   }catch (Exception e) {
        %><p>error! <%=e.getMessage()%></p><%
    } 
%></body>
</html>

Note

If the getDataSource method throws an exception becuase the datasource is not found, make sure the Connections Pool service configuration is correct. Verify the property names, values, and data types.

Your comments are welcome!

Did you notice a way we could improve the documentation on this page? Is something unclear or insufficiently explained? Please leave your comments below and we will make the appropriate changes. Comments that have been addressed, by improving the documentation accordingly, will then be removed.

COMMENTS

By Mark - 8:16 PM on Mar 01, 2013 Reply
I've read the first half of this article over and over and am clueless on how to proceed. I have an external database, SQL and MySQL. All I want to do is create a component that will connect to these databases and show some values.

After two days, I am still stuck on step 1. How to configure a connection and add my mysql/sql drivers to CQ5? I am a decent at Java (not an expert), have not used Maven and it seems there are implied steps here that I am missing.

A more step by step approach would have been much easier.
By Mark - 1:24 AM on Mar 02, 2013 Reply
Unsuccessfully spent over a day trying to get this working. No result.
Found a thread that says this page is out-of-date
http://forums.adobe.com/message/4725061

Creating a connection to a database should not be so complicated.

ADD A COMMENT

In order to post a comment, you need to sign-in.

Note: Customers with DayCare user accounts need to create a new account for use on day.com.