Performing SQL2 queries on Jackrabbit and AEM

With xpath being deprecated since JCR 2, SQL2 remains the most effective way to perform raw queries against Jackrabbit. The two ways I've used are described below:

1. Using resourceResolver.findResources - I.E. The Sling Way
2. Using queryManager.createQuery - I.E. The Jackrabbit Way

There's pros and cons to both, so let's dive in...

ResourceResolver.findResources

	public Iterator<Page> getChildren() {
	ResourceResolver resourceResolver = resource.getResourceResolver();
	String query = "SELECT * FROM [nt:unstructured] AS s "
	+ "WHERE [title] IS NOT NULL "
	+ "ORDER BY [created] DESC";
	Iterator<Resource> children = resourceResolver.findResources(query, Query.JCR_SQL2);
	return ResourceUtil.adaptTo(children,Page.class);
	}

Here we can see a simple query being executed and returning an iterable that can be adapted to any object. This works brilliantly for simple queries. Now what if we want to paginate or limit these posts? Enter queryManager...

QueryManager.createQuery

	public NodeIterator getChildren() {
	QueryManager queryManager = session.getWorkspace().getQueryManager();
	String query = "SELECT * FROM [nt:unstructured] AS s "
	+ "WHERE [title] IS NOT NULL "
	+ "ORDER BY [created] DESC";
	Query query = queryManager.createQuery(query, Query.JCR_SQL2);
	// Set Offset
	if (offset != null) {
	query.setOffset(offset);
	}
	// Set Limit
	if (limit != null) {
	query.setLimit(limit);
	}
	QueryResult result = query.execute();
	return result.getNodes();
	}

With a few changes, we can now leverage the methods query.setOffset and query.setLimit. 

While there are many advantages to this approach, there is one big disadvantage: There's no simple way to adapt a plain JCR NodeIterator into a resource without writing a custom adapter. However, we can do something very clever in Sightly...

	<sly data-sly-use.list="org.millr.slick.components.common.ListPosts" data-sly-list.page="${list.children}">
	<sly data-sly-resource="${page.path @ resourceType='slick/publish/post/content'}"/>
	</sly>

By using data-sly-resource, we can grab each child node using our path and render it using our given resourceType.