Monday 16 November 2015

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

1) My SQL

Step 1: Create OSGI version of mysql Jar file


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

    No comments :

    Post a Comment