Persisting Adobe Experience Manager data in a relational database

Article summary

Summary

Discusses how to create an Adobe Experience Manager application that is able to persist data in a relational database. In this article, the OSGi bundle persists data into MySQL. An Eclipse Plugin is used to create the OSGi bundle in this development article.

A special thank you to community member Ranta Kumar Kotla for testing this Community Article to ensure it works.

Digital Marketing Solution(s)

Adobe Experience Manager (Adobe CQ)

Audience

Developer (intermediate)

Required Skills

Java, JQuery, SQL, CSS

Tested On

Adobe Experience Manager 5.5, 5.6

Introduction

You can create an Adobe Experience Manager application that captures data from users and stores the data in either the Java Content Repository (JCR) or other back ends such as a relational database. For example, if your organization uses a relational database such as MySQL, you can create an Experience Manager application that persists data into MySQL.

User data persisted into a relational database

Assume that you want to create an Experience Manager application that tracks your customers.

An Experience Manager application that captures customer information

When an end user fills in the web form and clicks the Add Customer button, customer information is added to the database. The primary key value is returned and displayed in the Customer Id field.

This article guides you through creating an Experience Manager web application that captures data from an end user and persists the data in the Customer table.

Enter the folder name into the Create Folder dialog box. Enter sqlpersist.

Repeat steps 1-4 for each folder specified in the previous illustration.

Click the Save All button.

Note:

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

Create a template

You can create a template by using CRXDE Lite. An Experience Manager 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 tasks:

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

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

Label: The name of the template to create. Enter templateSQL.

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 sqlpersist/components/page/templateSQL.

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 Experience Manager 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.

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

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.

The following Java code represents the CustomerService class that updates the Customer table.

This class contains a method named injestCustData that calls the ConnectionHelper object’s static method named getConnection to establish a connection to the MySQL database. The four values that are passed to the injestCustData method are persisted into the Customer table using a SQL PreparedStatement instance (to project against SQL injection attacks). The four values are obtained from the client JSP. The primary key is programmatically generated and is used as a return value for the injestCustData method. The return value is displayed in the client JSP.

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.

Create the Java JDBC classes:

Create an Eclipse Java project and name the project CQJDBCProject.

Add the MySQL JAR file to your Eclipse project’s class path.

Add the Java classes specified in this section to your Eclipse project. Add the Java JDBC application logic to the classes.

Compile and build your Java project.

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 Adobe 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, you can invoke the injestCustData method of the CustomerService class. 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. (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.

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.

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 a CSS file and a JQuery framework file 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.

To add CSS files and the JQuery framework to your component, add a cq:ClientLibraryFolder node to your component. After you create the node, set properties that allow the JSP script to find the CSS files and the JQuery library files.

To add the JQuery framework, add a new node named clientlibs to your component (as discussed later). Add these two properties to this node.

Name

Type

Value

dependencies

String[]

cq.jquery

categories

String[]

jquerysamples

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

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

The content of the clientlibs folder

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.

In this code example, notice that a JSONWriter instance is created. This object is used to return data to the main JSP client after the call to the OSGi bundle is made. In this example, the primary key value of the new customer record is send back client JSP.

Modify the templateSQL.jsp

Modify the templateSQL.jsp file to call the persist.json.jsp when the submit button is clicked. In this example, a JQuery Ajax HTTP request is used and the four values are passed to the persist.json.jsp. This code shows the submit method that is called when the user fills in the data and clicks the submit button.

Create an Experience Manager web page

The final task is to create a site that contains a page that is based on the templateSQL (the template created earlier in this development article). When the user enters data and submits it, the data is persisted in the MySQL database. The primary key value of the new customer record is displayed in the Customer ID field.

Go to the welcome page at http://[host name]:[port]; for example, http://localhost:4502.

Select Websites.

From the left hand pane, select Websites.

Select New Page.

Specify the title of the page in the Title field.

Specify the name of the page in the Name field.

Select templateSQL 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.

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.

Twitter™ and Facebook posts are not covered under the terms of Creative Commons.