[DATABASE] USE DATASOURCEPOOL WITHIN OSGI BUNDLE TO CONNECT MYSQL

Goal:

Create a CQ/AEM project to connect mysql by

Create project:

mvn archetype:generate \    -DarchetypeRepository=http://repo.adobe.com/nexus/content/groups/public/ \    -DarchetypeGroupId=com.day.jcr.vault \    -DarchetypeArtifactId=multimodule-content-package-archetype \    -DarchetypeVersion=1.0.2 \    -DgroupId=mysqldemo \    -DartifactId=mysqldemo \    -Dversion=1.0-SNAPSHOT \    -Dpackage=com.mysqldemo \    -DappsFolderName=mysqldemo \    -DartifactName="Mysql Demo" \    -DcqVersion="5.6.1" \    -DpackageGroup="Mysql Demo"

Update bundle pom:

<dependency>            <groupId>com.day.commons</groupId>            <artifactId>day.commons.datasource.poolservice</artifactId>            <version>1.0.10</version>            <scope>provided</scope> </dependency>

Update content pom:

<dependencies> ... <dependency>            <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.22</version> <scope>provided</scope> </dependency> ... </dependencies> ... <embeddeds> ... <embedded> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <target>${install.target}</target> </embedded> ... </embeddeds>

Configure the DataSourcePool connection properties:

Add a configuration for the JDBC Connections Pool service that uses the JDBC driver to create data source objects. The OSGi bundle created in this development article uses this service to connect to the MySQL database. For information, see Connecting to SQL Databases.  

To configure a DataSourcePool, peform these tasks:

1. Login to Adobe CQ’s Apache Felix Web Console at http://server:port/system/console/bundles (default admin user = admin with password= admin).

2. Click the Configuration tab..

3. Click the + icon that appearts in the JDBC Configuration Pool row (in AEM 6, the row is titled Day Commons JDBC Connections Pool).

4. Enter the following values:

• JBDC Driver class - the driver class to use to connect to the database. To connect to MySQL, enter com.mysql.jdbc.Driver.
• JDBC connection URI - the URI to the database. In this example, enter jdbc:mysql://localhost:3306/databasename.
• Username - the user name to use to connect to MySQL.
• Password - the corresponding password.
• Datasource name - the datasource name. This is the value that you reference in the Java logic located in the OSGi bundle. In this example, enter Customer.

5. Click Save.

Code to get connection:

@Reference private DataSourcePool source; // Returns a connection using the configured DataSourcePool private Connection getConnection() { DataSource dataSource = null; Connection con = null; try { // Inject the DataSourcePool right here! dataSource = (DataSource) source.getDataSource("Customer"); con = dataSource.getConnection(); return con; } catch (Exception e) { e.printStackTrace(); } return null; }