Connect to a MySQL Database from AEM

Jul 24, 2015 · 3 minute read · Comments
OSGI AEM CQ

AEM allows to connect to external RDBMs via JBDC using OSGI bundles. In this post we will show how to connect to a mysql DB from AEM.

For this we need to perform some steps that will be described below

Adding MySQL Connector Jar as a Bundle

As AEM is backed by Felix OSGI Container, because of that we need to deploy the MySql driver’s jar as a bundle into the container.

For that we created a new maven module into our project to contain the driver. The pom file was created based on the AEM docs. Our pom.xml looks similar but we needed to add few different configurations to the bundle plugin.

The pom.xml can be seen below:

<?xml version="1.0"?>
<project xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <modelVersion>4.0.0</modelVersion>
  <parent>
    <groupId>com.xicojunior</groupId>
    <artifactId>xicojunior-example</artifactId>
    <version>0.0.1-SNAPSHOT</version>
  </parent>
  <groupId>com.xicojunior</groupId>
  <artifactId>mysql-bundle</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>bundle</packaging>
      <build>
        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <Embed-Dependency>
                            *
                        </Embed-Dependency>
                        <Import-Package>javax.naming,!com.mchange.v2.c3p0, !org.jboss.resource.adapter.jdbc, !org.jboss.resource.adapter.jdbc.vendor</Import-Package>
                        <_exportcontents>com.mysql.jdbc.*</_exportcontents>                        
                    </instructions>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <profiles>
        <!-- Development profile: install only the bundle -->
        <profile>
            <id>autoInstallBundle</id>
            <activation>
            </activation>
            <build>
                <plugins>
                    <plugin>
                        <groupId>org.apache.sling</groupId>
                        <artifactId>maven-sling-plugin</artifactId>
                        <configuration>
                            <!-- Note that this requires /apps/xicojunior/install to exist!!          -->
                            <!--    This is typically the case when ui.apps is deployed first                -->
                            <!--    Otherwise, create /apps/xicojunior/install manually (CRXDE|Lite)  -->
                            <slingUrlSuffix>/apps/xicojunior/install/</slingUrlSuffix>
                            <failOnError>true</failOnError>
                        </configuration>
                    </plugin>
                </plugins>
            </build>
        </profile>
    </profiles>
    <dependencies>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>

    </dependencies>
</project>

In order to make it work we needed to include the tag to specify which packages should be imported(javax.naming) and which ones should not (!org.jboss.resource.adapter.jdbc, !org.jboss.resource.adapter.jdbc.vendor).

Once we do that, we can deploy it using the maven command

mvn clean install -PautoInstallPackage

Then we can check if our bundle is deployed using the Felix Console

http://localhost:4502/system/console/bundles

We can filter by mysql and see it in the console.

If you can see it then everything went ok.

Creating the DataSource on AEM

The DataSource configuration is an OSGI Configuration, so in order to create our data source we need to create a new sling:OsgiConfig node, the node name should contain the factory service PID and the alias for our configuration. In our example will be: com.day.commons.datasource.jdbcpool.JdbcPoolService-myDataSource

As it is a OSGI config, it should be placed in a config folder, so in our example it will be created on the ui.apps module in the following path:

/apps/xicojunior/config

Our node configuration can be seen below:

The node properties are basically the information needed to connect to the DB.

To deploy that run the following maven command on ui.apps module:

mvn clean install -PautoInstallPackage

Once you do that you can check the node create on CRXDE Light.

To verify if all configurations were fine, we can check on Felix console if the DataSource was created:

http://localhost:4502/system/console/services

Console

Using the DataSource

As we saw in the last image the data source is created as a service using the javax.sql.DataSource interface, so we can refer to it on our Service classes.

To do that we can simply use the @Reference annotation setting the target property like below:

@Reference(target="datasource.name=myDataSource")   
DataSource myDataSource;

Now you can retrive data from MySQL from your Aem OSGI bundles.

I hope you enjoyed it!

See you in the next post.

Xico Junior

Connect to a MySQL Database from AEM

Adding MySQL Connector Jar as a Bundle

Creating the DataSource on AEM

Using the DataSource