SQL connections using Java™ APIs
Connections to SQL databases (and other non-HTTP/HTTPS services) must be proxied out of AEM.
The exception to this rule is when dedicated egress ip address is in use, and the service is on Adobe or Azure.
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
Since secrets must not be stored in code, the SQL connection’s username and password are best provided via secret OSGi configuration variables, set using AIO CLI, or Cloud Manager APIs.
ui.config/src/jcr_root/apps/wknd-examples/osgiconfig/com.adobe.aem.wknd.examples.core.connections.impl.MySqlExternalServiceImpl.cfg.json
{
"username": "$[env:MYSQL_USERNAME;default=mysql-user]",
"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 SQL server web server, by way of the following Cloud Manager portForwards rule of the enableEnvironmentAdvancedNetworkingConfiguration operation.
...
"portForwards": [{
"name": "mysql.example.com",
"portDest": 3306,
"portOrig": 30001
}]
...
core/src/com/adobe/aem/wknd/examples/connections/impl/MySqlExternalServiceImpl.java
package com.adobe.aem.wknd.examples.core.connections.impl;
import com.adobe.aem.wknd.examples.core.connections.ExternalService;
import org.osgi.service.component.annotations.Activate;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.metatype.annotations.AttributeDefinition;
import org.osgi.service.metatype.annotations.AttributeType;
import org.osgi.service.metatype.annotations.Designate;
import org.osgi.service.metatype.annotations.ObjectClassDefinition;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
@Component
@Designate(ocd = MySqlExternalServiceImpl.Config.class)
public class MySqlExternalServiceImpl implements ExternalService {
private static final Logger log = LoggerFactory.getLogger(MySqlExternalServiceImpl.class);
// Get the proxy host using the AEM_PROXY_HOST Java environment variable provided by AEM as a Cloud Service
private static final String PROXY_HOST = System.getenv().getOrDefault("AEM_PROXY_HOST", "proxy.tunnel");
// Use the port mapped to the external MySQL service in the Cloud Manager API call
private static final int PORT_FORWARDS_PORT_ORIG = 30001;
private Config config;
@Override
public boolean isAccessible() {
log.debug("MySQL connection URL: [ {} ]", getConnectionUrl(config));
// Establish a connection with the external MySQL service
// This MySQL connection URL is created in getConnection(..) which will use the AEM_PROXY_HOST is it exists, and the proxied port.
try (Connection connection = DriverManager.getConnection(getConnectionUrl(config), config.username(), config.password())) {
// 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 SQL service
return true;
} catch (SQLException e) {
log.error("Unable to establish an connection with MySQL service using connection URL [ {} ]", getConnectionUrl(config), e);
}
return false;
}
/**
* Create a connection string to the MySQL using the AEM-provided AEM_PROXY_HOST and portForwards.portOrg port
* defined in the Cloud Manager API mapping.
*
* @param config OSGi configuration object
* @return the MySQL connection URI
*/
private String getConnectionUrl(Config config) {
return String.format("jdbc:mysql://%s:%d/wknd-examples", PROXY_HOST, PORT_FORWARDS_PORT_ORIG);
}
@Activate
protected void activate(Config config) throws ClassNotFoundException, SQLException {
this.config = config;
// Load the required MySQL Driver class required for Java to make the connection
// The OSGi bundle that contains this driver is deployed via the project's all project
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
}
@ObjectClassDefinition
@interface Config {
@AttributeDefinition(type = AttributeType.STRING)
String username();
@AttributeDefinition(type = AttributeType.STRING)
String password();
}
}
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>
...