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

Persisting Adobe CQ data in a relational database

  • October 16, 2015
  • 18 replies
  • 4255 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

    Pawan-Gupta
    Pawan-Gupta
    New Participant
    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

    smacdonald2008
    smacdonald2008Accepted solution
    New Participant
    October 16, 2015

    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
      nitin_eusebiusAuthor
      New Participant
      October 16, 2015

      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
      nitin_eusebiusAuthor
      New Participant
      October 16, 2015

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

        smacdonald2008
        smacdonald2008
        New Participant
        October 16, 2015
        Glad its working.
        Pawan-Gupta
        Pawan-Gupta
        New Participant
        October 16, 2015

        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
        nitin_eusebiusAuthor
        New Participant
        October 16, 2015

        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 Namesqlosgi
        Version1.0.0
        Bundle Locationinputstream:sqlosgi_1.0.0.jar
        Last ModificationWed Aug 14 22:50:10 PDT 2013
        Start Level20
        Exported Packages*,version=0.0.0
        Imported Packagesch.randelshofer.io,version=1.0.0 from day-commons-gfx (107)
        Manifest HeadersBundle-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
        Scott_Brodersen
        New Participant
        October 16, 2015

        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
        nitin_eusebiusAuthor
        New Participant
        October 16, 2015

        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
        smacdonald2008
        New Participant
        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.

        Show more replies
        Terms & ConditionsAccessibility statement

        Loading footer...