JCR-SQL2 is a query language defined by the Java Content Repository 2.0 (JSR-283) specification. It is similar to a subset of SQL but also provides JCR-specific elements. JCR-SQL2 is an improvement over JCR-SQL, the original language defined in JCR 1.0 that was far less like SQL.
JCR-SQL2 queries are simple string statements that include common relational operators:
- The
SELECTclause identifies which JCR properties are to be returned in the tabular result set, and may include aliases - The
FROMclause identifies the JCR node types (conceptually similar to tables or view) or joins that are to be sources of nodes - The optional
WHEREclause lists any predicates that must be satisfied before nodes can be included in the results - The optional
ORDER BYclause the specifies how the results are to be ordered
An example JCR-SQL2 query is this query:
SELECT file.*,content.* FROM [nt:file] AS file
JOIN [nt:resource] AS content ON ISCHILDNODE(content,file)
WHERE LOCALNAME(file,'*.txt') AND ISDESCENDANTNODE(file,[/a/b/c])
This query finds all nodes of type nt:file with a name that matches the *.txt pattern that exist somewhere under the /a/b/c node, and the nt:file node's child of type nt:resource node. The result set will minimally include a column for each of the properties defined on nt:file (e.g., "jcr:created" and "jcr:createdBy") and nt:resource (e.g., "jcr:mimeType", "jcr:encoding", "jcr:lastModified", "jcr:lastModifiedBy"); implementations are allowed to return additional columns.
Some JCR implementations (such as Jackrabbit) support only the required grammar, while other JCR implementations (such as ModeShape) support an extended grammar with additional functionality.
The JCR-SQL2 language is surprisingly powerful and flexible, and provide an easy way to find repository content independent of where that content exists in the repository's hierarchical structure of nodes.
