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

Use Case: Connect to external datasource using CQ

Solution:

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

• 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

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

• 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)

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,