Article summary

Summary

Discusses how to create a Java Swing application that queries the AEM JCR. The Java application can display JCR data based on a score property and export the JCR result set to Excel.

This was updated in Nov 2015. Besure to include the correct JackRabbit JAR file for AEM 5.6. You can use jackrabbit-standalone-2.6.5.jar. You can obtain the jackrabbit-standalone JAR file from http://jackrabbit.apache.org/jcr/jcr-api.html.

Also included is a sample AEM package that contains sample users that is used by the client AEM app. If you are following along with this example AEM Java application, be sure to install the sample users package; otherwise, the application will not work.

A special thank you to Ratna Kumar Kotla, a top community member, for helping test this article and ensuring it works! 

Digital Marketing Solution(s) Adobe Experience Manager (Adobe CQ)
Audience
Developer (intermediate)
Required Skills
Java, JCR, JCR-SQL
AEM Version(s)

AEM 5.5, AEM 5.6, AEM 6, AEM 6.1

To read AEM 6.3 version, see Developing Java Swing Applications that displays Adobe Experience Manager 6.3 Data.

Note:

You can download an AEM package that contains the AEM sample users that this Java Swing application works with. Install this sample users package using AEM package manager to successfully run this sample community app.  These sample users are for running this community application and teaching purposes only and not meant to go into production.

Download

Introduction

When working with Adobe Experience Manager (AEM), sometimes you need to query data that is located in the AEM JCR and view results. Typically you write an AEM component to display JCR data results within an AEM web application. However, sometimes you want to view JCR data from a tool that is separate from an AEM web application. For example, consider members that are stored as AEM JCR data. Assume that you want the quickly view screen names, display names, and score values.

In this situation, a Java Swing application is a good choice to use to build a custom tool that enables you to quickly query JCR data and view the result set. You can display the result set in a Swing data type that extends javax.swing.JTable, as shown in this illustration. 

client


This Java swing application queries data from the AEM JCR and displays the data into a table with the following columns:

  • Number - the record number
  • Points - the points that the community has
  • Name - the name of the member
  • Screen Name - the screen name of the member

The data is stored in the AEM JCR as a series of nodes, child nodes and properties. The following illustrations shows CRXDE Lite and how the data is organized within the AEM JCR.

 

UsersJCR

In this example, notice that each user is represented as a node under:

/home/users/test/

Each child node, such as Scott44, is a node of type is rep:User. This is important and is used to query the rep:User nodes by using JCR-SQL. For example:

String sqlStatement = "select * from [rep:User] where isdescendantnode('/home/users/test') ";

This is shown later in this development article.

Under each node of type rep:User is a node named profile of type nt:unstructured. The profile node supports these properties:

  • displayName - the name of the member
  • screen_name - the screen name of the member

The profile node has a child node named scoring (type sling:Folder). This node has a property named score that contains the score of the member. For each member, the Java application logic drills down and retrieves the value of the score property.

For each member, the displayName, screen_name, and score property values are retrieved from the AEM JCR and displayed in the Java swing application. The Java swing application supports two additional features.

The first feature is the ability to query members based on points (the value of the score property of the scoring node). In this example, you can display members based on star values:

  • Five stars - display members with a score of 15,000 and higher.
  • Four stars - display members with a score between 5000-14999.
  • Three stars - display members with a score between 1000-4999.
  • Two stars - display members with a score of between 300-999.
  • One star - display members with a score of between 20-299.
  • Zero stars- display members with a score of between 0 -19.
  • All Stars - display all members.

The other feature is the ability to export the JCR result set to an Excel spreadsheet. To export the JCR data to an Excel spreadsheet, the Java Excel API is used. The Excel API lets you dynamically create an Excel spreadsheet and populate it with JCR data. When you click the Excel button, the application writes  the user data to an Excel file in the applications local folder. For information, see http://jexcelapi.sourceforge.net/.

Note:

To follow along with this development article, install the sample users package found at the start of this article.

To create the Java swing application created in this development article, the Eclipse IDEA is used. The type of project is a Java project that contains these classes:

  • FileModel - extends the AbstractTableModelJava class and defines Java application logic that uses the JCR API and JCR SQL to query the AEM JCR. This class drills down into child nodes for each rep:User node.
  • FileTable -  extends the Java JFrame class and defines the user interface of the application. This class defines a JTextField instance, a JComboBox instance, two JButton instances, and JTable instance. The JTable instance displays the data queried from the AEM JCR. 
  • members - a Java class that defines member properties such as the display name. Each member is a private class member that can be set using setter methods and retrieved by using getter methods.
  • WriteExcel - a Java class that uses the Java Excel API and exports the data to an Excel spreadsheet.

Create an Eclipse project 

The first step is to create an Eclipse Java project as shown here.

Project

Create an Eclipse project by performing these tasks:

  1. Start Eclipse.
  2. Click File, New, Java Project.
  3. In Project Name, type JavaSwingJCRApp.
  4. In the Project SDK, specify the location to your Java SDK location. 
  5. Click Next.
  6. Click Finish.  

Add the following JAR files in your Eclipse project's class path:

  • jackrabbit-standalone-2.6.5.jar - required to connect to the AEM repository from a Java client app.
  • jxl-2.6.12.jar - required to use Excel functionality in your client application.

To successfully connect to the AEM JCR, you still have to include the jackrabbit-standalone-2.4.3.jar (for AEM 5.5) or jackrabbit-standalone-2.6.5.jar (for AEM 5.6, or later) into your project's class path. You can obtain this JAR file from the Java JCR API web page at http://jackrabbit.apache.org/jcr-api.html.

Likewise you can download the Excel JAR file from the Maven web page. 

To add these JAR files to your Eclipse project's class path, perform these tasks:

  1. Right click on the project.
  2. Select Properties.
  3. Select Java Build Path.
  4. In the Javava Build Path dialog, click Add External JAR and browse the JAR files that you downloaded.
  5. Click OK.

Setup the Java class files

Using your project, setup the required Java classes. To setup a class file, perform these tasks:

1. Click src, main, Java.

2. Right click and select New, Class.

3. In  the Name field, specify FileTable.

4. Click Ok.

5. Repeat stepts 2-4 for FileModel, members and WriteExcel.  

Create the FileTable class

The FileTable class extends JFrame and represents the frame of your Java application. For information about the JFrame class, see JFrame.

The FileTable class also contains a Java main method that represents the entry point into your Java application, as shown here.

public static void main(String args[]) {
    FileTable ft = new FileTable();
    ft.setVisible(true);
}

The FileTable class defines these class members.

JTextField hostField;
JComboBox patternList;
int m_stars = -1;

String[] patternStars = {
"All Stars",
"5 Stars",
"4 Stars",
"3 Stars",
"2 Stars",
"1 Star",
"0 Stars"
};

The JTextField represents a text field where the user can specify the URL for the AEM server. The default value is localhost:4502. The JComboBox lets a user pick how many stars to select. The stars line up with the score of the member (as explained eariler in this development article). The m_stars class member stores the value of the stars. The value -1 is the default value and means to select all members. The patternStars class member is a String array that is used to populate the JComboBox instance.  

A FileModel instance represents the model that contains the data that is displayed in the JTable. In this application, the FileModel contains data queried from the AEM JCR. Notice in the following code, a FileModel instance is created. Then notice that it is used to create a JTable instance. 

The JTable instance is used to create a JScrollPane instance, as shown here.  

 

final FileModel fm = new FileModel();
JTable jt = new JTable(fm);
jt.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
jt.setColumnSelectionAllowed(true);

//Create the Scroll Pane for the Main Java GUI
//JScrollPane consumes the Table
JScrollPane jsp = new JScrollPane(jt);

 

A JPanel instance is created and is used to store the other Java swing controls, such as the JComboBox instance. To add a Java swing component to a JPanel instance, invoke the JPanel object's add method.

//Create a JPanel instance
JPanel p1 = new JPanel();
p1.setLayout(new GridLayout(4, 2));
p1.add(new JLabel("Enter the AEM URL: "));
p1.add(hostField = new JTextField());
p1.add(new JLabel("Enter Community Member query: "));
p1.add(patternList = new JComboBox(patternStars));
p1.add(new JLabel("Search"));
JButton jb = new JButton("Search");

The following Java code represents the entire FileTable class. Add this code to the FileTable class that you created.

// FileTable.java
// A test frame for the custom table model, FileModel.
//

import java.awt.*;
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.Date;
import java.io.File;
import javax.swing.JOptionPane;

public class FileTable extends JFrame {

    JTextField hostField;
    JTextField queryField;
    JComboBox patternList;
    int m_stars = -1;

    String[] patternStars = {
            "All Stars",
            "5 Stars",
            "4 Stars",
            "3 Stars",
            "2 Stars",
            "1 Star",
            "0 Stars"
    };

   //In the FileTale constructor define the GUI for the Java swing application 
    public FileTable() {
        super("Digital Marketing Community Members");
        setSize(300, 200);
        setDefaultCloseOperation(EXIT_ON_CLOSE);

        final FileModel fm = new FileModel();
        JTable jt = new JTable(fm);
        jt.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
        jt.setColumnSelectionAllowed(true);


        //Create the Scroll Pane for the Main Java GUI
        //Pass the JTable instance to the JScrollPane constructor
        JScrollPane jsp = new JScrollPane(jt);

        //Create a JPanel instance
        JPanel p1 = new JPanel();
        p1.setLayout(new GridLayout(4, 2));
        p1.add(new JLabel("Enter the AEM URL: "));
        p1.add(hostField = new JTextField());
        hostField.setText("localhost:4502"); // Set default for AEM URL
        p1.add(new JLabel("Enter Community Member query: "));
        p1.add(patternList = new JComboBox(patternStars));
        p1.add(new JLabel("Search"));
        JButton jb = new JButton("Search");
        JButton jbe = new JButton("Excel");
        jb.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
               String url =  hostField.getText() ;
                fm.setFileStats(m_stars, url);     //entry point ito the grid   - this will hook into the AEM JCR
            }
        } );

        patternList.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                JComboBox cb = (JComboBox)e.getSource();
                String stars = (String)cb.getSelectedItem();

                if (stars.equals("All Stars"))
                    m_stars = -1;

                else if (stars.equals("5 Stars"))
                    m_stars = 5;

                else if (stars.equals("4 Stars"))
                    m_stars = 4;

                else if (stars.equals("3 Stars"))
                    m_stars = 3;

                else if (stars.equals("2 Stars"))
                    m_stars = 2;

                else if (stars.equals("1 Star"))
                    m_stars = 1;

                else if (stars.equals("0 Stars"))
                    m_stars = 0;

                JOptionPane.showMessageDialog (
                        null, "You selected "+stars);
            }
        } );


        jbe.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent e) {
                // qtm.setHostURL(hostField.getText().trim());  //entry point ito the grid

                int recs =  fm.doExcel();
                JOptionPane.showMessageDialog (
                        null, "You exported " +recs+ " AEM community records to Excel" );
            }
        } );

        p1.add(jb);
        p1.add(new JLabel("Export to Excel"));
        p1.add(jbe);

        //Add the JPanel
        getContentPane().add(p1, BorderLayout.NORTH);

        //Add the Scroll Pane
        getContentPane().add(jsp, BorderLayout.CENTER);
    }

    public static void main(String args[]) {
        FileTable ft = new FileTable();
        ft.setVisible(true);
    }
}

Create the FileModel class 

The FileModel class extends AbstractTableModel that is the base class for a JTabel model. This class exposes methods that are used to populate the JTable instance, such as the getColumnName method. For information about this class, see AbstractTabelModel.

The FileModel class contain Java application logic that queries the AEM JCR in order to create the data that is displayed in the JTable instance. To query the AEM JCR, you can use JCR-SQL. Data that is displayed in the JTable is stored as a List instance where each element is a member instance. The List instance is defined as a class member of the FileModel class. 

private List<members> l ;

To setup the JTable instance, a data member named titles is created. This data member is a String array that stores the column titles, as shown here.

String titles[] = new String[] { "Number", "Points", "Name","Screen Name"};

The titles data member is used in the getColumnName method. This  method returns the String value that is located at the given element.   

public String getColumnName(int c) { return titles[c]; }

JCR data is displayed in the JTable instance by using the getDataAt method. This method returns data located in the List data member at the given element. 

public Object getValueAt(int rowIndex, int columnIndex) {
if(columnIndex==0){

return l.get(rowIndex).getNum();
}

if(columnIndex==1){
return l.get(rowIndex).getScore();
}
else if(columnIndex==2){
return l.get(rowIndex).getName();
}
else if(columnIndex==3){
return l.get(rowIndex).getDisplay();
}

return null;
}

Each element in the l data member is a member instance that contains data retrieved from the AEM JCR. 

setFileStats method

The entry point into the FileModel class is a method named setFileStats. That is, this method  is called  from the FileTable class. 

 

//  called from the FileTable class
public void setFileStats(int stars, String url) {

    //Set the community score level
    m_stars  = stars ;

    List<members> theList = getRepository(url);
    l = theList ;

    // Refresh the JTable
    fireTableDataChanged();
}

The setFileStats method accepts an int value that specifies the members to place in the List collection based on the score property. For example, if -1 is passed to this method, then all members are added to the List collection. Else only members that correspond to the stars argument are added to the collection. The second parameter is the URL to the AEM server as specified by the FileTable class.  

Notice that a method named getRepository is invoked that returns a List instance. Each element in the List data collection is a member instance. The return value of getRespository is assigned to the data member named l and the fireTableDataChanged method is invoked. This method refreshes the JTable with the data located in the l data member.  

getRepository method

In the getRepository method, a Session is created by using the JCR API.

//Retrieve member data from the JCR
public List<members> getRepository(String url)
{

    try {

        String aemUrl = "http://"+url +"/crx/server" ;

        //Create a connection to the CQ repository running on local host
        Repository repository = JcrUtils.getRepository(aemUrl);

         //Create a Session
        javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()));

A JCR query is defined that searches for all rep:User nodes that represents the members that are displayed in the JTable instance. The result set is iterated over and each path of the user node is obtained and passed to another method named getProfileNode. The path value is used to perform another query to obtain the child profile node.

The following method represents the getRepository method.

 

//Retrieve member data from the JCR
    public List<members> getRepository(String  url)
    {

        try {

            String aemUrl = "http://"+url +"/crx/server" ;
            //Create a connection to the CQ repository running on local host
            Repository repository = JcrUtils.getRepository(aemUrl);

            //Allocate memory to the List
            memberList = new ArrayList();

            //Create a Session
            javax.jcr.Session session = repository.login( new SimpleCredentials("readonly", "readonly".toCharArray()));

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

            //SELECT * FROM [rep:User]
           String sqlStatement = "select * from [rep:User] where isdescendantnode('/home/users/test') ";

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

            while ( nodeIter.hasNext() ) {

                //For each node-- get the node path
                javax.jcr.Node node = nodeIter.nextNode();

                String myPath = node.getPath();

                getProfileNode( queryManager, myPath)  ;
            }

            // Save the session changes and log out
            session.save();
            session.logout();


            recnum=1;
            return memberList ;
        }
        catch(Exception e){
            e.printStackTrace();
        }

        return null;
    }

getProfileNode method

The getProfileNode method is used to obtain the child profile node (type nt:unstructured) of the parent user node (type user:Rep). To obtain the child node, the following JCQ-SQL query is used. Notice that the path value is also used in this query.

String sql= "SELECT * FROM nt:unstructured WHERE jcr:path LIKE '"+path +"/%' AND NOT jcr:path LIKE '"+path +"/%/%'";

This query returns all child nodes; however, nodes at a deeper level (for example, grandchild nodes) are not returned due to this syntax:

AND NOT jcr:path LIKE '"+path +"/%/%

The result set is iterated through. If the name of the node is profile, propeties of the node are read. The following Java code represents the getProfileNode method.

 

public void getProfileNode(javax.jcr.query.QueryManager qm, String path)
    {
        try
        {

            String sql= "SELECT * FROM nt:unstructured WHERE jcr:path LIKE '"+path +"/%' AND NOT jcr:path LIKE '"+path +"/%/%'";

            javax.jcr.query.Query query = qm.createQuery(sql, Query.SQL);

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


            while ( nodeIter.hasNext() ) {

                //For each node-- Check to see if is the profile node
                javax.jcr.Node node = nodeIter.nextNode();

                //Get the node name
                String nodeName = node.getName();


                //Get the profile node
                if (nodeName.equals("profile") == true )
                {
                    //Create a members instance
                    members member = new members();

                    String displayName="";
                    String screen_name="";
                    try
                    {
                        //Get profile data
                        displayName = node.getProperty("displayName").getString();
                        screen_name = node.getProperty("screen_name").getString();
                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                        break;
                    }
                    String score = getScore(node, qm) ;

                    int score2 = new Integer(score)  ;

                    //Only populate the collection if score lines up with m_stars

                    if (m_stars == -1) //Get all members
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 0 stars mean that score is between 0 - 20 points
                    else if (m_stars == 0  && ((score2 >= 0) && (score2 < 20) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }



                    // 1 stars mean score is between 20 - 299 points
                    else if (m_stars == 1  && ((score2 >= 20) && (score2 < 300) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                          recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 2 stars mean score is between 300 - 999 points
                    else if (m_stars == 2  && ((score2 >= 300) && (score2 < 1000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                         recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 3 stars mean score is between 1000 - 4999 points
                    else if (m_stars == 3  && ((score2 >= 1000) && (score2 < 5000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                         recnum++;

                        //push the member
                        memberList.add(member) ;
                    }


                    // 4 stars mean score is between 5000 - 14999 points
                    else if (m_stars == 4  && ((score2 >= 5000) && (score2 < 15000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 5 stars mean score is over  14999 points
                    else if (m_stars == 5  && (score2 >= 15000)  )
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                         recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                }
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }

Note:

The FileModel class has a method named getScore that returns the score of each member. This method is shown in the full FileModel class.  

FileModel Class

The following Java code represents the FileModel class.  Add this code to the FileModel class that you created.

// FileModel.java
// A custom table model to display data queried from the AEM JCR.
//
import javax.swing.table.*;
import java.util.Date;
import java.io.File;

import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.Node;
import javax.jcr.query.Query;
import java.util.List ;
import java.util.ArrayList ;
import org.apache.jackrabbit.commons.JcrUtils;
import org.apache.jackrabbit.core.TransientRepository;

public class FileModel extends AbstractTableModel {

    String titles[] = new String[] { "Number", "Points", "Name","Screen Name"};


    //Define the List to store members taken from the JCR
    private List<members> memberList = null;
    private List<members> l;  // This list represents the latest query -- we can export this out to an excel spreadsheet
    private int recnum = 1;
    private int m_stars = -1 ;// -1 is the default - meaning get all scores

    Class types[] = new Class[] {
            String.class, String.class,String.class, String.class
    };

    public FileModel() { this("."); }

    public int doExcel()
    {
        WriteExcel excel = new WriteExcel();
        return excel.exportExcel(l)  ;
    }

    public FileModel(String dir) {
        setFileStats(-1, "localhost:4502") ;
    }

    // Implement the methods of the TableModel interface 
    //  Only getRowCount(), getColumnCount() and getValueAt() are
    // required.  The other methods tailor the look of the table.
    public int getRowCount() { return l.size();}
    public int getColumnCount() { return titles.length; }
    public String getColumnName(int c) { return titles[c]; }
    public Class getColumnClass(int c) { return types[c]; }

    //Use the List data member to populate the JTable
    public Object getValueAt(int rowIndex, int columnIndex) {
        if(columnIndex==0){

            return l.get(rowIndex).getNum();
        }

        if(columnIndex==1){
            return l.get(rowIndex).getScore();
        }
        else if(columnIndex==2){
            return l.get(rowIndex).getName();
        }
        else if(columnIndex==3){
            return l.get(rowIndex).getDisplay();
        }

        return null;
    }

    //  called from the FileTable class
    public void setFileStats(int stars, String url) {

        //Set the community score level
        m_stars  = stars ;

        List<members> theList = getRepository(url);

        l = theList ;

        // Just in case anyone's listening...
        fireTableDataChanged();
    }

    //Retrieve member data from the JCR
    public List<members> getRepository(String url)
    {

        try {

            String aemUrl = "http://"+url +"/crx/server" ;
            //Create a connection to the CQ repository running on local host
            Repository repository = JcrUtils.getRepository(aemUrl);


            //Allocate memory to the List
            memberList = new ArrayList();

            //Create a Session
            javax.jcr.Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()));

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

            //Specify the AEM JCR path where examples users are stored at /home/users/test

              String sqlStatement = "select * from [rep:User] where isdescendantnode('/home/users/test') ";


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

            long mySize = nodeIter.getSize();

            while ( nodeIter.hasNext() ) {

                //For each node-- get the path of the node
                javax.jcr.Node node = nodeIter.nextNode();

                String myPath = node.getPath();

                getProfileNode( queryManager, myPath)  ;
            }

            // Save the session changes and log out
            session.save();
            session.logout();


            recnum=1;
            return memberList ;
        }
        catch(Exception e){
            e.printStackTrace();
        }

        return null;
    }


    public void getProfileNode(javax.jcr.query.QueryManager qm, String path)
    {
        try
        {

            String sql= "SELECT * FROM nt:unstructured WHERE jcr:path LIKE '"+path +"/%' AND NOT jcr:path LIKE '"+path +"/%/%'";

            javax.jcr.query.Query query = qm.createQuery(sql, Query.SQL);

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


            while ( nodeIter.hasNext() ) {

                //For each node-- Check to see if is the profile node
                javax.jcr.Node node = nodeIter.nextNode();

                //Get the node name
                String nodeName = node.getName();


                //Get the profile node
                if (nodeName.equals("profile") == true )
                {
                    //Create a members instance
                    members member = new members();

                    String displayName="";
                    String screen_name="";

                    try
                    {
                        //Does this node have a displayName property

                        //Make sure node has displayName
                        if (node.hasProperty("displayName"))
                           displayName = node.getProperty("displayName").getString();


                        //Make sure node has screen_name
                        if (node.hasProperty("screen_name"))
                            screen_name = node.getProperty("screen_name").getString();

                    }
                    catch (Exception e)
                    {
                        e.printStackTrace();
                        break;
                    }
                    String score = getScore(node, qm) ;

                    int score2 = new Integer(score)  ;

                    //Only populate the collection if score lines up with m_stars

                    if (m_stars == -1) //Get all members
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 0 stars mean that score is between 0 - 19 points
                    else if (m_stars == 0  && ((score2 >= 0) && (score2 < 20) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }



                    // 1 stars mean score is between 20 - 299 points
                    else if (m_stars == 1  && ((score2 >= 20) && (score2 < 300) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 2 stars mean score is between 300 - 999 points
                    else if (m_stars == 2  && ((score2 >= 300) && (score2 < 1000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 3 stars mean score is between 1000 - 4999 points
                    else if (m_stars == 3  && ((score2 >= 1000) && (score2 < 5000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }


                    // 4 stars mean score is between 5000 - 14999 points
                    else if (m_stars == 4  && ((score2 >= 5000) && (score2 < 15000) ))
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                    // 5 stars mean score is over  14999 points
                    else if (m_stars == 5  && (score2 >= 15000)  )
                    {
                        member.setNum(String.valueOf(recnum) );
                        member.setName(displayName);
                        member.setDisplay(screen_name);
                        member.setScore(score);
                        recnum++;

                        //push the member
                        memberList.add(member) ;
                    }

                }
            }
        }
        catch(Exception e){
            e.printStackTrace();
        }
    }


    //Drill down to get the score for the member
    public String getScore(javax.jcr.Node node, javax.jcr.query.QueryManager qm)
    {
        try
        {
            String path = node.getPath();
            String sql= "SELECT * FROM sling:Folder WHERE jcr:path LIKE '"+path +"/%' AND NOT jcr:path LIKE '"+path +"/%/%'";

            javax.jcr.query.Query query = qm.createQuery(sql, Query.SQL);

            //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();
            String score = "0";
            while ( nodeIter.hasNext() ) {

                //For each node-- check to see if the node is scoring
                javax.jcr.Node nodeSc = nodeIter.nextNode();

                String nodeName = nodeSc.getName();

                //is this the scoring node
                if (nodeName.equals("scoring") == true )
                {
                    //Get score property
                    //Make sure node has score
                    if (node.hasProperty("score"))
                         score = nodeSc.getProperty("score").getString();

                }
            }
            return score;
        }
        catch(Exception e){
            e.printStackTrace();
        }

        return null;
    }

}

Create the member class

The member class simply stores user data retrieved from the AEM JCR and displayed in the JTable. This class has these data members.

private String num ;
private String score;
private String name;
private String display;

The following Java code represents the members class. Add this Java code to the members class that you created.  

public class members {

    private String num ;
    private String score;
    private String name;
    private String display;



    public void setScore (String  score)
    {
        this.score = score;
    }

    public void setName(String  name)
    {
        this.name = name;
    }



    public void setNum (String num )
    {
        this.num = num;
    }

    public String getNum ( )
    {
        return this.num;
    }


    public void setDisplay (String  display)
    {
        this.display = display;
    }

    public String getScore ( )
    {
        return this.score;
    }

    public String getName()
    {
        return this.name;
    }

    public String getDisplay ()
    {
        return this.display ;
    }
}

Create the WriteExcel class

The WriteExcel class simply takes the List collection that is used to populate the JTable object and writes the data to an Excel spreadsheet. This class uses the Java Excel API. The Java Excel API dependency that is required to work with this API is already in the POM dependencies section.

The following code represents the WriteExcel class. Add this Java code to the WriteExcel class that you created. This code writes the JCR data to an Excel file named JCRMembers.xls in the current application directory.  

import java.io.File;
import java.io.IOException;
import java.util.List;
import java.util.Locale;

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;


public class WriteExcel {

    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
    private String inputFile;

    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public int write( List<members> memberList) throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "EN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("Comumunity Report", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        createLabel(excelSheet)   ;
        int size =  createContent(excelSheet, memberList);

        workbook.write();
        workbook.close();

        return size ;
    }

    private void createLabel(WritableSheet sheet)
            throws WriteException {
        // Lets 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, "Number");
        addCaption(sheet, 1, 0, "Points");
        addCaption(sheet, 2, 0, "Name");
        addCaption(sheet, 3, 0, "Screen Name");


    }

    private int createContent(WritableSheet sheet, List<members> memberList) throws WriteException,
            RowsExceededException {

        int size = memberList.size() ;


        // This is where we will add Data from the JCR
        for (int i = 0; i < size; i++) {

            members mem =  (members)memberList.get(i) ;

            String number = mem.getNum();
            String points = mem.getScore();
            String name = mem.getName();
            String display = mem.getDisplay();



            // First column
            addLabel(sheet, 0, i+2, number);
            // Second column
            addLabel(sheet, 1, i+2, points);

            // Second column
            addLabel(sheet, 2, i+2,name);

            // Second column
            addLabel(sheet, 3, i+2, display);




        }

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


    public int exportExcel( List<members> memberList)
    {
        try
        {
            setOutputFile("JCRMembers.xls");
            int recs =  write( memberList);
            return recs ;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        return -1;
    }


}

See also

Congratulations, you have just created a sample Java Swing application that queries the AEM JCR and exports the data to Excel. Please refer to the AEM community page for other articles that discuss how to build 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