Persisting Adobe CQ data in a relational database | Community
Skip to main content
nitin_eusebius
nitin_eusebius
Level 2
October 16, 2015
Solved

Persisting Adobe CQ data in a relational database

  • October 16, 2015
  • 18 replies
  • 4257 views

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 Namesqlosgi
Version1.0.0
Bundle Locationinputstream:sqlosgi_1.0.0.jar
Last ModificationWed Aug 14 15:59:09 PDT 2013
Start Level20
Exported Packagescom.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 Packagesjavax.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 HeadersBundle-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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by smacdonald2008

    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.
     

    18 replies

    smacdonald2008
    smacdonald2008
    Level 10
    October 16, 2015

    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
    nitin_eusebiusAuthor
    Level 2
    October 16, 2015

    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. 

    smacdonald2008
    smacdonald2008
    Level 10
    October 16, 2015

    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
    nitin_eusebiusAuthor
    Level 2
    October 16, 2015

    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

    nitin_eusebius
    nitin_eusebiusAuthor
    Level 2
    October 16, 2015

    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.

    smacdonald2008
    smacdonald2008
    Level 10
    October 16, 2015

    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
    nitin_eusebiusAuthor
    Level 2
    October 16, 2015

    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
    smacdonald2008
    Level 10
    October 16, 2015

    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
    nitin_eusebiusAuthor
    Level 2
    October 16, 2015

    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

    Pawan-Gupta
    Pawan-Gupta
    Level 8
    October 16, 2015

    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

    Show more replies
    Terms & ConditionsAccessibility statement

    Loading footer...