Sunday, March 8, 2015

How to connect to oracle database using datasource pool from OSGI- Adobe Experience Manager(AEM)

How to connect to oracle database using datasource pool from OSGI- Adobe Experience Manager(AEM)

This post will explain how to connect to oracle database using datasource pool from Adobe Experience Manager(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

osgi_bundle

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.


osgi_bundle


Click Next and click Finish

Right click the the created project and click on Export

osgi_bundle

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

osgi_bundle

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

osgi_bundle

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)

datasource_pool


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();
}
}
}

}



5 comments:

  1. how to configure the osgi class and method in AEM adaptive form to fill a field with data

    ReplyDelete
  2. Getting null pointer error while connecting to JDBC

    ReplyDelete
  3. Hello,
    I followed these steps to connect my servlet to an Oracle DB.
    But in the log I have this error:

    Caused by: java.sql.SQLException: oracle.jdbc.OracleDriver not found by org.apache.sling.datasource [65]

    What could be the problem?

    ReplyDelete
    Replies
    1. Please check the status of the Orcle JDBC bundle

      Delete
    2. The status of my Oracle JDBC bundle is "Active".

      Thanks for the response

      Delete