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:
- A JDBC driver bundle, which lets Java communicate with the database
- A data source configuration, which represents the connection details of your database
- 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 theojdbc8
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 isojdbc8
) into this bundle.
If you add this POM to your project as a Maven submodule, and embed it in your all
package, 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:
- 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.
- 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 😀
Top comments (0)