AEM Integration With Mysql

AEM integration with any external relational database is one of the common requirements in today’s world. Especially in case of legacy systems, where the data is already present in a relational database & migrating that data to AEM node structure is either not feasible or way too complicated.
This blog post will serve as a step by step guide of integrating AEM with one such database i.e. MySQL. 

There are many ways with which AEM enables integration with an external database. One of the most common & standard practices of database integration is by using the JDBC Connection Pool Service. Since AEM is supported by Felix OSGI Container, hence we need to deploy the relational database (In our example, MySQL) driver’s jar as a bundle into the container. 

First step for database integration is to have a database driver available in AEM.
MySQL provides a bundle of JDBC drivers. This can be downloaded from – https://dev.mysql.com/downloads/connector/j/
Once downloaded, you can upload this bundle in AEM & it will start coming in the list of bundles as displayed below. This driver bundle will export the packages that are required for interacting with the database server.

The Next step is to configure the JDBC Connection Pool Service, which will use the JDBC driver to create data source objects. JDBC Connections Pool (com.day.commons.datasource.jdbcpool.JdbcPoolService) is a factory service available within AEM, that allows you to create multiple database configurations.
JDBC Connection Pool Service has configurations such as JDBC Driver Class (jdbc.driver.class), JDBC Connection URI (jdbc.connection.uri), Username(jdbc.username) & Password (jdbc.password) etc.
Once configured your JDBC Connection Pool Service should look like as displayed below

·
Once everything is in place, the next step is to access the database from AEM. In the OSGi service, you can use the DataSourcePool service to obtain javax.sql.DataSource object for the configuration that you created.
Please note that DataSourcePool service is present in Day Commons Library Data Source Pool Jar file & this needs to be defined as the dependency in your pom.xml file otherwise you will not be able to get the reference to DataSourcePool service.


· Now you can use the reference of DataSourcePool in your OSGI service & it can be used to fetch the data source, connection. In our example, the name of the data source is “test” as configured in step 2.



Once you have the connection, you can create the SQL statement, execute the query for CRUD operations & get the corresponding result set.