0

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?

Kevin Wong
  • 14,656
  • 11
  • 42
  • 52

1 Answers1

4

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

Randall Hauch
  • 7,069
  • 31
  • 28
  • Great answer, thanks. I do indeed find the XPath syntax more intuitive and readable. I wonder why they deprecated it. – Kevin Wong Jan 17 '12 at 20:13
  • I'm not exactly sure why it was deprecated, since I was not on the JSR-283 effort. Clearly JCR-SQL2 and JCR-JQOM are far superior, more powerful, and more expressive languages. But perhaps they were not sure that all JCR XPath queries could be translated into JCR-SQL2 or JCR-JQOM. – Randall Hauch Jan 17 '12 at 23:28
  • Should those statements be "INNER JOIN"? (Based on the grammar I found here http://www.h2database.com/jcr/grammar.html#join.) – Kevin Wong Jan 26 '12 at 20:11
  • According to the [Section 6.7.5 of the JCR 2.0 specification](http://www.day.com/specs/jcr/2.0/6_Query.html#6.7.5%20Join), the grammar rule is actually `Join ::= left [JoinType] 'JOIN' right 'ON' JoinCondition`, where the `JoinType` (e.g., "INNER", "LEFT OUTER" or "RIGHT OUTER") is optional and defaults to "INNER". So I think the railroad diagram at http://www.h2database.com/jcr/grammar.html#join is incorrect. – Randall Hauch Jan 28 '12 at 15:03