Connecting to SQL Databases connecting-to-sql-databases

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

Bundling the JDBC Database Driver 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 plug-in 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 does not start on installation. Two possible solutions are as follows:

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 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 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:

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 nodes for that PID. An example node name is com.day.commons.datasource.jdbcpool.JdbcPoolService-myhsqldbpool.

Connecting to the Database 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 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>