SQL connections using JDBC DataSourcePool
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.
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 host30001
which is theportOrig
value for the Cloud Manager port forward mapping30001
→mysql.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>
...