Persisting Adobe CQ data in a relational database

[nitin_eusebius]

Hi Friends,

I am trying to follow along the example on this link http://helpx.adobe.com/adobe-cq/using/persisting-cq-data-relational-database.html and somehow my persist.json.jsp is not able to resolved the following

com.adobe.cq.CustomerService custService = new com.adobe.cq.CustomerService();

I can see that the bundle is "active" and tried to deploy and re-deploy. This is what my manifest looks like

Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: Sqlosgi
Bundle-SymbolicName: sqlosgi
Bundle-Version: 1.0.0
Export-Package: com.adobe.cq,
 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
Import-Package: com.adobe.cq,
 javax.xml.transform.stream,
 javax.net.ssl,
 javax.xml.transform.sax,
 javax.naming.spi,
 javax.sql,
 javax.xml.stream,
 javax.xml.parsers,
 javax.xml.transform.stax,
 javax.xml.transform.dom,
 org.xml.sax,
 javax.net,
 org.w3c.dom,
 javax.naming,
 javax.management,
 javax.transaction.xa,
 javax.xml.transform,
 org.xml.sax.helpers
Bundle-RequiredExecutionEnvironment: JavaSE-1.7

Also this is how the system console looks

                                 

Symbolic Name	sqlosgi
Version	1.0.0
Bundle Location	inputstream:sqlosgi_1.0.0.jar
Last Modification	Wed Aug 14 15:59:09 PDT 2013
Start Level	20
Exported Packages	com.adobe.cq,version=0.0.0 com.mysql.jdbc,version=0.0.0 com.mysql.jdbc.authentication,version=0.0.0 com.mysql.jdbc.exceptions,version=0.0.0 com.mysql.jdbc.exceptions.jdbc4,version=0.0.0 com.mysql.jdbc.integration.c3p0,version=0.0.0 com.mysql.jdbc.integration.jboss,version=0.0.0 com.mysql.jdbc.interceptors,version=0.0.0 com.mysql.jdbc.jdbc2.optional,version=0.0.0 com.mysql.jdbc.jmx,version=0.0.0 com.mysql.jdbc.log,version=0.0.0 com.mysql.jdbc.profiler,version=0.0.0 com.mysql.jdbc.util,version=0.0.0 org.gjt.mm.mysql,version=0.0.0
Imported Packages	javax.management,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.naming,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.naming.spi,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.net,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.net.ssl,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.sql,version=0.0.0.1_007_JavaSE from org.apache.felix.framework (0) javax.transaction.xa,version=1.1.0 from org.apache.aries.transaction.manager (16) javax.xml.parsers,version=0.0.0 from org.apache.felix.framework (0) javax.xml.stream,version=0.0.0 from org.apache.felix.framework (0) javax.xml.transform,version=0.0.0 from org.apache.felix.framework (0) javax.xml.transform.dom,version=0.0.0 from org.apache.felix.framework (0) javax.xml.transform.sax,version=0.0.0 from org.apache.felix.framework (0) javax.xml.transform.stax,version=0.0.0 from org.apache.felix.framework (0) javax.xml.transform.stream,version=0.0.0 from org.apache.felix.framework (0) org.w3c.dom,version=0.0.0 from org.apache.felix.framework (0) org.xml.sax,version=0.0.0 from org.apache.felix.framework (0) org.xml.sax.helpers,version=0.0.0 from org.apache.felix.framework (0)
Manifest Headers	Bundle-ManifestVersion: 2 Bundle-Name: Sqlosgi Bundle-RequiredExecutionEnvironment: JavaSE-1.7 Bundle-SymbolicName: sqlosgi Bundle-Version: 1.0.0 Export-Package: com.adobe.cq, 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 Import-Package: com.adobe.cq, javax.xml.transform.stream, javax.net.ssl, javax.xml.transform.sax, javax.naming.spi, javax.sql, javax.xml.stream, javax.xml.parsers, javax.xml.transform.stax, javax.xml.transform.dom, org.xml.sax, javax.net, org.w3c.dom, javax.naming, javax.management, javax.transaction.xa, javax.xml.transform, org.xml.sax.helpers Manifest-Version: 1.0

Please help

Thanks,

Nitin

[Pawan-Gupta]

Hi,

Can you plz confirm the service that you want to access using API has been deployed as sling service or just a simple java class? because if you have deployed it as a sling service then the correct way to access it is

com.adobe.cq.CustomerService custService= sling.getService(CustomerService.class);

sling is implicit object in scriptlet defined through <cq:defineobjects> and you can find reference in various documentation.

if it doesn't work then plz share you service code.

Thanks,

Pawan

[smacdonald2008 · Accepted solution]

Here is an update to this entire thread -- the original code did have the Class.forName("com.mysql.jdbc.Driver").newInstance(); line of code in the ConnectionHelper class. It looks like there was a copy and paste error in a recent version of the article and a line of code was accidentally deleted. When i tested - i ran into the same issue and CQ did not know what Driver file to use-- Hence the exception. 
Here is proper ConnectionHelper class -- with the Driver file specified. Use this ConnectionHelper class -- with the DriverFile specified and it will work -- just tested and it works like a charm.  

package com.adobe.sql.aem; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectionHelper { private String url; private static ConnectionHelper instance; private ConnectionHelper() { try { Class.forName("com.mysql.jdbc.Driver").newInstance(); url = "jdbc:mysql://localhost:3306/cq"; } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { if (instance == null) { instance = new ConnectionHelper(); } try { return DriverManager.getConnection(instance.url, "root", "root"); } catch (SQLException e) { throw e; } } public static void close(Connection connection) { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } }

I have also created a more in-depth MySQL/AEM article that lets you persist and query data from MySQL. See http://scottsdigitalcommunity.blogspot.ca/2013/08/querying-and-persisting-adobe-aem-data.html.
 

[nitin_eusebius]

Thanks Pawan for your reply. I am using a simple java class which talks to MySQL as mentioned in the example @ http://helpx.adobe.com/adobe-cq/using/persisting-cq-data-relational-database.html

The class is CustomerService.java 

[nitin_eusebius]

Great this work. Thanks Scott for all your time and fixing/updating the article

[smacdonald2008]

Glad its working.

[Pawan-Gupta]

Hi Nitin,

Ok. Are you building and deploying your bundle to Maven some third party build tool or using CQ build using .bnd file? because i saw some issues in import & export package in manifest file.

1. The url which you asked me to refer is referring to simple java database related class file but to work this class ofcourse you require import of different database connection classes and it this case it is MYSQL. but i observed the com.mysql.* (all classes) are part of export not import as you are referring those classes from other bundle.

2. your own custom package com.day.cq also appears in import package list where it should be in export package only.

3. Can you just for testing purpose configure export and import package with * (by default it will export all your custom package and import all the package your custom bundle require) and test it.

4. When you deploy/redeploy the bundle plz also check the deployment status of your bundle in error.log file.

Plz let me know what happen after this.

Thanks,

Pawan

[nitin_eusebius]

Hi Pawan,

No I am just following the link and trying to create a bundle with the instructions in it. I tried the * but it didn't work . Below is the snap shot from console

                                 

Symbolic Name	sqlosgi
Version	1.0.0
Bundle Location	inputstream:sqlosgi_1.0.0.jar
Last Modification	Wed Aug 14 22:50:10 PDT 2013
Start Level	20
Exported Packages	*,version=0.0.0
Imported Packages	ch.randelshofer.io,version=1.0.0 from day-commons-gfx (107)
Manifest Headers	Bundle-ManifestVersion: 2 Bundle-Name: Sqlosgi Bundle-RequiredExecutionEnvironment: JavaSE-1.7 Bundle-SymbolicName: sqlosgi Bundle-Version: 1.0.0 Export-Package: * Import-Package: * Manifest-Version: 1.0

If you have some time, can you try that link and see if things work for you.

Thanks,

Nitin

[Scott_Brodersen]

Here is another source of information that is related to your task. Perhaps you can find some help here:

http://dev.day.com/docs/en/cq/current/developing/jdbc.html

scott

[nitin_eusebius]

Thanks Scott . The reason I started with that original link to see the ability of working on a standalone application like a spring application that does a specific function or say talk to a DB and then use that as an OSGI bundle within CQ to be used. Is that possible. I was seeing the ability of Spring DM but didn't find any concrete examples of how to do that

Also if I create a OSGI within CRXDE and need to use any third party JARs to use their API how to use that. 

[smacdonald2008]

You can also create an OSGi bundle and include the database JAR file and the Java app logic to talk to the database Here is an article that talks about how to create an OSGi bundle that contains Java JDBC logic and the MySQL driver file to persist CQ data into MySQL: http://scottsdigitalcommunity.blogspot.ca/2013/01/persisting-adobe-cq-data-in-relational.html. This article walks you through how to build a CQ app that persists data into a MySQL database.

[nitin_eusebius]

Hey Scott - Thanks. I am trying the links in your blog on which I am facing issues which I posted above. I followed step by step but my persist.json.jsp does not recognize the

com.adobe.cq.CustomerService custService = new com.adobe.cq.CustomerService();

This is where I am stuck :-( I can see my bundle active and also import and export in the manifest file same as in your links. But no luck. 

Thanks for all your help in advance

[smacdonald2008]

This is strange -- this code was tested on CQ 5.5 and 5.6. I am going to look into this today to see what may be causing your issue. I am not able to re-delicate this issue as the code is working fine.  How did you build the OSGi bundle? Did you use the Eclipse IDE as described in this article?

I am going to update the MySQL article so it follows the same procedure as  http://scottsdigitalcommunity.blogspot.ca/2013/01/persisting-cq-data-in-java-content.html. This article uses Maven to build the OSGi and creates an AEM service and uses sling:

com.adobe.cq.CustomerService cs = sling.getService(com.adobe.cq.CustomerService.class);

[nitin_eusebius]

Thanks Scott. Yes I followed as is in the link. I am using

1. mysql-connector-java-5.1.26-bin.jar
2. Eclipse Version: Indigo Service Release 2
3. JDK 7
4. CQ 5.6

Thanks a lot for your help and review.

[smacdonald2008]

What you should do is try and return a hard coded string in your code and comment out the JDBC app logic. This rules out something else causing your errors. Then see if you can call it from simple JSP like this:

<%@include file="/libs/foundation/global.jsp"%>
<%@taglib prefix="cq" uri="http://www.day.com/taglibs/cq/1.0" %>
<%
String first = "TOM";
String last = "Blue";
String phone = "555-5555";
String desc = "Active Customer";

//invoke the CustomerServer object's injestCustData method
com.adobe.cq.CustomerService custService = new com.adobe.cq.CustomerService();
int myPK=-1; 
myPK = custService.injestCustData(first, last, phone, desc) ;
  

%>
<h3>PK IS <%=myPK%></h3>

Then when you get that working -- get the app logic back into the bundle and try calling it again. Pass the values that you see in the above code. Also make sure that you get your driver file into CQ. Here is another link that talks about getting the Database driver file into CQ:

http://cq-ops.tumblr.com/post/21893960212/how-to-turn-a-jdbc-driver-jar-into-an-osgi-bundle-jar

[nitin_eusebius]

Thanks Scott for your pointers. I was able to get to the error. Pasting the custom sysout below

**In the Exception block** -->No suitable driver found for jdbc:mysql://localhost:3306/cq

Now I am wondering that I did included the "mysql-connector-java-5.1.26-bin.jar" while creating the main bundle (as mentioned in the original link), they why its not able to get it?

Also even if I am able to reach inside the bundle via JSP, why does the JSP in CRXDE still says "com.adobe.cq.CustomerService cannot be resolved to a type"

Thanks for your help.

[nitin_eusebius]

Hi Scott,

I tried adding jdbc jar as osgi and re-tested. I am still getting "No suitable driver found for jdbc:mysql://localhost:3306/cq"

Thanks,

Nitin

[smacdonald2008]

I am investigating this issue - there seems to be an issue with CQ finding the MySQL driver file in an OSGi bundle. The code in the articles referenced in this thread has been tested many times. Did you try restarting CQ after you deployed the bundle? 

We are working on this -- we think it may have to do with the version of the MySQL driver file. Sling is not serving up the Database Driver in the OSGi. 

[nitin_eusebius]

Thanks Scott. I tried restarting it and still same issue. Thanks a lot for looking into this issue as I guess someone else can have same issue.