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 ArchivesClick 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
Configure the Datasource pool:
Login to http://localhost:4502/system/console/configMgrSelect 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();
}
}
}
}
how to configure the osgi class and method in AEM adaptive form to fill a field with data
ReplyDeleteGetting null pointer error while connecting to JDBC
ReplyDeleteHello,
ReplyDeleteI 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?
Please check the status of the Orcle JDBC bundle
DeleteThe status of my Oracle JDBC bundle is "Active".
DeleteThanks for the response