Article summary

Summary
Discusses how to create an Adobe Experience Manager application that is able to query data from MySQL as well as persist data back to the database. This article creates an OSGi bundle that uses the JDBC API to query and persist data. In addition, discusses how to invoke an OSGi bundle operation from the client web page.    
Digital Marketing Solution(s) Adobe Experience Manager (Adobe CQ)
Audience
Developer (intermediate)
Required Skills
Java, JQuery, JDBC, SQL, AJAX, CSS, HTML
Tested On Adobe Experience Manager 5.5, 5.6, 6.x

Note:

You can download an AEM package that contains code and the OSGi bundle that are used in this article. Download the package and deploy using package manager. The purpose of this code is to show the community these concepts in action. That is, it's to illustrate how to write an AEM application that uses a custom connection class to persist and query data from MySQL. This community code is for teaching purposes only and not meant to go into production as is.

You can view the application on AEM 6 by using the following URL: http://localhost:4502/editor.html/content/SQL.html (assuming you deploy on author). 

You can view the application on AEM 5.6 by using the following URL: http://localhost:4502/cf#/content/SQL.html (assuming you deploy on author). 

Download

Introduction

You can create an Adobe Experience Manager web application that performs database operations on a relational database. In this development article, the Experience Manager web application queries and updates a MySQL database. That is, a user can create a new customer record that is stored in the Customer table. Likewise, a user can view all Customer records, as shown in the following illustration.

MainApp

The Java JDBC API is used within an OSGi bundle that returns a data result set based on three options:

1. All customers (both active and past customers are displayed as shown in the previous illustration).
2. Active customers (only active customers are displayed).
3. Past customers (only past customers are displayed).

This development article walks you through creating an AEM application that queries data from MySQL and displays the data in client web page. In addition, new customer records can be added.  The following represents the Customer table that belongs to a MySQL database named CQ.

Field Name Field Type Key
custId An integer that specifies the customer identifier value. PK
custFirst A string value that specifies the customer’s first name. N/A
custLast A string value that specifies the customer’s last name. N/A
custDesc A string value that specifies the customer’s description. N/A
custAddress A string value that specifies the customer’s address or phone number. N/A

Note:

This development article connects to a MySQL database by using a custom Java class that uses the JDBC API. You can create an OSGi bundle that is able to connect to a relational database by using a DataSourcePool. In this situation, you do not have to write a custom Java connection class. For information, see  Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.

Note:

Before following along with this development article, setup MySQL and create a database schema named CQ that contains the Customer table. See www.mysql.com. If you install the package shown at the beginning of this article, you can skip the steps. 

Note:

Instread of querying and persisting data from MySQL, you can persist and query data from the AEM JCR. For information, see Querying Adobe Experience Manager Data using the JCR API.

Create an AEM application folder structure 

Create an AEM application folder structure that contains templates, components, and pages by using CRXDE Lite. 

CQAppSetup

 

The following describes each application folder:

  • application name: contains all of the resources that an application uses. The resources can be templates, pages, components, and so on.
  • components: contains components that your application uses. 
  • page: contains page components. A page component is a script such as a JSP file. 
  • global: contains global components that your application uses.
  • template: contains templates on which you base page components. 
  • src: contains source code that comprises an OSGi component (this development article does not create an OSGi bundle using this folder). 
  • install: contains a compiled OSGi bundles container.

To create an AEM application folder structure:

  1. To view the welcome page, enter the URL http://[host name]:[port] into a web browser. For example, http://localhost:4502.
  2. Select CRXDE Lite (if you are using AEM 5.6, click Tools from the left menu). 
  3. Right-click the apps folder (or the parent folder), select Create, Create Folder.
  4. Enter the folder name into the Create Folder dialog box. Enter sqlquery
  5. Repeat steps 1-4 for each folder specified in the previous illustration. 
  6. Click the Save All button.

 

Note:

You have to click the Save All button when working in CRXDE Lite for the changes to be made.

Create a template 

You can create a template by using CRXDE Lite. A template enables you to define a consistent style for the pages in your application. A template comprises of nodes that specify the page structure. For more information about templates, see Templates.

To create a template, perform these tasks:

1. To view the CQ welcome page, enter the URL http://[host name]:[port] into a web browser. For example, http://localhost:4502.

2. Select CRXDE Lite (if you are using AEM 5.6, click Tools from the left menu).

3. Right-click the template folder (within your application), select Create, Create Template.

4. Enter the following information into the Create Template dialog box:

  • Label: The name of the template to create. Enter mysqlTemplate
  • Title: The title that is assigned to the template.
  • Description: The description that is assigned to the template.
  • Resource Type: The component's path that is assigned to the template and copied to implementing pages. Enter sqlquery/components/page/mysqlTemplate.
  • Ranking: The order (ascending) in which this template will appear in relation to other templates. Setting this value to 1 ensures that the template appears first in the list.

5. Add a path to Allowed Paths. Click on the plus sign and enter the following value: /content(/.*)?.

6. Click Next for Allowed Parents.

7. Select OK on Allowed Children.
 

Create a render component that uses the mysqlTemplate template 

Components are re-usable modules that implement specific application logic to render the content of your web site. You can think of a component as a collection of scripts (for example, JSPs, Java servlets, and so on) that completely realize a specific function. In order to realize this functionality, it is your responsibility as a developer to create scripts that perform specific functionality. For more information about components, see Components.

By default, a component has at least one default script, identical to the name of the component. To create a render component, perform these tasks:

1. To view the welcome page, enter the URL http://[host name]:[port] into a web browser. For example, http://localhost:4502.

2. Select CRXDE Lite (if you are using AEM 5.6, click Tools from the left menu).

3. Right-click /apps/jcrquery/components/page, then select Create, Create Component.

4. Enter the following information into the Create Component dialog box:

  • Label: The name of the component to create. Enter mysqlTemplate.
  • Title: The title that is assigned to the component.
  • Description: The description that is assigned to the template.
  • Super Type: foundation/components/page 

5. Select Next for Advanced Component Settings and Allowed Parents.

6. Select OK on Allowed Children.

7. Open the templateQueryjsp located at: /apps/sqlquery/components/page/mysqlTemplate/mysqlTemplate.jsp.

8. Enter the following JSP code.
 

<html>
<head>
<title>Hello World !!!</title>
</head>
<body>
<h1>Hello MySQL!!!</h1>
<h2>This page will query MySQL</h2>
</body>
</html>

Create Java JDBC application logic

The next step is to create a Java Eclipse project that contains a Java package named com.adobe.aem.sql. The Java files that you create in this section use the Java JDBC API and belong to this package. For information about the Java JDBC API, see http://docs.oracle.com/javase/7/docs/technotes/guides/jdbc/.

Add the following Java files to the com.adobe.aem.sql package:

  • A Java class named Customer that stores customer information.
  • A Java class named ConnectionHelper that contains MySQL connection information.
  • A Java interface named CustomerService.
  • A Java class named CustomerServiceImp that implements the CustomerService interface.

Note:

Ensure you add the MySQL JAR file to your Java project's class path. In addition, the MySQL JAR file is also included in the OSGi bundle that is deployed to Experience Manager. The MySQL JAR file that is used in this development article is named mysql-connector-java-5.1.22.  

Customer class 

The following code represents the Customer class that is used to store customer information. This class contains getter and setter methods that modify class members.  

package com.adobe.aem.sql;

//This bean holds customer information
public class Customer {
     
    //Define private class members
    private String custId ;
    private String first;
    private String last;
    private String address;
    private String description;
     
     
    public void setCustId(String id)
    {
        this.custId = id;
    }
     
    public String getCustId()
    {
        return this.custId;
    }
     
    public void setCustFirst(String first)
    {
        this.first = first;
    }
     
    public String getCustFirst()
    {
        return this.first;
    }
     
    public void setCustLast(String last)
    {
        this.last = last;
    }
     
    public String getCustLast()
    {
        return this.last;
    }
     
    public void setCustAddress(String address)
    {
        this.address = address;
    }
     
    public String getCustAddress()
    {
        return this.address;
    }
     
    public void setCustDescription(String description)
    {
        this.description = description;
    }
     
    public String getCustDescription()
    {
        return this.description;
    }
 
}

ConnectionHelper class 

The following Java code represents the ConnectionHelper class.  Notice that this class contains this line of code:

Class.forName("com.mysql.jdbc.Driver").newInstance();

This line of code informs CQ what database driver to use to connect to MySQL. The database driver is included in the OSGi bundle that is deployed to Experience Manager. (This is shown later in this development article.)

package com.adobe.aem.sql;

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

Note:

Hard-coded values such as the value to the MySQL database is not good practice to use within a production environment. For example, you will have to change the code when the host name of the database server changes. It's better practice to make it configurable. However, to keep this development article simple, the URL to the database server is hard-coded in the Java logic.

CustomerService interface 

The following code represents the CustomerService interface. This interface contains two method signatures named injestCustData and getCustomerData. The implementation logic for these methods is located in the CustomerServiceImp class. The getCustomerData method uses the Java JDBC API to query customer data from the Customer table in MySQL. Likewise, the injectCustData uses the Java JDBC API to update the Customer table with a new customer record.  

package com.adobe.aem.sql;
 
public interface CustomerService {
     
    //Adds a new customer record in the Customer table
    public int injestCustData(String firstName, String lastName, String address, String desc);
      
    /*
     * Retrieves customer data from the Customer table and returns all customer
     *The filter argument specifies one of the following values:
     *    
     *Customer - retrieves all customer data
     *Active Customer- retrieves current customers
     *Past Customer - retrieves old customers no longer current customers
     */
    public String getCustomerData(String filter); 
 
}

CustomerServiceImp class

The getCustomerData method returns an XML schema that contains customer data within a string. The Java JDBC API is used to query the Customer table based on user input. In this example, the custDesc field of each Customer record stores one of these values:

  • Active Customer
  • Past Customer

Therefore the MySQL query is setup to query all customer records, only active customers, or only past customers. This is achieved by querying the custDesc field.  

//Set the query and use a preparedStatement
if (filter.equals("All Customers"))
   query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer";
else if(filter.equals("Past Customer"))
  query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer where custDesc = 'Past Customer'; ";
else if(filter.equals("Active Customer"))
   query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer where custDesc = 'Active Customer'; ";
               
 pstmt = c.prepareStatement(query); 
 rs = pstmt.executeQuery();

The getCustomerData method returns XML that contains customer data that is queried from the Customer table. This XML is parsed on the client web page and displayed within a grid control (this is shown later in this development article).

<?xml version="1.0" encoding="UTF-8"?>
<Customers>
    <Customer>
        <First>Tony</First>
        <Last>Blue</Last>
        <Description>Past Customer</Description>
        <Address>Active</Address>
    </Customer>
    <Customer>
        <First>Sam</First>
        <Last>White</Last>
        <Description>Past Customer</Description>
        <Address>Active</Address>
    </Customer>
    <Customer>
        <First>Kim</First>
        <Last>Red</Last>
        <Description>Past Customer</Description>
        <Address>Active</Address>
    </Customer>
</Customers>

The following Java code represents the CustomerServiceImp class. For each customer record that is queried from the Customer table, a new Customer object is created and stored in an ArrayList. Data from each Customer object is retrieved from the ArrayList and stored in an XML schema by calling the toXML method. 

package com.adobe.aem.sql;
 
 
import com.adobe.aem.sql.ConnectionHelper;
import com.adobe.aem.sql.Customer ; 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
 
import java.util.List;
import java.util.ArrayList;
 
import org.w3c.dom.Document;
import org.w3c.dom.Element;
  
import java.io.StringWriter;
import java.util.Iterator;
 
 
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
 
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
  

public class CustomerServiceImp implements CustomerService {
 
    //Adds a new customer record in the Customer table
    @Override
    public int injestCustData(String firstName, String lastName, String address, String desc) {
        Connection c = null;
         
        int rowCount= 0; 
        try {
                                    
              // Create a Connection object
              c =  ConnectionHelper.getConnection();
           
               ResultSet rs = null;
               Statement s = c.createStatement();
               Statement scount = c.createStatement();
                 
               //Use prepared statements to protected against SQL injection attacks
               PreparedStatement pstmt = null;
               PreparedStatement ps = null; 
                           
               //Set the query and use a preparedStatement
               String query = "Select * FROM Customer";
               pstmt = c.prepareStatement(query); 
               rs = pstmt.executeQuery();
                 
               while (rs.next()) 
                       rowCount++;
                              
               //Set the PK value
               int pkVal = rowCount + 2; 
                 
               String insert = "INSERT INTO Customer(custId,custFirst,custLast,custAddress,custDesc) VALUES(?, ?, ?, ?, ?);";
               ps = c.prepareStatement(insert);
               ps.setInt(1, pkVal);
               ps.setString(2, firstName);
               ps.setString(3, lastName);
               ps.setString(4, address);
               ps.setString(5, desc);
               ps.execute();
               return pkVal;
        }
        catch (Exception e) {
          e.printStackTrace();
         }
        finally {
          ConnectionHelper.close(c);
    }
        return 0; 
 }
 
    /*
     * Retrieves customer data from the Customer table and returns all customer
     *The filter argument specifies one of the following values:
     *    
     *Customer - retrieves all customer data
     *Active Customer- retrieves current customers
     *Past Customer - retrieves old customers no longer current customers
     */
    @Override
    public String getCustomerData(String filter) {
        Connection c = null;
        int rowCount= 0; 
        Customer cust = null; 
        String query = "";
        List<Customer> custList = new ArrayList<Customer>();
        try {
                                    
              // Create a Connection object
              c =  ConnectionHelper.getConnection();
           
               ResultSet rs = null;
               Statement s = c.createStatement();
               Statement scount = c.createStatement();
                 
               //Use prepared statements to protected against SQL injection attacks
               PreparedStatement pstmt = null;
               PreparedStatement ps = null; 
                           
               //Set the query and use a preparedStatement
               if (filter.equals("All Customers"))
                   query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer";
               else if(filter.equals("Past Customer"))
                   query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer where custDesc = 'Past Customer'; ";
               else if(filter.equals("Active Customer"))
                   query = "Select custId,custFirst,custLast,custAddress,custDesc FROM Customer where custDesc = 'Active Customer'; ";
                
               pstmt = c.prepareStatement(query); 
               rs = pstmt.executeQuery();
                 
               while (rs.next()) 
               {
                   //for each pass - create a Customer object
                   cust = new Customer(); 
                    
                   //Populate customer members with data from MySQL
                   int custId = rs.getInt(1);
                   String id = Integer.toString(custId);
                   cust.setCustId(id); 
                    
                   cust.setCustFirst(rs.getString(2));
                   cust.setCustLast(rs.getString(3));
                   cust.setCustAddress(rs.getString(4));
                   cust.setCustDescription(rs.getString(5));
                    
                 //Push Customer to the list
                 custList.add(cust);
                     
               }             
               //return xml that contains all customer taken from MySQL
               return convertToString(toXml(custList));               
                
        }
    catch(Exception e)
        {
         e.printStackTrace();
        }
        return null;
    }
          
          
    //Convert Customer data retrieved from the AEM JCR
    //into an XML schema to pass back to client
    private Document toXml(List<Customer> custList) {
    try
    {
        DocumentBuilderFactory factory =     DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document doc = builder.newDocument();
                      
        //Start building the XML to pass back to the AEM client
        Element root = doc.createElement( "Customers" );
        doc.appendChild( root );
                   
        //Get the elements from the collection
        int custCount = custList.size();
         
        //Iterate through the collection to build up the DOM           
         for ( int index=0; index < custCount; index++) {
      
             //Get the Customer object from the collection
             Customer myCust = (Customer)custList.get(index);
                           
             Element Customer = doc.createElement( "Customer" );
             root.appendChild( Customer );
                            
             //Add rest of data as child elements to customer
             //Set First Name
             Element first = doc.createElement( "First" );
             first.appendChild( doc.createTextNode(myCust.getCustFirst() ) );
             Customer.appendChild( first );
                                                               
             //Set Last Name
             Element last = doc.createElement( "Last" );
             last.appendChild( doc.createTextNode(myCust.getCustLast() ) );
             Customer.appendChild( last );
                         
             //Set Description
             Element desc = doc.createElement( "Description" );
             desc.appendChild( doc.createTextNode(myCust.getCustDescription() ) );
             Customer.appendChild( desc );
                        
             //Set Address
             Element address = doc.createElement( "Address" );
             address.appendChild( doc.createTextNode(myCust.getCustAddress()) );
             Customer.appendChild( address );
          }
                 
    return doc;
    }
    catch(Exception e)
    {
        e.printStackTrace();
        }
    return null;
    }
      
      
    private String convertToString(Document xml)
    {
    try {
       Transformer transformer = TransformerFactory.newInstance().newTransformer();
      StreamResult result = new StreamResult(new StringWriter());
      DOMSource source = new DOMSource(xml);
      transformer.transform(source, result);
      return result.getWriter().toString();
    } catch(Exception ex) {
          ex.printStackTrace();
    }
      return null;
     }
     }

Exporting the Java project as a JAR file

  1. Create an Eclipse Java project and name the project CQJDBCProject.
  2. Add the MySQL JAR file (mysql-connector-java-5.1.22-bin) to your Eclipse project’s class path.
  3. Add the Java classes specified in this section to your Eclipse project. Add the Java JDBC application logic shown in this section to the classes.
  4. Compile and build your Java project.
  5. Export the project to a JAR file named CQJDBC.jar. (This JAR file is bundled into an OSGi bundle in the next step.)

 

Deploy the OSGi bundle that contains JBDC application logic

Once you deploy the OSGi bundle, you are able to invoke the getCustomerData and injestCustData methods defined in the CustomerServiceImpl class (this is shown later in this development article). After you deploy the OSGi bundle, you will be able to see it in the Apache Felix Web Console.

OSGi

To create the OSGi bundle, you use another Eclipse project that creates an OSGi bundle. An OSGi bundle is essentially a collection of Java files and a MANIFEST.MF file.

Create an OSGi bundle that contains the JDBC application logic:

1. Start Eclipse (Indigo). The steps below have been tested on Eclipse Java EE IDE for Web Developers version Indigo Service Release 1.

2. Select File, New, Other.

3. Under the Plug-in Development folder, choose Plug-in from Existing JAR Archives. Name your project CQSQLBundle.

4. In the JAR selection dialog, click the Add external button, and browse to the CQJDBC.jar file that you created in the previous step. Also include the MySQL database driver file (mysql-connector-java-5.1.22-bin). (If you are using another database, you must include your database driver file into the OSGi bundle. Otherwise, the OSGi bundle will not be able to store or retrieve data from the database.)

5. Click Next.

6. In the Plug-in Project properties dialog, ensure that you check the checkbox for Analyze library contents and add dependencies.

7. Make sure that the Target Platform is the standard OSGi framework.

8. Ensure the checkboxes for Unzip the JAR archives into the project and Update references to the JAR files are both checked.

9. Click Next, and then Finish.

10. Click the Runtime tab.

11. Make sure that the Exported Packages list is populated.

12. Make sure these packages have been added under the Export-Package header in MANIFEST.MF. Remove the version information in the MANIFEST.MF file. Version numbers can cause conflicts when you upload the OSGi bundle.

13. Also make sure that the Import-Package header in MANIFEST.MF is also populated, as shown here.

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

14. Save the project.

15. Build the OSGi bundle by right-clicking the project in the left pane, choose Export, Plug-in Development, Deployable plug-ins and fragments, and click Next.

16. Select a location for the export (C:\TEMP) and click Finish. (Ignore any error messages).

17. In C:\TEMP\plugins, you should now find the OSGi bundle.

18. Login to the Apache Felix Web Console at http://server:port/system/console/bundles (default admin user = admin with password= admin).

19. Sort the bundle list by Id and note the Id of the last bundle.

20. Click the Install/Update button.

21. Check the Start Bundle checkbox.

22. Browse to the bundle JAR file you just built. (C:\TEMP\plugins).

23. Click Install.

24. Click the Refresh Packages button.

25. Check the bundle with the highest Id.

26. Your new bundle should now be listed with the status Active.

27. If the status is not Active, check the error.log for exceptions. If you get “org.osgi.framework.BundleException: Unresolved constraint” errors, check the MANIFEST.MF for strict version requirements which might follow: javax.xml.namespace; version=”3.1.0”

28. If the version requirement causes problems, remove it so that the entry looks like this: javax.xml.namespace.

29. If the entry is not required, remove it entirely.

30. Rebuild the bundle.

31. Delete the previous bundle and deploy the new one.
 

Add CSS and JQuery files to a CQ:ClientLibraryFolder node  

You add CSS files and JQuery framework files to a cq:ClientLibraryFolder node to define the style of the client JSP. The JQuery framework file that is added is named jquery-1.6.3.min.js.

In addition to the JQuery framework file, a data grid plugin is used in this sample AEM application named DataTables. This plugin is used to display the customer data that is returned by the Customer service in a tabular format.

Download the DataTables plugin from the following URL:

http://www.datatables.net/

Download and extract the DataTables archive file. The client AEM application uses these files from the DataTables archive file:

  • demo_table.css
  • jquery.dataTables.js
  • jquery.dataTables.min.js

The following illustration shows the clientlibs folder with all the files required for this AEM application.

clientlibs

Set the following properties to the cq:ClientLibraryFolder node. 

Name Type Value
dependencies  String[] cq.jquery
categories String[]  jquerysamples

The dependencies property informs Experience Manager to include the CSS files and JQuery libraries in the page. The categories property informs Experience Manager which clientlibs must be included.

After you create the Clientlibs folder, add two CSS files, and the JQuery library files, and two map text files.
 

Site css file 

The site.css file defines the display style for the client JSP file that lets the user enter and submit data. The following code represents the site.css file. 

/* reset */
html, body, div, span, iframe,
h1, h2, h3, h4, h5, h6, p, blockquote, pre,
a, abbr, acronym, address, big, cite, code,
del, dfn, em, img, ins, kbd, q, s, samp,
small, strike, strong, sub, sup, tt, var,
b, u, i, center,
dl, dt, dd, ol, ul, li,
fieldset, form, label, legend,
table, caption, tbody, tfoot, thead, tr, th, td {
 margin: 0;
 padding: 0;
 border: 0;
 font-size: 100%;
 font: inherit;
 vertical-align: baseline;
}
html , body{
 line-height: 1;
 background-color: #334873;
 background-image: url(../_images/bg-page2.png);
}
    
ol, ul {
 list-style: none;
}
    
    
table {
 border-collapse: collapse;
 border-spacing: 0;
}
/* end reset*/
    
    
    
h1, h2, h3 {
 font-family: 'ColaborateRegular', Arial, sans-serif;
}
    
    
strong {
 font-family: 'ColaborateMediumRegular', Arial, sans-serif;
}
    
em {
 font-family: 'ColaborateThinRegular', Arial, sans-serif;
}
    
.content {
 max-width: 760px;
 margin: 20px 0 0 100px;
}
    
.clear:after {
content: "."; display: block; height: 0; clear: both; visibility: hidden;
}
    
.clear {
 min-height: 1px;
}
    
* html .clear {
 height: 1px;
}
    
.header {
 position: relative;
 border-top: solid 6px white;
 padding: 10px 0 10px 0;
 margin-bottom: 20px;
}
    
    
.main {
 xxposition: relative;
 padding-bottom: 1em;
 border-bottom: solid 1px rgba(255,255,255,.5);
 xxoverflow:hidden;
 xxmin-height: 300px;
}
    
.main h1 {
 font-size: 32px;
 color: white;
 text-shadow: 1px 1px 1px rgba(0,0,0,.75);
 border-bottom: solid 1px rgba(255,255,255,.5);
 margin-bottom: 0.75em;
}
    
    
p , li, legend , form{
 font-size: 18px;
 color: white;
 font-family: 'ColaborateLightRegular', Arial, sans-serif;
 line-height: 125%;
 margin-bottom: 10px;
}
    
fieldset {
 padding: 10px;
 border: 1px solid white;
 margin: 25px 0;
}
    
.nav {
 margin: 10px 0 0 100px;
}
    
.nav li {
 display: inline-block;
}
    
.nav a:hover, .example:hover{
 background-color: rgba(255,255,255,.85);
 color: rgb(0,0,0);
}
    
h3 {
 font-size: 18px;
 color: rgb(227,198,133);;
}
    
.results h2 {
 color: rgba(255,255,255,1);
}
.results div {
 padding-bottom: 10px;
}
.results div code {
 float: right;
 width: 60%;
}
    
input {
 font-size: 20px;
}
.form .wide {
 font-size: 18px;
 width: 100%;
}
.resultSection {
 float: right;
 width: 45%;
 margin-left: 20px;
}
#regexTester {
 margin-right: 55%;
}
.sideBySide li {
 float: left;
 overflow: hidden;
 width: 220px;
}
.clickable {
 cursor:pointer;
 margin-bottom: 5px;
}
    
.clickable:hover {
background-color:#FFC;
}
    
    
.col1 {
 float: left;
 width: 75%;
}
.col2 {
 float: right;
 width: 20%;
}
    
.col2 ul {
 margin-left: 20px;
 list-style: square;
}
.col2 li {
 font-size: 90%;
}
    
    
#selectorList {
 overflow: hidden;
}
#selector {
 width: 275px;
}
    
    
form#signup .label {
 width: 200px;
}

Text files

You have to add two text files to the clientlibs folder. These text files map to the JS file and the CSS file. The names of the text files are: css.txt and js.txt. The css.txt file contains the CSS file names: site.css and demo_table.css. Likewise, the js.txt file contains the JS file names: jquery-1.6.3.min.js, jquery.dataTables.js,and jquery.dataTables.min.js.

Add the files to the ClientLibs folder

  1. Right-click sqlquery/components/page then select New, Node.
  2. Make sure that the node type is cq:ClientLibraryFolder and name the node tree.
  3. Right click on clientlibs and select Properties. Add a categories property of type String[]. Assign the value jquerysamples to this property.
  4. On your file system, navigate to the folder where the JQuery JS files are located. Upload these files to AEM.
  5. On your file system, navigate where you placed the CSS file. Upload this file to AEM.
  6. Add a TXT file to the clientlibs folder named js.txt. Add the content specified in this section.
  7. Add a TXT file to the clientlibs node named css.txt. Add the content specified in this section.

 

Note:

You can write a Java client application to upload files to AEM. Once developed, this provides an easy and fast way to upload files. See the following article for more information: Creating Java Swing applications that posts files to AEM ClientLibs folders.

Modify the Client JSP to invoke the getCustomerData and injestCustData methods

To create the AEM application that queries and persists data from MySQL, create these files:

  • query.json.jsp: contains application logic that calls the OSGi bundle's getCustomerData method.
  • persist.json.jsp: contains application logic that calls the OSGi bundle’s injestCustData method. 
  • mysqlTemplateJCR.jsp: contains application logic that defines the JSP that lets a user enter customer data and query existing data.

 

Create the query.json.jsp

Add a new JSP file named query.json.jsp to the following CQ path:

/apps/sqlquery/components/page/mysqlTemplate

In query.json.jsp, you create a com.adobe.aem.sql.CustomerServiceImpl instance by using the new operator, as shown in the following example:

com.adobe.aem.sql.CustomerServiceImpl cs = new com.adobe.aem.sql.CustomerServiceImpl();

After you create a CustomerServiceImpl object, you can invoke the getCustomerData method exposed by the OSGi bundle. You pass a string value to this method that specifies whether to query all customers, past customers, or active customers.

The following code represents the query.json.jsp file. In this code example, notice that a JSONWriter instance is created. This object returns XML that contains customer information to the main JSP client after the call to the OSGi bundle is made. The client JSP parses the XML and displays the customer data in a grid control.
 

<%@include file="/libs/foundation/global.jsp"%>
<%@ page import="org.apache.sling.commons.json.io.*,org.w3c.dom.*" %><%
String filter = request.getParameter("filter");
   
com.adobe.aem.sql.CustomerServiceImp cs = new com.adobe.aem.sql.CustomerServiceImp();

String XML = cs.getCustomerData(filter); 
   
//Send the data back to the client
JSONWriter writer = new JSONWriter(response.getWriter());
writer.object();
writer.key("xml");
writer.value(XML);
   
writer.endObject();
%>

Create the persist.json.jsp

Add a new JSP file named persist.json.jsp to the following CQ path:

/apps/sqlquery/components/page/mysqlTemplate

The following code represents the persist.json.jsp file.
 

<%@include file="/libs/foundation/global.jsp"%>
<%@ page import="org.apache.sling.commons.json.io.*,com.adobe.cq.*" %><%
String first = request.getParameter("first");
String last = request.getParameter("last");
String address = request.getParameter("address");
String desc = request.getParameter("desc");
   
com.adobe.aem.sql.CustomerServiceImp cs = new com.adobe.aem.sql.CustomerServiceImp();
 
int myPK = cs.injestCustData(first, last, address, desc) ; 
    
//Send the data back to the client
JSONWriter writer = new JSONWriter(response.getWriter());
writer.object();
writer.key("pk");
writer.value(myPK);
   
writer.endObject();
%>

Modify the mysqlTemplate

Modify the mysqlTemplate.jsp file to call both the query.json.jsp and the persist.json.jsp. In this example, a JQuery Ajax HTTP request is used and the corresponding values are passed. This code shows the modified mysqlTemplate.jsp file.  

<%@include file="/libs/foundation/global.jsp"%>
<cq:includeClientLib categories="jquerysamples" />
<html>
<head>
<meta charset="UTF-8">
<title>Adobe CQ Persist Page</title>
<style>
#signup .indent label.error {
  margin-left: 0;
}
#signup label.error {
  font-size: 0.8em;
  color: #F00;
  font-weight: bold;
  display: block;
  margin-left: 215px;
}
#signup  input.error, #signup select.error  {
  background: #FFA9B8;
  border: 1px solid red;
}
</style>
<script>
$(document).ready(function() {
       
       var aDataSet = [
                       ['','','',''],
                       ['','','','']
                   ];
                 
  
  
  
      $('#dynamic').html( '<table cellpadding="0" cellspacing="0" border="0" class="display" id="example"></table>' );
      $('#example').dataTable( {
          "aaData": aDataSet,
           "bDestroy": true,
          "aoColumns": [
              { "sTitle": "First Name" },
              { "sTitle": "Last Name" },
              { "sTitle": "Address", "sClass": "center" },
              { "sTitle": "Description", "sClass": "center" }
          ]
      } );
  
  
    $('body').hide().fadeIn(5000);
       
$('#submit').click(function() {
    var failure = function(err) {
      //  $(".main").unmask();
        alert("Unable to retrive data "+err);
           
    };
       
       
    //Get the user-defined values to persist in the database
    var myFirst= $('#first').val() ;
    var myLast= $('#last').val() ;
    var myDescription= $('#description').val() ;
    var myAddress= $('#address').val() ;
       
    var url = location.pathname.replace(".html", "/_jcr_content.persist.json") + "?first="+ myFirst +"&last="+myLast +"&desc="+myDescription +"&address="+myAddress;
       
    //$(".main").mask("Saving Data...");
   
    $.ajax(url, {
        dataType: "text",
        success: function(rawData, status, xhr) {
            var data;
            try {
                data = $.parseJSON(rawData);
                   
                           
                //Set the fields in the forum
                $('#custId').val(data.pk);
              
            } catch(err) {
                failure(err);
            }
        },
        error: function(xhr, status, err) {
            failure(err);
        }
    });
  });
  
  
//Get customer data -- called when the submitget button is clicked
//this method populates the data grid with data retrieved from the //Adobe CQ JCR
$('#submitget').click(function() {
    var failure = function(err) {
          alert("Unable to retrive data "+err);
      };
        
    //Get the query filter value from drop down control
    var filter=   $('#custQuery').val() ;
         
    var url = location.pathname.replace(".html", "/_jcr_content.query.json") + "?filter="+ filter;
         
    $.ajax(url, {
        dataType: "text",
        success: function(rawData, status, xhr) {
            var data;
            try {
                data = $.parseJSON(rawData);
                   
                              
                //Set the fields in the forum
                var myXML = data.xml;
  
                var loopIndex = 0;
  
                //Reference the data grid, clear it, and add new records
                //queried from the Adobe CQ JCR
                
                
                  var oTable = $('#example').dataTable();
                 oTable.fnClearTable(true);
  
  
                 //Loop through this function for each Customer element
                 //in the returned XML
                 $(myXML).find('Customer').each(function(){
                          
                    var $field = $(this);
                    var firstName = $field.find('First').text();
                      
                    var lastName = $field.find('Last').text();
                    var Description = $field.find('Description').text();
                    var Address = $field.find('Address').text();    
  
                    //Set the new data
                    oTable.fnAddData( [
                        firstName,
                        lastName,
                        Address,
                        Description,]
                    );
             
                    });
             
            } catch(err) {
                failure(err);
            }
        },
        error: function(xhr, status, err) {
            failure(err);
        }
    });
  });
   
}); // end ready
</script>
  
</head>
<body>
<div class="wrapper">
    <div class="header">
        <p class="logo">Adobe CQ MySQL Customer Persist/Query Application</p>
    </div>
    <div class="content">
    <div class="main">
    <h1>CQ MySQL Persist Example</h1>
       
    <form name="signup" id="signup">
     <table>
    <tr>
    <td>
    <label for="first">First Name:</label>
    </td>
     <td>
    <input type="first" id="first" name="first" value="" />
    </td>
    </tr>
    <tr>
    <td>
    <label for="last">Last Name:</label>
    </td>
     <td>
    <input type="last" id="last" name="last" value="" />
    </td>
    </tr>
     <tr>
    <td>
    <label for="address">Address:</label>
    </td>
     <td>
    <input type="address" id="address" name="address" value="" />
    </td>
    </tr>
     <tr>
    <td>
   <label for="description">Description:</label>
    </td>
    <td>
    <select id="description"  >
            <option>Active Customer</option>
            <option>Past Customer</option> 
        </select>
    </td>
    </tr>
     <tr>
    <td>
    <label for="custId">Customer Id:</label>
    </td>
     <td>
    <input type="custId" id="custId" name="custId" value="" readonly="readonly"/>
    </td>
    </tr>
      
</table>
            <div>
                <input type="button" value="Add Customer!"  name="submit" id="submit" value="Submit">
            </div>
        </form>
        </div>
    </div>
      
    <div id="container">
     <form name="custdata" id="custdata">
     
    <h1>Query Customer Data from MySQL</h1>
   <div>
     <select id="custQuery"  >
            <option>All Customers</option>
            <option>Active Customer</option>
            <option>Past Customer</option>   
        </select>
    </div>
    <div id="dynamic"></div>
    <div class="spacer"></div>
  
   <div>
      <input type="button" value="Get Customers!"  name="submitget" id="submitget" value="Submit">
    </div>
   </form>
       
</div>
</body>
</html>

Modify the mysqlTemplateQuery file 

  1. To view the welcome page, enter the URL: http://[host name]:[port] into a web browser. For example, http://localhost:4502.
  2. Select CRXDE Lite. (If you are using AEM 5.6, click Tool in the left menu)
  3. Double-click apps/apps/sqlquery/components/page/mysqlTemplate/templateQuery.jsp.
  4. Replace the JSP code with the new code shown in this section.
  5. Click Save All.
     

Create an AEM web page that queries and persists data 

The final task is to create a site that contains a page that is based on the mysqlTemplateQuery (the template created earlier in this development article). When the user selects the customer data to retrieve from the drop down and clicks the Get Customer button, customer data is displayed in the data grid control.

In addition, this client application lets users add new customer data to MySQL.
 

MainApp

 

Create a AEM web page that queries and persists data from the AEM JCR:

  1. Go to the welcome page at http://localhost:4502/siteadmin#/content.
  2. Select New Page.
  3. Specify the title of the page in the Title field.
  4. Specify the name of the page in the Name field.
  5. Select mysqlTemplate from the template list that appears. This value represents the template that is created in this development article. If you do not see it, then repeat the steps in this development article. For example, if you made a typing mistake when entering in path information, the template will not show up in the New Page dialog box.
  6. Open the new page that you created by double-clicking it in the right pane. The new page opens in a web browser. You should see a page similar to the previous illustration.

 

See also

Congratulations, you have just created an AEM application that interacts with MySQL. Please refer to the AEM community page for other articles that discuss how to build other AEM services/applications.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy