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 QueriesWhile 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. Furthermore - 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....

}