How to connect to oracle database using DataSource pool from OSGi

This post will explain how to connect to oracle database using datasource pool from AEM

Convert the JDBC driver to OSGI bundle: 

In eclipse - File-->New-->Plug-in Development-->Plug-in from Existing JAR Archives
Click on Add External and Select the JDBC jar file

Click Next and enter the required details

Project name - OracleDriver
Plug-in ID - com.jdbc.oracle
Plug-in vendor - Oracle
Select the Execute Environment
Select an OSGi framework and select standard
Un-Select Unzip the JAR archives into the project and select Update references to the JAR files.



Click Next and click Finish

Right click the the created project and click on Export



Select Plug-in Development --> Deploying plug-ins and fragments
Specify the destination directory and make sure the plug-in is selected(com.jdbc.oracle)



Click on Finish, this will create the OSGI bundle in the selected directory.
Install the OSGI bundle:
Login to http://localhost:4502/system/console/bundles
Click on Install/Update
Choose the OSGI bundle
Select Start Bundle and refresh Packages



Click on Install or Update

Configure the Datasource pool:Login to http://localhost:4502/system/console/configMgr

Select JDBC Connection Pool and add new Connection Pool(click on plus)

Enter the required details

JDBC driver class - oracle.jdbc.OracleDriver
JDBC connection URI - jdbc:oracle:thin:@hostname:port:SID or Service Name
Username
Password
Validation query - SELECT 1 FROM DUAL
Pool size - 10(configure based on the requirement)
Datasource name - SampleOracleDS(provide the datasource name to uniquely identify the DS)



Save the configurations

Maven dependency:

<dependency>
<groupId>com.day.commons</groupId>
<artifactId>day.commons.datasource.poolservice</artifactId>
<version>1.0.10</version>
<scope>provided</scope>
</dependency>
Service class to get the database connection and to perform the operations:

package com.tr.commerce.connector.common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Reference;
import org.apache.felix.scr.annotations.Service;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.day.commons.datasource.poolservice.DataSourcePool;

@Component(immediate = true, metatype = true)
@Service(value = DatabaseConnectionImpl.class)
public class DatabaseConnectionImpl {

protected static final Logger log = LoggerFactory.getLogger(DatabaseConnectionImpl.class);

@Reference
private DataSourcePool dataSourceService;

public Connection getDataBaseConnection(String dataSourceName) {
Connection conn = null;
try {
DataSource dataSource = (DataSource) dataSourceService.getDataSource(dataSourceName);
conn = dataSource.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

public void executeQuery() {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
con = getDataBaseConnection("SampleOracleDS");
stmt = con.createStatement();
rs = stmt.executeQuery("select empno,empname from Employee");
while (rs.next()) {
System.out.println("Employee ID=" + rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null)
rs.close();
if (stmt != null)
stmt.close();
if (con != null)
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

}