Article summary

Summary

Discusses how to create an AEM OSGi bundle that generates Excel reports and persists them into the AEM JCR. The Java Excel API and the JCR API libraries are used in this development article.  

This article creates a service that writes data to an Excel document. Likewise, you can create an AEM service that reads data from an Excel document. For information, see Creating a custom Excel Service for Adobe Experience Manager.

Digital Marketing Solution(s) Adobe Experience Manager (Adobe CQ)
Audience
Developer (Intermediate)
Required Skills
JavaScript, Java. OSGi, XML, HTML
Tested On Adobe Experience Manager 5.5, 5.6

Introduction

You can create a custom reporting service for Adobe Experience Manager (AEM) that stores AEM data. A benefit of creating a custom service is you can develop it to meet your business requirements and integrate the reporting service to use data queried from an AEM service. For example, you can create an AEM solution that queries enterprise data that a digital marketer is interested in viewing. Then you can write out the data to a report and store the report in the AEM Java Content Repository (JCR).

This development article extends an AEM application that is built by following another AEM development article that queries data from MySQL using a DataSourcePool. Before following this article, create the DataSourcePool application by following this article: Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.

In this article, an option is added to the web application that lets a user specify if the queried data is written to a custom report. Consider the following AEM web application.

AEMApp


Note:

For information about setting up the MySQL database and configuring the DataSourcePool, see the article referenced by the previous link.

When an end user clicks the Generate Report checkbox, the custom AEM service generates the report using the result set that is queried from MySQL. In this example, the report is generated as an Excel spreadsheet and stored in the AEM JCR, as shown in the following illustration.

ReportJCR

Notice that the report named AEMReports.xls is stored in /content/reports in the AEM JCR. You specify the name of the report in the web application by specifying a value in the Report Name field. The data that is displayed in the web application is also written to the Excel report, as shown in this illustration.

 

sheet


The application logic required to create an custom Excel repot using the AEM data is developed by using the JExcel API. For information, see:

http://jexcelapi.sourceforge.net/

Also, the JCR API is used to persist the report to the /content/reports JCR location. The JCR API is used within an OSGi bundle that is built using Declarative Services (DS) and Maven. DS is used to inject a ResourceResolverFactory instance into the service. The OSGi bundle is a managed component, which means that the OSGi service container creates the ResourceResolverFactory instance.

Because this article extends the DataSourcePool application to include report generation functionality, the required steps to build the DataSourcePool AEM application are not repeated. Only those steps that relate to extending the application's functionality are discussed. Before following along with this article, create the DataSourcePool application by following the steps located in that article. (See Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.)

Add the JExcel JAR file to Experience Manager as an OSGi bundle

Add the jxl.* classes to Experience Manager. To add the jxl.* classes to Experience Manager, add it to an OSGi bundle and then deploy the bundle to Experience Manager, as discussed in this section. First, download the jxl JAR from the following URL:

http://jexcelapi.sourceforge.net/

To create an OSGi bundle fragment that contains the jxl.* classes, perform these tasks:

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

4. In the JAR selection dialog, click the Add external button, and browse to the jxl JAR file that you downloaded.

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 (notice that Export-Package is jxl).

Manifest-Version: 1.0
Bundle-ManifestVersion: 2
Bundle-Name: ExcelOSGi
Bundle-SymbolicName: ExcelOSGi
Bundle-Version: 1.0.0
Bundle-ClassPath: .
Export-Package: jxl,
jxl.biff,
jxl.biff.drawing,
jxl.biff.formula,
jxl.common,
jxl.common.log,
jxl.demo,
jxl.format,
jxl.read.biff,
jxl.write,
jxl.write.biff
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 Java sources files that are located in the com.adobe.cq.sql package. 

Modify the Java source files that are located in the com.adobe.cq.sql package. You have the following Java source files already part of this package:

  • A Java class named Customer that stores customer information.
  • A Java interface named CustomerService.
  • A Java class named CustomerServiceImp that implements the CustomerServiceinterface.

WriteExcel class

The first step is to add a new Java class named WriteExcel to the com.adobe.cq.sql package. This class contains Java application logic that uses the JExcel API to generate the custom AEM report. This class uses a List instance where each element is a Customer object to populate the report.

The followiing Java code represents this class. 

package com.adobe.cq.sql;

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;
import java.io.*;
 
import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


//Generates an Excel Report by reading the List that contains data
//retrieved by the AEM Service that uses the DataSourcePool.  
public class WriteExcel {
 
    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
     
        
    //Returns an InputStream that represents the Excel Report
    public java.io.InputStream exportExcel( List<Customer> custList)
    {
        try
        {
            java.io.InputStream is =  write( custList);
            return is ;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return null;
    }
    
    //Generates the report and returns an an inputstream
    public java.io.InputStream write( List<Customer> list) throws IOException, WriteException {
    	java.io.OutputStream os = new java.io.ByteArrayOutputStream() ;
        WorkbookSettings wbSettings = new WorkbookSettings();
 
        wbSettings.setLocale(new Locale("en", "EN"));
 
        //Create a Workbook - pass the OutputStream
        WritableWorkbook workbook = Workbook.createWorkbook(os, wbSettings);
        workbook.createSheet("Comumunity Report", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        createLabel(excelSheet)   ;
        int size =  createContent(excelSheet, list);
 
        //Close the workbook               
        workbook.write();
        workbook.close();
 
        //Get an inputStram that represents the Report
        java.io.ByteArrayOutputStream stream = new java.io.ByteArrayOutputStream();
        stream = (java.io.ByteArrayOutputStream)os;
        byte[] myBytes = stream.toByteArray();
        java.io.InputStream is = new java.io.ByteArrayInputStream(myBytes) ;
               
        return is ;
    }
 
    //Create Headings in the Excel spreadsheet
    private void createLabel(WritableSheet sheet)
            throws WriteException {
        // Create a times font 
        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
        // Define the cell format
        times = new WritableCellFormat(times10pt);
        // Lets automatically wrap the cells
        times.setWrap(true);
 
        // create create a bold font with unterlines
        WritableFont times10ptBoldUnderline = new WritableFont(WritableFont.TIMES, 10, WritableFont.BOLD, false,
                UnderlineStyle.SINGLE);
        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
        // Lets automatically wrap the cells
        timesBoldUnderline.setWrap(true);
 
        CellView cv = new CellView();
        cv.setFormat(times);
        cv.setFormat(timesBoldUnderline);
        cv.setAutosize(true);
 
        // Write a few headers
        addCaption(sheet, 0, 0, "First");
        addCaption(sheet, 1, 0, "Last");
        addCaption(sheet, 2, 0, "Description");
        addCaption(sheet, 3, 0, "Address");
}
    
    //Write the Customer Data to the Report
    private int createContent(WritableSheet sheet, List<Customer> custList) throws WriteException,
            RowsExceededException {
 
        int size = custList.size() ;
 
        // Add customer data to the Excel report
        for (int i = 0; i < size; i++) {
 
           Customer cust =  (Customer)custList.get(i) ;
 
            String first = cust.getCustFirst();
            String last = cust.getCustLast();
            String address = cust.getCustAddress();
            String desc = cust.getCustDescription();
  
            // First column
            addLabel(sheet, 0, i+2, first);
            // Second column
            addLabel(sheet, 1, i+2, last);
 
            // Third column
            addLabel(sheet, 2, i+2,desc);
 
            // Forth column
            addLabel(sheet, 3, i+2, address);
 
        }
 
        return size;
    }
 
    private void addCaption(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldUnderline);
        sheet.addCell(label);
    }
 
    private void addNumber(WritableSheet sheet, int column, int row,
                           Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times);
        sheet.addCell(number);
    }
 
    private void addLabel(WritableSheet sheet, int column, int row, String s)
            throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times);
        sheet.addCell(label);
    }
}

Modify the CustomerServiceImp class

Modify the CustomerServiceImp class. Replace the getCustomerData method with the following Java code.  

/*
    * 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
    *
    * The report argument specifies whether to generate a custom report based on the resultset
    * The fileName is the name of the report that is stored in the AME JCR
    */
	@Override
	public String getCustomerData(String filter, boolean report, String fileName) {
       Connection c = null;
       int rowCount= 0; 
       Customer cust = null; 
       String query = "";
       List<Customer> custList = new ArrayList<Customer>();
       try {
                                  
             // Create a Connection object
             c =  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);
                   
              }             
              
             //If user selected a custom report -- generate the report and store it in the JCR
             if (report == true)
              {
            	  String jcrFileName = fileName +".xls" ;

                  //Generate the custom report - pass custList
     	       	  WriteExcel cusReport = new WriteExcel(); 
            	  java.io.InputStream is = cusReport.exportExcel(custList); 
                       	  
            	  //Persist the custom report in the AEM JCR 
            	  writeToJCR(is, jcrFileName ); 
              }
              
              //return xml that contains all customer taken from MySQL
              return convertToString(toXml(custList));               
              
       }
   catch(Exception e)
       {
        e.printStackTrace();
       }
       return null;
   }

Notice if the report parameter is true, then a custom report is generated. A WriteExcel instance is created and its exportExcel method is invoked. The Java List object named custList is passed to the exportExcel method. An InputStream object that represents the custom report is returned.

Next the InputStream object is passed to the writeToJCR method. This method persists the custom report in the AEM JCR located at /content/reports. The following Java code represents the writeToJCR method.  

//Writes the Excel Report to the AEM JCR under /content/reports
   private String writeToJCR(InputStream iss, String fileName)
   {
       try
       {
       
       //Invoke the adaptTo method to create a Session
       ResourceResolver resourceResolver = resolverFactory.getAdministrativeResourceResolver(null);
       session = resourceResolver.adaptTo(Session.class);
              
      //Get the base node where reports are stored
       Node node = session.getNode("/content/reports");
       javax.jcr.ValueFactory valueFactory = session.getValueFactory();             
          
       //Create a Report Node that contains the custom report
       javax.jcr.Binary contentValue = valueFactory.createBinary(iss);
       Node fileNode = node.addNode(fileName, "nt:unstructured");
       fileNode.setProperty("jcr:data", contentValue);
       fileNode.setProperty("desc","Custom Report");
       Calendar lastModified = Calendar.getInstance();
       lastModified.setTimeInMillis(lastModified.getTimeInMillis());
       fileNode.setProperty("jcr:lastModified", lastModified);
       log.info("Created a custom report named "+fileName);   
              
       //Log out
       session.save();
       session.logout();  
                 
       // Return the path to the document that was stored in CRX. 
       return fileNode.getPath();
   }
   catch(Exception e)
   {
       e.printStackTrace();
   }
   return null; 
   } 

The following Java code represents the modified CustomServiceImp class.  

package com.adobe.cq.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import com.adobe.cq.sql.Customer ;
import java.io.InputStream ; 

import java.sql.SQLException;
import java.text.SimpleDateFormat;

import javax.jcr.Session;
import javax.jcr.Node; 

import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.felix.scr.annotations.Activate;
import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Deactivate;
import org.apache.felix.scr.annotations.Reference;
import org.apache.felix.scr.annotations.Service;
import com.day.commons.datasource.poolservice.DataSourcePool;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Calendar;
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;

import javax.jcr.Repository; 
import javax.jcr.SimpleCredentials; 
import javax.jcr.Node; 
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import java.text.SimpleDateFormat;
import java.util.Calendar;

//Sling Imports
import org.apache.sling.api.resource.ResourceResolverFactory ; 
import org.apache.sling.api.resource.ResourceResolver; 
import org.apache.sling.api.resource.Resource; 

@Component

@Service
public class CustomerServiceImp implements CustomerService {

	protected final Logger log = LoggerFactory.getLogger(this.getClass());
    
    
	private Session session;
	
   @Reference
   private DataSourcePool source;
   
   //Inject a Sling ResourceResolverFactory used to create a Session from within an OSGi bundle
   @Reference
   private ResourceResolverFactory resolverFactory;
	
   //Returns a connection to the Database using the configured DataSourcePool 
   private Connection getConnection()
   {
	        DataSource dataSource = null;
	        Connection con = null;
	        try
	        {
	            //Inject the DataSourcePool right here! 
	        	dataSource = (DataSource) source.getDataSource("Customer");
	            con = dataSource.getConnection();
	            return con;
	           
	          }
	        catch (Exception e)
	        {
	        	e.printStackTrace(); 
	        }
	            return null; 
   }
    
   //Adds a new customer record in the Customer table
	@Override
	public int injestCustData(String firstName, String lastName, String phone,String desc) {
       Connection c = null;
       
       int rowCount= 0; 
       try {
                                  
             // Create a Connection object
             c =  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,custDesc,custAddress) VALUES(?, ?, ?, ?, ?);";
              ps = c.prepareStatement(insert);
              ps.setInt(1, pkVal);
              ps.setString(2, firstName);
              ps.setString(3, lastName);
              ps.setString(4, phone);
              ps.setString(5, desc);
              ps.execute();
              return pkVal;
       }
       catch (Exception e) {
         e.printStackTrace();
        }
       finally {
         try
         {
    	   c.close();
         }
        
           catch (SQLException e) {
             e.printStackTrace();
           }
   }
       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
    *
    * The report argument specifies whether to generate a custom report based on the resultset
    */
	@Override
	public String getCustomerData(String filter, boolean report, String fileName) {
       Connection c = null;
       int rowCount= 0; 
       Customer cust = null; 
       String query = "";
       List<Customer> custList = new ArrayList<Customer>();
       try {
                                  
             // Create a Connection object
             c =  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);
                   
              }             
              
             //If user selected a custom report -- generate the report and store it in the JCR
             if (report == true)
              {
            	  String damFileName = fileName +".xls" ;
     	       	  WriteExcel cusReport = new WriteExcel(); 
            	  java.io.InputStream is = cusReport.exportExcel(custList); 
                       	  
            	  //Persist the custom report in the AEM JCR 
            	  writeToJCR(is, damFileName); 
              }
              
              //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;
    }
   
   
   
  
   //Writes the Excel Report to the AEM JCR under /content/reports
   private String writeToJCR(InputStream iss, String fileName)
   {
       try
       {
       
       //Invoke the adaptTo method to create a Session
       ResourceResolver resourceResolver = resolverFactory.getAdministrativeResourceResolver(null);
       session = resourceResolver.adaptTo(Session.class);
              
      //Get the base node where reports are stored
       Node node = session.getNode("/content/reports");
       javax.jcr.ValueFactory valueFactory = session.getValueFactory();             
            
       //Create a Report Node that contains the custom report
       javax.jcr.Binary contentValue = valueFactory.createBinary(iss);
       Node fileNode = node.addNode(fileName, "nt:unstructured");
       fileNode.setProperty("jcr:data", contentValue);
       fileNode.setProperty("desc","Custom Report");
       Calendar lastModified = Calendar.getInstance();
       lastModified.setTimeInMillis(lastModified.getTimeInMillis());
       fileNode.setProperty("jcr:lastModified", lastModified);
       log.info("Created a custom report named "+fileName);   
              
       //Log out
       session.save();
       session.logout();  
                 
       // Return the path to the document that was stored in CRX. 
       return fileNode.getPath();
   }
   catch(Exception e)
   {
       e.printStackTrace();
   }
   return null; 
   } 
}      

Modify the POM file

Modify the POM file to successfully build the updated OSGi bundle that uses the JExcel API. In the POM file located at C:\AdobeCQ\customerdb\bundle, add the JExcel API dependency:

 <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
 </dependency>

The following XML code represents the updated POM file.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd ">
    <modelVersion>4.0.0</modelVersion>
    <!-- ====================================================================== -->
    <!-- P A R E N T P R O J E C T D E S C R I P T I O N -->
    <!-- ====================================================================== -->
    <parent>
        <groupId>com.adobe.cq.sql</groupId>
        <artifactId>customerdb</artifactId>
        <version>1.0-SNAPSHOT</version>
    </parent>

    <!-- ====================================================================== -->
    <!-- P R O J E C T D E S C R I P T I O N -->
    <!-- ====================================================================== -->

    <artifactId>customerdb-bundle</artifactId>
    <packaging>bundle</packaging>
    <name>Csutomer Training Bundle</name>

    <!-- ====================================================================== -->
    <!-- B U I L D D E F I N I T I O N -->
    <!-- ====================================================================== -->
    <build>

        <plugins>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-scr-plugin</artifactId>
                <executions>
                    <execution>
                        <id>generate-scr-descriptor</id>
                        <goals>
                            <goal>scr</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.felix</groupId>
                <artifactId>maven-bundle-plugin</artifactId>
                <extensions>true</extensions>
                <configuration>
                    <instructions>
                        <Bundle-SymbolicName>com.adobe.cq.sql.customerdb-bundle</Bundle-SymbolicName>
                    </instructions>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.sling</groupId>
                <artifactId>maven-sling-plugin</artifactId>
                <configuration>
                    <slingUrl>http://${crx.host}:${crx.port}/apps/customer-training/install</slingUrl>
                    <usePut>true</usePut>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
       
       <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>
        
        <dependency>
            <groupId>com.day.commons</groupId>
            <artifactId>day.commons.datasource.poolservice</artifactId>
            <version>1.0.10</version>
            <scope>provided</scope>
        </dependency>
       
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>org.osgi.compendium</artifactId>
        </dependency>
        <dependency>
            <groupId>org.osgi</groupId>
            <artifactId>org.osgi.core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.felix</groupId>
            <artifactId>org.apache.felix.scr.annotations</artifactId>
        </dependency>
     
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
        </dependency>
         
          
        <dependency>
         <groupId>org.apache.felix</groupId>
   
         <artifactId>org.osgi.core</artifactId>
   
         <version>1.4.0</version>
      </dependency>
         
        
          
    <dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>jackrabbit-core</artifactId>
    <version>2.4.3</version>
    </dependency>
       
    <dependency>
    <groupId>org.apache.jackrabbit</groupId>
    <artifactId>jackrabbit-jcr-commons</artifactId>
    <version>2.4.3</version>
    </dependency>
   <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
           
    <dependency>
            <groupId>org.apache.sling</groupId>
            <artifactId>org.apache.sling.api</artifactId>
            <version>2.2.4</version>
            <scope>provided</scope>
        </dependency>
         
      <dependency>
         <groupId>javax.jcr</groupId>
         <artifactId>jcr</artifactId>
         <version>2.0</version>
      </dependency>
       
       <dependency>
            <groupId>com.day.cq.wcm</groupId>
            <artifactId>cq-wcm-api</artifactId>
            <version>5.5.0</version>
            <scope>provided</scope>
        </dependency>
         
        <dependency>
            <groupId>com.day.cq</groupId>
            <artifactId>cq-commons</artifactId>
            <version>5.5.0</version>
            <scope>provided</scope>
        </dependency>
               
    </dependencies>
     
    <repositories>
        <repository>
            <id>adobe</id>
            <name>Adobe Public Repository</name>
            <url>http://repo.adobe.com/nexus/content/groups/public/</url>
            <layout>default</layout>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>adobe</id>
            <name>Adobe Public Repository</name>
            <url>http://repo.adobe.com/nexus/content/groups/public/</url>
            <layout>default</layout>
        </pluginRepository>
    </pluginRepositories>       
      
</project>

Note:

After you modify the POM file, rebuild the OSGi bundle and then redeploy it to Experience Manager. Delete the old OSGi bundle before installing the updated bundle. For information about building and deploying the OSGi bundle, see Injecting a DataSourcePool Service into an Adobe Experience Manager OSGi bundle.

Modify the client JSP files

Modify two client JSP files: templateCustomer.jsp and query.json.jsp.

Modify the query.json.jsp file

The getCustomerData method exposed by the CustomerServiceImp class was modifed to include two new parameters:

  • report - a boolean value that specifies whether to generate the report
  • fileName - a string value that specifies the name of the report that is stored at /content/reports 

Modify the query.json.jsp file to reflect these changes. The following code represents the updated query.json.jsp file.

<%@include file="/libs/foundation/global.jsp"%>
<%@ page import="org.apache.sling.commons.json.io.*,org.w3c.dom.*" %><%
String filter = request.getParameter("filter");
String reportName = request.getParameter("reportName");
String genReport = request.getParameter("genReport");

boolean report=false ; 
if (genReport.equals("true"))
	report = true ; 


//create a CustomerService instance
com.adobe.cq.sql.CustomerService cs = sling.getService(com.adobe.cq.sql.CustomerService.class);
 
String XML = cs.getCustomerData(filter, report, reportName); 
   

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

Modify the templateCustomer.jsp file

Modify the templateCustomer JSP file. This change is required to change the main web page so that the Generate Report checkbox is included as well as the text box that lets a user specify the name of the report. If the user clicks the Generate Report checkbox,. then the custom AEM service generates a report and stores it at /content/reports. The name of the report is specfied by entering a value into the Report Name field.

The following JavaScript code represents the templateCustomer 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,
          "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 +"&phone="+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 genReport ; 

    var reportName = $('#reportName').val() ;

    if($("#reportcb").is(':checked'))
        genReport = "true";   // checked
	else
      genReport = "false";  // unchecked
         
    var url = location.pathname.replace(".html", "/_jcr_content.query.json") + "?filter="+ filter +"&reportName="+reportName +"&genReport="+genReport;
         
    $.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>
	<input type="checkbox" id="reportcb" name="reportcb" value="reportcb">Generate Report<br>	
     </div>
      <div>
	 <label for="reportName">Report Name:</label>
     <input type="reportName" id="reportName" name="reportName" value="AEMReport" />
     </div>    
     <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>

Retrieving the custom report

The custom report that is generated by the AEM service is a node of type nt:unstructured. The reason why nt:unstructured is used is so custom properties can easily be added to the node. A custom property makes is easier to query for content by using the JCR API. For example a report node that is created in this development article contains the properties that are shown in the following illustration.

 

Props

In this example, the desc property specifies Custom Report. You can use this property to search for the report by using the JCR SQL. For example, consider the following JCR SQL code.

 

//Obtain the query manager for the session ...
javax.jcr.query.QueryManager queryManager = session.getWorkspace().getQueryManager();

//Setup the query to search for a Custom Report
String sqlStatement="";

 sqlStatement = "SELECT * FROM [nt:unstructured] WHERE CONTAINS(desc, 'Custom Report')";

javax.jcr.query.Query query = queryManager.createQuery(sqlStatement,"JCR-SQL2");

//Execute the query and get the results ...
javax.jcr.query.QueryResult result = query.execute();

//Iterate over the nodes in the results ...
  javax.jcr.NodeIterator nodeIter = result.getNodes();

 //Get the report whose description is Custom Report
 while ( nodeIter.hasNext() ) {


        javax.jcr.Node node2 = nodeIter.nextNode();

        //Get the jcr:data property which is binary data that 
        //represents the report
        Binary  myData = node2.getProperty("jcr:data").getBinary();

        //Get the InputStream and write out to the file
        java.io.InputStream iss = myData.getStream();
        java.io.OutputStream outputStream =
    
       new java.io.FileOutputStream(new File("C:\\CustomReport.xls"));

        int read = 0;
        byte[] bytes = new byte[1024];

        while ((read = iss.read(bytes)) != -1) {
                    outputStream.write(bytes, 0, read);
        }

 System.out.println("Retrieved the custom Excel report!");

In this example, JCR API code is used to query for the custom report. The jcr:data property is read, its binary data is retrieved (this represents the custom report) and the binary data is written out as a file. This query code can be used in a Java console application. Be sure to create a Session object and include the JCR API JAR file. For information about using the JCR API to retrieve node values from a Java application, see Programmatically Accessing Experience Manager Content using the JCR API.

See also

Please refer to the AEM community page for other articles that discuss how to build AEM services/applications by using an Adobe Maven Archetype project.

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