Monday 14 December 2015

Example JCR SQL Queries


Search for JCR retPlugin nodes in JCR

The following code example shows JCR SQL2 syntax to search for all rtePlugin nodes under /apps.

import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.Node;
import javax.jcr.query.Query;

import org.apache.jackrabbit.commons.JcrUtils;

public class TestJCR {


    public static void main(String [] args)
    {


        try {

            String aemUrl = "http://localhost:4502/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()));

            //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 [nt:unstructured] as p where isdescendantnode (p, [/apps]) AND NAME(p)='rtePlugins'";


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

                System.out.println(myPath);
            }

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


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

    }



}

Using Date Values in JCR SQL2 Queries

While developing AEM tools for my team, I am using some date-based queries while performing JCR-SQL2 queries. While this will not be a community article, I will still post the date-based queries so people new to JCR SQL2 can reference this. Futhermore - the dates are variables used in the query string.

This example is based on this stackoverflow thread: http://stackoverflow.com/questions/15117075/jcr-sql2-query-comparing-dates.

The important thing to consider and is shown below is:

The best way to use dates in a query is to use CAST to force the conversion of a string representation of a date/time into a DATE value used by JCR.

 String sqlStatement2 = "select * from [rep:User] where isdescendantnode('/home/users/mypath') and [jcr:created] > cast('"+ year +"-"+monthStr+"-"+dayStr+"T00:00:00.000+02:00' as date) and [jcr:created] < cast('"+ year2 +"-"+monthStr2+"-"+dayStr2+"T23:59:59.000+02:00' as date)";

  javax.jcr.query.Query query = queryManager.createQuery(sqlStatement2,"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 path of the node
                javax.jcr.Node node = nodeIter.nextNode();

                String myPath = node.getPath();
                //do something with path value.... 


}

Join the Adobe Experience Cloud Community 

Join the Adobe Experience Cloud Community by clicking this banner




I (Scott Macdonald) am a Senior Digital Marketing Community Manager at Adobe Systems with 20 years in the high tech industry. I am also a programmer with knowledge in Java, JavaScript, C#,C++, HTML, XML and ActionScript. If  you would like to see more CQ or other Adobe Digital Marketing end to end articles like this, then leave a comment and let me know what content you would like to see.


TwitterFollow the Digital Marketing Customer Care team on Twitter @AdobeExpCare.

YouTube: Subscribe to the AEM Community Channel