Connect to External Data Source (Teradata / MySQL / Oracle) in CQ / AEM

1) My SQL

Step 1: Create OSGI version of mysql Jar file

• Download MySQL JDBC Driver From http://dev.mysql.com/downloads/connector/j 
• Open eclipse and select file -> New -> Other -> Plugin development -> Plug-in from existing Jar Archive

• Click next and then select add external. Select jar file you downloaded above and select next
•  Give Project Name -> Select Location -> Make sure that Analyze Library Content is checked -> In Target Platform select an OSGI framework -> from drop down select standard -> Check unzip jar file and update reference -> click finish
• 
  

• An Plugin Development prospective will open 
• Click on export tab and make sure that all dependencies are exported (If they are not there, Click on add then select all)

• Click on Dependencies and make sure that Imported packages are there. If it is not there click on MENIFEST.MF tab and then add following import statement 
• Once all import and export statement is added your MENIFEST.MF will have following data

	Manifest-Version: 1.0
	Bundle-ManifestVersion: 2
	Bundle-Name: Mysql-Osgi
	Bundle-SymbolicName: com.osgi.mysql
	Bundle-Version: 1.0.0
	Bundle-RequiredExecutionEnvironment: JavaSE-1.7
	Import-Package: javax.management,
	javax.naming,
	javax.naming.spi,
	javax.net,
	javax.net.ssl,
	javax.sql,
	javax.transaction.xa,
	javax.xml.parsers,
	javax.xml.stream,
	javax.xml.transform,
	javax.xml.transform.dom,
	javax.xml.transform.sax,
	javax.xml.transform.stax,
	javax.xml.transform.stream,
	org.slf4j,
	org.w3c.dom,
	org.xml.sax,
	org.xml.sax.helpers
	Export-Package: com.mysql.fabric,
	com.mysql.fabric.hibernate,
	com.mysql.fabric.jdbc,
	com.mysql.fabric.proto.xmlrpc,
	com.mysql.fabric.xmlrpc,
	com.mysql.fabric.xmlrpc.base,
	com.mysql.fabric.xmlrpc.exceptions,
	com.mysql.jdbc,
	com.mysql.jdbc.authentication,
	com.mysql.jdbc.exceptions,
	com.mysql.jdbc.exceptions.jdbc4,
	com.mysql.jdbc.integration.c3p0,
	com.mysql.jdbc.integration.jboss,
	com.mysql.jdbc.interceptors,
	com.mysql.jdbc.jdbc2.optional,
	com.mysql.jdbc.jmx,
	com.mysql.jdbc.log,
	com.mysql.jdbc.profiler,
	com.mysql.jdbc.util,
	org.gjt.mm.mysql

view rawMENIFEST.MF hosted with ❤ by GitHub

• Then right click on your project and then select export 
• Select Deployable Deployable plug-in and fragments

• Click Next and select your plug-in 
• From Directory section select location where you want to upload.
• Click finish. This will store OSGI jar in to location you selected.
• Once you have MySQL Osgi bundle. Install that bundle either using felix console or adding it through deployment process

• If you are getting error saying javax.Naming not getting resolved then you have to add following line in sling.properties under crx-quickstart folder

org.osgi.framework.bootdelegation= javax.naming.*, ${org.apache.sling.launcher.bootdelegation}

• Once bundle is up is running, You need to go to felix osgi config. Search For JDBC connection pool and then click '+'
• Fill all the information about your datasource

• Go to your application logic and test your code

	<%@page session="false"%><%
	%><%@ page import="com.day.commons.datasource.poolservice.DataSourcePool" %><%
	%><%@ page import="javax.sql.DataSource" %><%
	%><%@ page import="java.sql.Connection" %><%
	%><%@ page import="java.sql.SQLException" %><%
	%><%@ page import="java.sql.Statement" %><%
	%><%@ page import="java.sql.ResultSet"%><%
	%><%@ page import="java.sql.PreparedStatement"%>
	%><%@ page import="java.sql.DriverManager"%>
	DataSourcePool dspService = sling.getService(DataSourcePool.class);
	try {
	DataSource ds = (DataSource) dspService.getDataSource("mysql_datasource");
	if(ds != null) {
	%><p>Obtained the datasource!</p><%
	%><%
	out.println("Trying to get connection Connection done");
	final Connection connection = ds.getConnection();
	out.println("Connection done");
	final Statement statement = connection.createStatement();
	out.println("Create Statement done");
	final ResultSet resultSet = statement.executeQuery("some-query'");
	int r=0;
	while(resultSet.next()){
	r=r+1;
	}
	resultSet.close();
	%><p>Number of results: <%=r%></p><%
	}
	}catch (Exception e) {
	%><p>error! <%=e.getMessage()%></p><%
	}

view rawmy_sql_cq_test hosted with ❤ by GitHub

If you are having trouble creating osgi version of mysql jar file, You can download it from here

2) Teradata

• All the steps for Teradata will remain same except you need to create teradata OSGI bundle using Teradata JDBC connector.
• First Download teradatajdbc connector from here https://code.google.com/p/kyunra-java/downloads/detail?name=terajdbc4.jar
• Then Download tdconfig jar from here https://code.google.com/p/kyunra-java/downloads/detail?name=tdgssconfig.jar
• Follow above steps to create osgi version of jar file (Everything will remain same except this time you need to select two jar file for creating osgi version of it)
• Your Final MENIFEST.MF will look like this

	Manifest-Version: 1.0
	Bundle-ManifestVersion: 2
	Bundle-Name: Taradatajdbc
	Bundle-SymbolicName: taradatajdbc
	Bundle-Version: 1.0.0
	Bundle-ClassPath: .
	Export-Package: com.ncr.teradata,
	com.teradata.jdbc,
	com.teradata.jdbc.client,
	com.teradata.jdbc.encode,
	com.teradata.jdbc.jdbc,
	com.teradata.jdbc.jdbc.console,
	com.teradata.jdbc.jdbc.fastexport,
	com.teradata.jdbc.jdbc.fastload,
	com.teradata.jdbc.jdbc.monitor,
	com.teradata.jdbc.jdbc.raw,
	com.teradata.jdbc.jdbc_3.dbmetadata,
	com.teradata.jdbc.jdbc_4,
	com.teradata.jdbc.jdbc_4.ifsupport,
	com.teradata.jdbc.jdbc_4.io,
	com.teradata.jdbc.jdbc_4.logging,
	com.teradata.jdbc.jdbc_4.parcel,
	com.teradata.jdbc.jdbc_4.statemachine,
	com.teradata.jdbc.jdbc_4.util,
	com.teradata.jdbc.jdk14,
	com.teradata.jdbc.jdk6,
	com.teradata.jdbc.resource,
	com.teradata.tdgss.jalgapi,
	com.teradata.tdgss.jgssp2gss,
	com.teradata.tdgss.jgssp2ldap,
	com.teradata.tdgss.jgssp2td1,
	com.teradata.tdgss.jgssp2td2,
	com.teradata.tdgss.jgssspi,
	com.teradata.tdgss.jtdgss
	Bundle-RequiredExecutionEnvironment: JavaSE-1.7
	Import-Package: javax.crypto,
	javax.crypto.spec,
	javax.naming,
	javax.naming.spi,
	javax.security.auth,
	javax.security.auth.callback,
	javax.security.auth.login,
	javax.sql,
	javax.xml.parsers,
	javax.xml.stream,
	javax.xml.transform,
	javax.xml.transform.dom,
	javax.xml.transform.sax,
	javax.xml.transform.stax,
	javax.xml.transform.stream,
	org.ietf.jgss,
	org.w3c.dom,
	org.xml.sax,
	org.xml.sax.helpers

view rawMENIFEST.MF hosted with ❤ by GitHub

• Upload final jar file in CQ, make sure that it is active

• Open OSGI config to create config for teradata connection pool

• You can then test it using same code (Just use teradata data source)

	<%@page session="false"%><%
	%><%@ page import="com.day.commons.datasource.poolservice.DataSourcePool" %><%
	%><%@ page import="javax.sql.DataSource" %><%
	%><%@ page import="java.sql.Connection" %><%
	%><%@ page import="java.sql.SQLException" %><%
	%><%@ page import="java.sql.Statement" %><%
	%><%@ page import="java.sql.ResultSet"%><%
	%><%@ page import="java.sql.PreparedStatement"%>
	%><%@ page import="java.sql.DriverManager"%>
	DataSourcePool dspService = sling.getService(DataSourcePool.class);
	try {
	DataSource ds = (DataSource) dspService.getDataSource("mysql_datasource");
	if(ds != null) {
	%><p>Obtained the datasource!</p><%
	%><%
	out.println("Trying to get connection Connection done");
	final Connection connection = ds.getConnection();
	out.println("Connection done");
	final Statement statement = connection.createStatement();
	out.println("Create Statement done");
	final ResultSet resultSet = statement.executeQuery("some-query'");
	int r=0;
	while(resultSet.next()){
	r=r+1;
	}
	resultSet.close();
	%><p>Number of results: <%=r%></p><%
	}
	}catch (Exception e) {
	%><p>error! <%=e.getMessage()%></p><%
	}

view rawmy_sql_cq_test hosted with ❤ by GitHub

You can also download OSGI version of teradata jar file from here

Note: If You want to call instance of your Connection from Java class (Not from service) You can do something like this,

	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.SQLException;
	public class MySQLConnectionHelper {
	private static volatile MySQLConnectionHelper mySQLConnectionHelper;
	private MySQLConnectionHelper() {
	try {
	Class.forName("com.mysql.jdbc.Driver").newInstance();
	//For Teradata Class.forName("com.teradata.jdbc.TeraDriver").newInstance();
	} catch (InstantiationException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	} catch (IllegalAccessException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	} catch (ClassNotFoundException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
	}
	public static synchronized Connection getConnection(final String url,
	final String uid, final String pwd) throws SQLException {
	if (mySQLConnectionHelper == null) {
	mySQLConnectionHelper = new MySQLConnectionHelper();
	}
	try {
	return DriverManager.getConnection(url, uid, pwd);
	} catch (SQLException e) {
	throw e;
	}
	}
	public static void close(Connection connection) {
	try {
	if (connection != null) {
	connection.close();
	}
	} catch (SQLException e) {
	e.printStackTrace();
	}
	}
	}

view rawMySQLConnectionHelper.java hosted with ❤ by GitHub