Connecting to SQL Databases

Last update: May 3, 2023

CREATED FOR:

  • Developer
CAUTION
AEM 6.4 has reached the end of extended support and this documentation is no longer updated. For further details, see our technical support periods. Find the supported versions here.

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

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="https://maven.apache.org/POM/4.0.0"
  xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://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.

chlimage_1-7

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.
NOTE
To learn how to inject a DataSourcePool into an OSGi bundle, see Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.
Previous page
Next page

Experience Manager


The Perfect Blend: A New Era of Collaboration with AEM and Workfront

Adobe Customer Success Webinars

Wednesday, Apr 2, 5:00 PM UTC

Explore how Adobe Experience Manager and Workfront integrate to help teams move from ideation to delivery without the usual bottlenecks, ensuring content is organized, on-brand, and ready to go live faster.

Register

Rapid Feature Releases with AEM Cloud: Telegraph Media Group’s RDE Strategy

Online | Session | Intermediate

Hear how Telegraph Media Group, the award-winning publisher of The Daily Telegraph, The Sunday Telegraph, The Telegraph Magazine,...

Wed, Mar 19, 3:30 PM PDT (10:30 PM UTC)

Register

How EY Enhanced Regulatory Knowledge Management with Adobe Solutions

Online | Session | General Audience

Delve into how EY leverages Adobe solutions to enhance regulatory knowledge management. EY leverages search analytics and personalization...

Wed, Mar 19, 4:00 PM PDT (11:00 PM UTC)

Register

Connect with Experience League at Summit!

Get front-row access to top sessions, hands-on activities, and networking—wherever you are!

Learn more

Register now