How do I do a multi-level properties query in JCR 2.0 SQL2?

Question

I want to do something like this, but in JCR_SQL2 (since XPATH is now deprecated in JCR 2.0).

    QueryManager queryManager = session.getWorkspace().getQueryManager();
    String queryExpression = 
        NodeNames.PLUGIN.jcrName()+"[@"+NodeProps.ID.jcrName()+"='"+pluginId+"']"
        +"/"+
        NodeNames.PLATFORM.jcrName()+"[@"+NodeProps.NAME.jcrName()+"='"+platform+"']"
        +"/"+
        NodeNames.VERSION.jcrName()+"[@"+NodeProps.VERSION.jcrName()+"='"+version+"']";
    query = queryManager.createQuery( queryExpression, Query.XPATH);
    return query.execute().getNodes().nextNode();

I can get the first node like this:

    Query query = queryManager.createQuery(
            "select * from [nt:unstructured] as p where p.["+NodeProps.ID.jcrName()
            +"] = '"+pluginId+"'", Query.JCR_SQL2);
    Node pluginNode = query.execute().getNodes().nextNode();

But then how do I query beneath that node only?

Answer 1

Since you're placing constraints on three nodes (ie., the bottom not is constrained by version, its parent is constrained by platform, and the platform node's parent is constrained by plugin), you need to do a three-way join:

SELECT * FROM [nt:unstructured] AS plugin
         JOIN [nt:unstructured] AS platform ON ISCHILDNODE(platform,plugin)
         JOIN [nt:unstructured] AS version ON ISCHILDNODE(version,platform)
WHERE plugin.idProp = $pluginId
  AND platform.nameProp = $platform
  AND version.versionProp = $version

For clarity, I used idProp, nameProp and versionProp as the hard-coded property names, but you can build the expression string using Java string concatenation easily enough. I also used bind variables (e.g., $pluginId, $platform and $version) that you could also replace these with Java string concatenation or use JCR's bind-variable mechanism:

Session session = ...
String expression = "SELECT * FROM ...";  // as above
String pluginId = ...
String platform = ...
String version = ...
QueryManager queryMgr = session.getWorkspace().getQueryManager();
Query query = queryMgr.createQuery(expression,Query.JCR_SQL2);
query.bindValue("pluginId",pluginId);
query.bindValue("platform",platform);
query.bindValue("version",version);
QueryResult result = query.execute();

IMO, the JCR-SQL2 expression is fairly readable (maybe less so if you use Java string concatenation), but you may think that the XPath is more readable. But this is the great thing about JCR 2.0's query system, because you can actually choose whichever language best suits the needs of that part of your application.

Because of this, both Jackrabbit and ModeShape (any other JCR 2.0 implementations?) plan on supporting the XPath language, even though it was deprecated by the JCR 2.0 specification.

And finally, ModeShape will parse both the XPath query and the JCR-SQL2 query listed above into the same JCR-JQOM representation, so and processing would be identical.

(Disclaimer: I'm the project lead for ModeShape.)