AEM: Connect AEM to an External database (Oracle example)

It can happen when implementing AEM that you suddenly realize that the JCR database is not the best solution to store certain types of data.

For example, the JCR-SQL2 dialect is missing a lot of features that you'd expect from an typical database, so queries can become cumbersome. These limitations can be a real bummer if you're trying to store thousands of products, blog posts or whatnot. For that reason, you may want to store certain data in an external database such as Oracle, MySQL, Neo4J, etc.

In enterprise environments, Oracle is often favored for RDBMS needs due its advanced features and support. With that in mind, I've decided to use Oracle as the basis for this tutorial, but the principles apply to any other external database.

There are essentially 3 components required to integrate AEM with an external database:

1. A JDBC driver bundle, which lets Java communicate with the database
2. A data source configuration, which represents the connection details of your database
3. A client, some Java class which actually runs a query

Follow this tutorial to find out how to set this all up. I've provided a Github link at the bottom of the article if you want to see a concrete implementation of this tutorial.

Wrapping the oracle JDBC driver

I've seen many articles online that simply mention "create an OJDBC wrapper bundle" or give step-by-step instructions to manually create the bundle using Eclipse plugins. I would prefer to have an automated wrapper that can easily be changed and/or bundled with the rest of your AEM application, so let's use Maven to create our bundle.

Here is a POM file to wrap the ojdbc8 driver:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>com.theopendle</groupId>
        <artifactId>demo</artifactId>
        <version>1.0.0-SNAPSHOT</version>
        <relativePath>../pom.xml</relativePath>
    </parent>

    <artifactId>demo.ojdbc-bundle</artifactId>
    <packaging>bundle</packaging>
    <name>Demo - OJDBC bundle</name>
    <description>OSGi wrapper for Oracle JDBC jar.</description>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <version>3.5.0</version>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <Bundle-License>non-free</Bundle-License>
                        <Bundle-Vendor>Oracle</Bundle-Vendor>
                        <_exportcontents>*</_exportcontents>
                        <Export-Package>
                            oracle.core.*;version="${project.version}",
                            oracle.jdbc.*;version="${project.version}",
                            oracle.jpub.*;version="${project.version}",
                            oracle.net.*;version="${project.version}",
                            oracle.security.*;version="${project.version}",
                            oracle.sql.*;version="${project.version}"
                        </Export-Package>
                        <Import-Package>*;resolution:=optional</Import-Package>
                        <Private-Package>!*</Private-Package>
                        <Embed-Dependency>*;scope=compile|runtime;type=!pom;inline=true</Embed-Dependency>
                    </instructions>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>21.7.0.0</version>
        </dependency>
    </dependencies>
</project>

If you're not familiar with the maven-bundle-plugin plugin, here are some pointers:

• The <Export-Package> tag contains the list of packages in the ojdbc8 JAR file that you might conceivably need to call from your Java code. We are making the classes from those packages available in OSGi so they can be imported by your Java code.
• The <Import-Package>*;resolution:=optional</Import-Package> marks all imports as optional so we aren't forced to satisfy them with another bundle.
• The <Embed-Dependency>*;scope=compile|runtime;type=!pom;inline=true</Embed-Dependency> tag tells Maven to embed all compile or runtime dependencies (in this case our only dependency is ojdbc8) into this bundle.

If you add this POM to your project as a Maven submodule, and embed it in your allpackage, you can easily deploy this bundle to AEM as a part of your application. See the Git diff at the bottom of the article for specifics.

The end result should be a new bundle running in the OSGi container, like so:

Now that the diver is available in OSGi, let's use it to open a database connection.

Configuring the DataSourcePool

The com.day.commons.datasource.poolservice.DataSourcePool Java class is used to to create connections to a database. It pulls its configuration from the com.day.commons.datasource.jdbcpool.JdbcPoolService configuration factory.

You can configure your datasource by going to /system/console/configMgr/com.day.commons.datasource.jdbcpool.JdbcPoolService but here is my version as cfg.json file:

{
  "jdbc.password": "test",
  "jdbc.driver.class": "oracle.jdbc.driver.OracleDriver",
  "datasource.name": "oracle",
  "jdbc.connection.uri": "jdbc:oracle:thin:@localhost:1521:test",
  "jdbc.validation.query": "SELECT 1 FROM DUAL",
  "default.readonly": false,
  "default.autocommit": false,
  "jdbc.username": "test",
  "pool.max.wait.msec": 1000,
  "pool.size": 10
}

Testing the connection

Now everything should be ready for you to start making queries to the database, so let's give it a go.

In your core java module, write a class that opens a connection to the database. If you used my configuration above, you can run use the Connection::isValid method to run the validation query, in my case SELECT 1 FROM DUAL.

Here is an example of an service that will let us easily open connections in the future. For now it does nothing but check the validity of the connection on startup.

@Slf4j
@Component(service = OracleDataSource.class, immediate = true)
public class OracleDataSource {
    public static final String DATA_SOURCE_NAME = "oracle";

    @Reference
    private DataSourcePool dataSourcePool;

    @Activate
    public void activate() {
        try {
            final DataSource dataSource = (DataSource) dataSourcePool.getDataSource(DATA_SOURCE_NAME);

            try (final Connection connection = dataSource.getConnection()) {
                if (connection == null) {
                    log.error("Could not establish connection to <{}>", DATA_SOURCE_NAME);
                    return;
                }
                log.info("Connection is valid: <{}>", connection.isValid(1000));
            }

        } catch (final SQLException e) {
            log.error("Could not establish connection to <{}>", DATA_SOURCE_NAME, e);

        } catch (final DataSourceNotFoundException e) {
            log.error("Could not find data source with name <{}>", DATA_SOURCE_NAME, e);
        }
    }
}

Now if you build and install your core bundle, you should see a log like this:

11.03.2023 14:00:28.568 *INFO* [OsgiInstallerImpl] com.theopendle.core.services.OracleDataSource Connection is valid: <true>

Conclusion

In this tutorial you learned how to bundle a JDBC driver and use it to open a connection to an Oracle database.

I usually like to tackle specific use cases in my tutorials to make them more engaging and because I try to anticipate what developers will be Googling 😉

However, there are several general takeaways in this article:

1. Bundling a JAR at build-time using a POM file in a Maven submodule is applicable for any dependency, not just a JDBC driver. And it is much easier and better practice than for your applications to realy on bundles that must be built by hand and deployed to OSGi as an installation step of your AEM instances.
2. Using a OSGi service to represent your data source and provide methods like opening and validating connections, handling timeouts, connection pool maintenance, etc. is a much better practice than to have those elements implemented in business logic.

See this link to a Git diff to see all the changes that make this feature possible.

I hope this was useful for you! If you liked it, don't hesitate to follow me here or on LinkedIn 😀