SQL connections using JDBC DataSourcePool

Last update: 2024-01-25
  • Created for:
  • Intermediate
    Developer

Connections to SQL databases (and other non-HTTP/HTTPS services) must be proxied out of AEM, including those made using AEM’s DataSourcePool OSGi service to manage the connections.

Advanced networking support

The following code example is supported by the following advanced networking options.

Ensure the the appropriate advanced networking configuration has been set up prior to following this tutorial.

No advanced networking Flexible port egress Dedicated egress IP address Virtual Private Network

OSGi configuration

The OSGi configuration’s connection string uses:

  • AEM_PROXY_HOST value via the OSGi configuration environment variable $[env:AEM_PROXY_HOST;default=proxy.tunnel] as the connection’s host
  • 30001 which is the portOrig value for the Cloud Manager port forward mapping 30001mysql.example.com:3306

Since secrets must not be stored in code, the SQL connection’s username and password are best provided via OSGi configuration variables, set using AIO CLI, or Cloud Manager APIs.

  • ui.config/src/jcr_root/apps/wknd-examples/osgiconfig/config/com.day.commons.datasource.jdbcpool.JdbcPoolService~wknd-examples-mysql.cfg.json
{
  "datasource.name": "wknd-examples-mysql",
  "jdbc.driver.class": "com.mysql.jdbc.Driver",
  "jdbc.connection.uri": "jdbc:mysql://$[env:AEM_PROXY_HOST;default=proxy.tunnel]:30001/wknd-examples",
  "jdbc.username": "$[env:MYSQL_USERNAME;default=mysql-user]",
  "jdbc.password": "$[secret:MYSQL_PASSWORD]"
}

The following aio CLI command can be used to set the OSGi secrets on a per environment basis:

$ aio cloudmanager:set-environment-variables --programId=<PROGRAM_ID> <ENVIRONMENT_ID> --secret MYSQL_USERNAME "mysql-user" --secret MYSQL_PASSWORD "password123"

Code example

This Java™ code example is of an OSGi service that makes a connection to an external MySQL database via AEM’s DataSourcePool OSGi service.
The DataSourcePool OSGi factory configuration in turn specifies a port (30001) that is mapped through the portForwards rule in the enableEnvironmentAdvancedNetworkingConfiguration operation to the external host and port, mysql.example.com:3306.

...
"portForwards": [{
    "name": "mysql.example.com",
    "portDest": 3306,
    "portOrig": 30001
}]
...
  • core/src/com/adobe/aem/wknd/examples/connections/impl/JdbcExternalServiceImpl.java
package com.adobe.aem.wknd.examples.core.connections.impl;

import com.adobe.aem.wknd.examples.core.connections.ExternalService;
import com.day.commons.datasource.poolservice.DataSourceNotFoundException;
import com.day.commons.datasource.poolservice.DataSourcePool;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@Component
public class JdbcExternalServiceImpl implements ExternalService {
    private static final Logger log = LoggerFactory.getLogger(JdbcExternalServiceImpl.class);

    @Reference
    private DataSourcePool dataSourcePool;

    // The datasource.name value of the OSGi configuration containing the connection this OSGi component will use.
    private static final String DATA_SOURCE_NAME = "wknd-examples-mysql";

    @Override
    public boolean isAccessible() {

        try {
            // Get the JDBC data source based on the named OSGi configuration
            DataSource dataSource = (DataSource) dataSourcePool.getDataSource(DATA_SOURCE_NAME);

            // Establish a connection with the external JDBC service
            // Per the OSGi configuration, this will use the injected $[env:AEM_PROXY_HOST] value as the host
            // and the port (30001) mapped via Cloud Manager API call
            try (Connection connection = dataSource.getConnection()) {

                // Validate the connection
                connection.isValid(1000);

                // Close the connection, since this is just a simple connectivity check
                connection.close();

                // Return true if AEM could reach the external JDBC service
                return true;
            } catch (SQLException e) {
                log.error("Unable to validate SQL connection for [ {} ]", DATA_SOURCE_NAME, e);
            }
        } catch (DataSourceNotFoundException e) {
            log.error("Unable to establish an connection with the JDBC data source [ {} ]", DATA_SOURCE_NAME, e);
        }

        return false;
    }
}

MySQL driver dependencies

AEM as a Cloud Service often requires you to provide Java™ database drivers to support the connections. Providing the drivers is typically best achieved by embedding the OSGi bundle artifacts containing these drivers to the AEM project via the all package.

Reactor pom.xml

Include the database driver dependencies in the reactor pom.xml and then reference them in the all subprojects.

  • pom.xml
...
<dependencies>
    ...
    <!-- MySQL Driver dependencies -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>[8.0.27,)</version>
        <scope>provided</scope>
    </dependency>
    ...
</dependencies>
...

All pom.xml

Embed the database driver dependency artifacts in the all package to they are deployed and available on AEM as a Cloud Service. These artifacts must be OSGi bundles that export the database driver Java™ class.

  • all/pom.xml
...
<embededs>
    ...
    <!-- Include the MySQL Driver OSGi bundles for deployment to the project -->
    <embedded>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <target>/apps/wknd-examples-vendor-packages/application/install</target>
    </embedded>
    ...
</embededs>

...

<dependencies>
    ...
    <!-- Add MySQL OSGi bundle artifacts so the <embeddeds> can add them to the project -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>provided</scope>
    </dependency>
    ...
</dependencies>
...

On this page