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

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

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

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

JCROakDatabaseSQL2Query