Saturday, 16 April 2016

JCR SQL 2 FULL Tutorial: CHEAT SHEET

JCR SQL 2 FULL Tutorial

JCR SQL2 Tutorial and Examples


Java content repository- Structured Query Language 2 better known as JCR-SQL2 is a new
specification which helps us to query data stored as properties under Nodes.It is a valuable tool for AEM developers to have a knowledge of this.

This article will explain various JCR queries in detail.
A basic understanding of SQL is a plus but now a mandate. Read on folks....


The 'SELECT' statement.

The first newbie command that we all try our hands on first. :)


i. Select Everything

SELECT * FROM [nt:base]
Selects every node from the content repository. This is because nt:base is the base type for all JCR nodes.




ii. Select Nodes of given type.

SELECT * FROM [my:type]
SELECT * FROM [nt:file]
SELECT * FROM [cq:Page]

The 1st query collects all nodes of jcr:PrimaryType = my:Type
The 2nd collects nodes where jcr:PrimaryType = nt:File, this is generally required to collect all nodes of nt:File type.
The 3rd collects all nodes of jcr:PrimaryType = cq:Page,is commonly used in Adobe Experience Manager to collect Pages.




iii. Find Nodes by Name

SELECT * FROM [nt:base] As nodes WHERE NAME(nodes) = 'jcr:content'
This will select all nodes of type nt:base having the name "jcr:content".




iv. Finding Nodes Under a path.

SELECT * FROM [nt:unstructured] As node WHERE ISDESCENDANTNODE ([/content/myProject])
This will return all nodes of type nt:unstructured under the given path /content/myProject




v. Finding Nodes Under MULTIPLE paths.

SELECT * FROM [nt:unstructured] AS s WHERE (ISDESCENDANTNODE('/content/myProject/Path1') 
or 
ISDESCENDANTNODE('/content/myProject/Path2'))
This will return all nodes of type nt:unstructured under 2 given paths /content/myProject/Path1 and /content/myProject/Path2




vii. Find Nodes based on property value.

SELECT * FROM [nt:base] AS nodes WHERE CONTAINS(nodes.title, 'news')
This will find all the nodes under the repository whose title contains the word "news". This can also be done using the LIKE clause but is not very efficient and hence slow.
SELECT * FROM [nt:base] AS nodes WHERE nodes.title LIKE 'news'




viii. Find Nodes based on MULTIPLE property values.

SELECT * FROM [nt:unstructured] WHERE title = 'fruit' AND color = 'red'
This will find all the nt:unstructured nodes under the repository whose title contains the word "fruit"
and has the color property as "red". You can also use the CONTAINS and LIKE clause here if you want to, just like the previous example.




The JCR 'LIKE' Clause

The LIKE clause is generally used for matching the attribute values.



i. Finding Nodes with exact match in title.

SELECT * from [cq:Page] where title LIKE 'News'
This will return all the nodes of type cq:Page where there is a title attribute EXACTLY Matching the word "News".




ii.Finding Nodes whose title contains a word/phrase.

SELECT * from [cq:Page] where title LIKE '%News%'
This will return all the nodes of type cq:Page where there is a title attribute WHICH CONTAINS the word "News".




iii. Finding files which matches the given file extension.

SELECT * FROM [nt:file] WHERE NAME() LIKE '%.txt'
This is used to find all the nodes of type nt:file whose name has a (.txt) file extension (text files).

SELECT [jcr:primaryType], [jcr:created], [jcr:createdBy], [jcr:path] FROM [nt:file]
WHERE LOCALNAME() LIKE '%.png'
Similarly the above query selects some attributes of the nt:file node for all files having the (.png) extension.




iv. Find Nodes under a given path.

SELECT * FROM [my:type] WHERE PATH([my:type]) LIKE '/content/myProject'
This will return all nodes of type "my:Type" under the path /content/myProject/.




v. Find Nodes under a given path and its subtree.

SELECT * FROM [my:type] WHERE PATH([my:type]) LIKE '/content/myProject/%'
This will return all nodes of type "my:Type" under the path /content/myProject/ and all its subtrees /content/myProject/*




The Jcr 'IS NOT NULL' Property

The 'IS NOT NULL' property is used to check for NON NULL values.

SELECT * FROM [my:type]WHERE [my:type].prop1 IS NOT NULL

SELECT * from [cq:Page] where image IS NOT NULL

In both the above queries the result is a set of node where the given properties is not null.
So if the values of the property is NULL in any case then the results will not be returned by the query. This means that the query will overlook any node having no value for the given property.





The JCR 'Order By' Clause

SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE ([/content/myProject]) 
ORDER BY [jcr:lastModified]
This will return all the nodes of type nt:unstructured in the given path /content/myProject ordered by the last modified time in increasing order.

Decreasing Order:
SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE ([/content/myProject]) 
ORDER BY [jcr:lastModified] DESC




JCR Query using Dates

SELECT * FROM cq:PageContent AS s
WHERE s.[jcr:created] >= CAST('2015-01-01T00:00:00.000Z' AS DATE)
AND s.[jcr:created] < CAST('2016-01-01T00:00:00.000Z' AS DATE)
This query will find all the nodes of type cq:PageContent which has been created between the given two dates.
This shows you how to query using dates in JCR SQL2.



JCR SQL2 'CAST' Property Values


BOOLEAN:


SELECT * FROM [my:type] WHERE prop1 = CAST('true' AS BOOLEAN)

SELECT * FROM [nt:unstructured] WHERE hideInNav = CAST('true' AS BOOLEAN)


LONG:

SELECT * FROM [my:type] WHERE PATH([my:type])> LIKE '/content/%' AND DEPTH([my:type]) = CAST(2 AS LONG)


DATE:

SELECT * FROM cq:PageContent AS s
WHERE s.[jcr:created] >= CAST('2015-01-01T00:00:00.000Z' AS DATE)
AND s.[jcr:created] < CAST('2016-01-01T00:00:00.000Z' AS DATE)
The examples above shows some JCR SQL2 queries using CAST property.
The CAST property can be used for the following datat types.
BINARYDOUBLEREFERENCE
BOOLEANLONGSTRING
DATENAMEURI
DECIMALPATHWEAKREFERENCE



No comments:

Post a Comment

Coder Magnet
CoderMagnet is full of resources from our daily development activities. It has solutions for common problematic scenarios in technologies like Java 8, AEM, JCR and also occasionally gives you tips on Blogger as well.