JCR SQL2 Query comparing name and path with like

Question

I was trying to execute following jcr sql2 query:

        String expression = "SELECT * FROM [nt:base] AS p " +
            "WHERE  NAME(p) like 'opony.txt'";

But I got

javax.jcr.UnsupportedRepositoryOperationException.

Is it any other way to search for nodes that names are like '%example%'?

I was also trying to search for nodes with specified path

        String expression = "SELECT * FROM [nt:base] " +
            "WHERE PATH([nt:base]) like '/a/b'";

But I got

javax.jcr.query.InvalidQueryException: Query:
SELECT * FROM [nt:base] WHERE PATH([(*)nt:base]) like '/a/b'; expected: LENGTH, NAME, LOCALNAME, SCORE, LOWER, UPPER, or CAST

How can I search nodes that paths are like '%example%'?

I am using JCR_SQL2

javax.jcr.query.Query query = queryManager.createQuery(expression, Query.JCR_SQL2);

David Gorsline David Gorsline · Accepted Answer · 2014-08-14T13:24:50

Edit

A couple of points:

If you don't include a wildcard like '%" in your pattern, there's not much difference between LIKE and the ordinary = operator.
PATH() and NAME() are not supported in all implementations of SQL2. They appear to be OK under JBoss.
To search for nodes within a subtree, use the ISDESCENDANTNODE() function.
There is a [jcr:title] attribute that you can test; it's not the same as the node name, but it might serve your purposes.

So, for your first query, try something like

SELECT * FROM [nt:base] AS p WHERE [jcr:title] like '%opony%'
    and ISDESCENDANTNODE('/content/a/b')

The JBoss documentation on querying is fairly good, but be aware that it may not line up exactly with vanilla Jackrabbit. I've also used this cheat sheet for the Magnolia implementation.

JCR SQL2 Query comparing name and path with like

1 Answers