Article summary

Summary

Discusses how to create and deploy an Experience Manager OSGi bundle that queries data from a relational database. In this example, MySQL is used. In addition, this article demonstrates how to build an OSGi bundle using Maven.

This article is for AEM 5.5. For newer versions, see Querying and Persisting Adobe AEM data into MySQL.

Digital Marketing Solution(s) Adobe Experience Manager (Adobe CQ)
Audience
Developer (intermediate)
Required Skills
Java, SQL, HTML
Tested On Adobe Experience Manager 5.5

Introduction

You can create an Adobe Experience Manager web application that performs database operations on a relational database. For example, an Experience Manager web application can retrieve a result set from MySQL and display the data in a client web page.

MySQL
An Adobe Experience Manager application interacting with a relational database

You can create an OSGi bundle that can be used within the Adobe Marketing Cloud. An OSGi bundle is essentially a Java module that contains application logic that meets your business requirements. An OSGi bundle lets you dynamically load, unload, configure, and control the Java module without restarting the server. The OSGi component that is created in this development article contains Java JDBC application logic that queries data from MySQL. (This OSGi component corresponds to the JDBC Java code shown in the previous illustration.)

Note:

This development environment connects to a 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.

This article guides you through creating an Experience Manager web application that queries data from a MySQL table named Customer. The following describes the Customer table.

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:

Before following along with this development article, make sure that you install AEM and have it running. In addition, setup MySQL and a data schema that contains the Customer table. For information about MySQL, see http://www.mysql.com/.

Note:

Learn how to persist data into MySQL and build a dynamic web client. For information, see Persisting Experience Manager data in a relational database.

Create an Experience Manager application folder structure

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

CQAppSetup

The following list 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 that you can base page components on.
  • src: contains source code that comprises an OSGi component (this development article uses an OSGi component that retrieves data from MySQL). 
  • install: contains a compiled OSGi bundles container.

To create an application folder structure, perform these steps:

  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.
  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 myjdbcapp
  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 that specify the page structure. For more information about templates, see Templates.

To create a template, perform these steps:

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.

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 jdbctemplate.
  • 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 myjdbcapp/components/page/jdbctemplate.
  • 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 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 an AEM developer to create scripts that perform specific functionality. For more information about components, see Components.

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.

3. Right-click /apps/myjdbcapp/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 jdbctemplate.
  • Title: The title that is assigned to the component
  • Description: The description that is assigned to the template

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

6. Select OK on Allowed Children.

7. Open the jdbctemplate.jsp file that is located at: /apps/myjdbcapp /components/page/jdbctemplate/jdbctemplate.jsp.

8. Enter the following JSP code:

 

<html>
<head>
<title>Hello World !!!</title>
</head>
<body>
<h1>Hello JDBC!!!</h1>
<h2>This page will contain data from MySQL</h2>
</body>
</html>

Create Java JDBC application logic

The OSGi bundle that is created in this development article contains the following two Java classes:
ConnectionHelper: A Java class that establishes a connection to MySQL.
DataService: A Java class that queries data from the Customer table.

The following Java code represents the ConnectionHelper class.

package com.adobe.test;

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

Notice that the ConnectionHelper class is located in a Java package named com.adobe.test. This class creates a connection to the database located at jdbc:mysql://localhost:3306/cq.

The following Java code represents the DataService class that queries data from the Customer table that is a MySQL database and the schema is named cq.

package com.adobe.test;
import java.sql.*;

public class DataService {
 
public String getUser(){
    Connection c = null;
    String firstName =""; 
    try {
   
          // Create a Connection object
          c =  ConnectionHelper.getConnection();
    
           Statement s = c.createStatement();
           ResultSet rs = s.executeQuery("Select * from Customer");
           rs.next(); 
           firstName = rs.getString("custFirst");
       
    }
    catch (Exception e) {
      e.printStackTrace();
     }
    finally {
      ConnectionHelper.close(c);
    }
    return firstName;
  }
}

This class contains a method named getUser that calls the ConnectionHelper object’s static method named getConnection to establish a connection to the MySQL database. A SQL string retrieves all records from the Customer table and the first name is the return value for the getUser method.

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.

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.

Create the Java JDBC classes:

  1. Create an Eclipse Java project and name the project CQJDBCProject.
  2. Add the MySQL JAR file 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 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.)

Note:

Instead of developing your own JDBC classes, another option you have is to use the Experience Manager JDBC connector. Experience Manager contains an integration for JDBC whereby you can configure connections and get a connection pool for free as an OSGi service. See http://docs.adobe.com/docs/en/cq/current/developing/jdbc.html. (This article taught you how to develop your own classes for possible customization.)

Create and deploy the OSGi bundle that contains the Java JDBC logic

Create an OSGi bundle that contains the CQJDBC.jar and mysql-connector-java-5.1.6.jar files. After you upload the OSGi bundle to Experience Manager, you can call the DataService object's getUser method. To create the OSGi bundle, you use another Eclipse project that creates an OSGi bundle.

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 CustomerJDBC10.

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 JAR file (for example, mysql-connector-java-5.1.22).

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.

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.

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

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.

Modify the jdbcpage JSP to call the methods specified in the OSGi bundle

After you build and deploy the OSGi bundle (that contains the JDBC application logic that the Database driver JAR file), you can call its methods from a JSP that is part of a page component. In this development article, the jdbcpage JSP calls the getUser method defined in the OSGi bundle’s DataService class. Modify this JSP so that it resembles the following JSP code.

<%@include file="/libs/foundation/global.jsp"%>
<%@taglib prefix="cq" uri="http://www.day.com/taglibs/cq/1.0" %>
<%@ page import="com.adobe.test.DataService"  %>
<h1><%= properties.get("title", currentPage.getTitle()) %></h1>
<%
DataService ds = new DataService();

%>
<h2>About to write the first name</h2>

<h3>The first name in the list is: <%=  ds.getUser()%></h3>

Notice that a page import is specified and it imports the com.adobe.test.DataService data type. A DataService instance is created and its getUser method is called.

Modify the jdbctemplate JSP 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.
  3. Double-click /apps/myjdbcapp/components/page/jdbctemplate/jdbctemplate.jsp.
  4. Replace the JSP code with the new code shown in this section.
  5. Click Save All.

 

Create a page that displays the data retrieved from the OSGI bundle

The final task to see a web page that displays data from MySQL is to create a page that is based on the jdbctemplate template (the template created earlier in this development article).

The following illustration shows the data returned by the OSGi bundle being displayed in the web page. The name Tom Blue was retrieved from the user table.

JSPMySQL

To create a web page that displays data retrieved from an OSGi bundle:

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

2. Select Websites.

3. From the left hand pane, select Websites.

4. Select New, New Page.

5. Specify the title of the page in the Title field.

6. Specify the name of the page in the Name field.

7. Select jdbctemplate 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 when creating the template, the template will not show up in the New Page dialog box.

8. Open the new page that you create by double-clicking it in the right pane. The new page opens in a web browser.

See Also

For a more advanced walkthrough that creates an AEM application that lets you query and persist data to MySQL, see http://helpx.adobe.com/adobe-cq/using/querying-persisting-cq-data-mysql.html.

The above link references you to an article that creates this AEM application.

MainApp


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