How many times is an AEM Component used in the JCR?

Can we detect and remove unused components from our codebase? Is this AEM component being used at all? How many times is my component used within my running AEM instance?

These are the common questions we have when we are trying to figure out the number of use and the node-paths of the targeted searched component within the Java content repository, known as “JCR”. In this article, you will discover one of the quickest techniques used to retrieve the total number of uses and node-paths of a component within the JCR by using the Query Builder API, query debugger tool.

If you don’t have certain user priveledges within your AEM instance, there’s another way to find out the usage of your component(s), Components Console; however, the Components Console is restricted in a sense where the component search results-list only display pages of a given component and does not provide a total number of how many pages are returned from the search.

1. Component Search by the Query Builder API, Query Builder Debugger Tool
The AEM Query Builder is an API that can be used to search for nodes in JCR. Well defined “Predicates” are typically created from using the Query Builder Debugging Tool, as this tool serves instant search results within the UI.

Step 1: With your running AEM instance, visit http://localhost:4502/libs/cq/search/content/querydebug.html.

Step 2: Paste in the search query below & hit search; view screenshot.

Replace with your component’s path: weretail/components/content/heroimage
Note: sometimes the descendant path must be more nested, so /content/we-retail/gb/en

path=/content/we-retail
1_property=sling:resourceType
1_property.value=weretail/components/content/heroimage

This query searches for all types of nodes with the sling:resourceType equals to the value, weretail/components/content/heroimage, and where the path is descendant from /content/we-retail; to learn more about the Query builder API and queries, click here (Adobe Documentation)

Step 3: Review the results.

Extra:
Find out How many pages are using an AEM template?
The query below will find all cq:Page nodes in the JCR, which cq:template equals to /conf/we-retail/settings/wcm/templates/hero-page; under the content path /content/we-retail. The search results will show you the absolute node page path of whom has the matched property and value set.

type=cq:Page
path=/content/we-retail
1_property=jcr:content/cq:template
1_property.value=/conf/we-retail/settings/wcm/templates/hero-page

Ensuring that the “path” property is set is important because you would not want to excessively traverse through the entire JCR; which will exhaust the AEM environment performance.

Extra:
How to search for all nodes in AEM, JCR, with an existing property?

The query below will search all nodes in the JCR, which “myCustomProperty” exists; under the content path /content/we-retail. The search results will show you the absolute node page path of whom has the matched property and value set.

path=/content/we-retail
property=myCustomProperty
property.operation=exists

Ensuring that the “path” property is set is important because you would not want to excessively traverse through the entire JCR; which will exhaust the AEM environment performance.

2. Component Search by the Components Console
This out of the box Components Console, CC, is available from AEM 6.2+ right under Tools -> General -> Components. The CC displays search results in a form of pages where the component is being used; rather than each node from the example above. This console is accessible to authors so that even they can check for which pages use the searched component.

Step 1: Within your running AEM instance, visit http://localhost:4502/libs/wcm/core/content/sites/components.html.
Step 2: Search for a component. Insert your search criteria with either by “keyword”, “component path”, or “component group”. On every change of the search criteria, you should see results in a list format on the right-hand side of the search left sidebar.
Step 3: Click on the targeted component, and you will be redirected to the details page.
Step 4: From the details page, click on “live usage”, and a results-list of pages will be displayed, where the targeted component can be found.
Step 5: From the results-list, clicking on a link will open the page’s editor.

Components Console – Steps 2 & 3

Components Console – Steps 4 & 5 

How to use AEM JCR SQL2 query strings to query for nodes in Java Content Repository

JCR-SQL2 (Java Content Repository – Structured Query Language 2) is a domain-specific language used to query JCR nodes held in the JCR repository. The syntax for JCR-SQL2 is fairly similar to SQL, so if you have some experience with SQL, this tutorial will be a walk in the park for you.

The best way to learn the power of the JCR-SQL2 language is to try it out on CRX/DE Lite. Without future or do, below will be a lesson on JCR-SQL2. To locate CRX/DE Lite’s query tool, visit http://localhost:4502/crx/de/index.jsp, then select on the tools drop-down, and choose query.

JCR-SQL2 Syntax

1. The JCR-SQL2 SELECT Statement

• The SELECT statement is used to select all JCR nodes that match the JCR node’s primary type.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- nt:base is a primary type that represents every single node in the JCR.
-- returns all nodes in the JCR.
SELECT * FROM [nt:base]

-- returns all [cq:Page] nodes
SELECT * FROM [cq:Page]

-- returns all [dam:Asset] nodes
SELECT * FROM [dam:Asset]

2. The JCR-SQL2 NAME() Statement

• Select nodes with a specific node name.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all [cq:Page] nodes WHERE the node's name is equal to "we-retail"
SELECT * FROM [cq:Page] AS nodes WHERE NAME(nodes) = "we-retail"

-- returns all [dam:Asset] nodes WHERE the node's name is equal to "we-retail"
SELECT * FROM [dam:Asset] AS nodes WHERE NAME(nodes) = "we-retail"

3. The JCR-SQL2 ISDESCENDANTNODE Statement

• Select nodes under a file path.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all nodes WHERE nodes exist under "/content/we-retail"
select * FROM [nt:base] WHERE ISDESCENDANTNODE ([/content/we-retail])

-- returns all [cq:Page] nodes WHERE nodes exist under "/content/we-retail"
SELECT * FROM [cq:Page] WHERE ISDESCENDANTNODE ([/content/we-retail])

-- returns all [dam:Asset] nodes WHERE nodes exist under "/content/dam/we-retail"
SELECT * FROM [dam:Asset] WHERE ISDESCENDANTNODE ([/content/dam/we-retail])

-- returns all [nt:unstructured] nodes WHERE nodes exist under "/content/we-retail"
SELECT * FROM [nt:unstructured] WHERE ISDESCENDANTNODE([/content/we-retail])

4. The JCR-SQL2 CONTAINS Statement

• Select nodes where the properties contain a value.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all nodes WHERE node's property jcr:title CONTAINS "we-retail"
SELECT * FROM [nt:base] AS nodes WHERE CONTAINS(nodes.title, "we-retail")

-- returns all [cq:Page] nodes where node's property jcr:title CONTAINS "we-retail"
SELECT * FROM [cq:Page] AS nodes WHERE CONTAINS(nodes.title, "we-retail")

5. The JCR-SQL2 LIKE Operator

• The LIKE operator is used to search for a specified pattern in node properties.
• There are two wildcards used in conjunction with the LIKE operator: % and _
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title starts with any values and ends with "w"
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "%w"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title starts with "w" and ends with any values
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "w%"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/contentl"
-- AND node's property jcr:title matching "w" in any position
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "%w%"

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title start with any values and have "w" in the second position and ends with any values
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] LIKE "_w%"

6. The JCR-SQL2 IS NOT NULL Property

• Used for validation of property’s value is not null.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title IS NOT NULL
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] IS NOT NULL

-- returns all [cq:PageContent] nodes WHERE nodes exist under "/content"
-- AND node's property jcr:title IS NOT NULL
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content])
AND nodes.[jcr:title] IS NOT NULL

7. The JCR-SQL2 ORDER BY Keyword

• The ORDER BY keyword is used to sort the result-set in ascending or descending order.
• Returned values are sorted by default in ascending order.
• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all nodes WHERE nodes exist under the "/content/we-retail"
-- ORDER BY jcr:created ascending
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
ORDER BY nodes.[jcr:created]

-- returns all nodes WHERE nodes exist under the "/content/we-retail"
-- ORDER BY node's property jcr:created descending
SELECT * FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
ORDER BY nodes.[jcr:created] DESC

8. The JCR-SQL2 CAST() Statement

1. Convert an expression from one data type to another.
2. Cast Types:

• STRING
• BINARY
• DATE
• LONG
• DOUBLE
• DECIMAL
• BOOLEAN
• NAME
• PATH
• REFERENCE
• WEAKREFERENCE
• URI

• Returns in CRX/DE Lite: A list of JCR nodes in the results table with their corresponding paths.
• Returns in OSGI Bundle (Sling API): All the found nodes stored in the Iterator<Resource> object.
• Returns in OSGI Bundle (JCR API): The QueryResult object where you can call getNodes() method to get the NodeIterator object.

-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's navRoot equals to "true"
SELECT * FROM [cq:PageContent] as nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[navRoot] = CAST("true" AS BOOLEAN)

-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's creation date is greater than "April 1st, 2018"
SELECT * FROM [cq:PageContent] AS nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[jcr:created] > CAST("2018-04-01T00:00:00.000Z" AS DATE)

-- returns all [cq:PageContent] nodes WHERE nodes exist under the "/content/we-retail"
-- AND node's maxRating is less than "5.0"
SELECT * FROM [cq:PageContent] as nodes WHERE ISDESCENDANTNODE ([/content/we-retail])
AND nodes.[maxRating] < CAST("5.0" AS DECIMAL)

Note: 

When is the perfect time to use JCR-SQL2?

• Build a images and documents search component, if your AEM site that allows consumers to search for images or documents.
• Build a blog post search component, If your AEM site has a blog section.
• Build a secure resources query API, If you are allowing 3rd party applications to query and find nodes in your AEM site.

There are many other things to utilize JCR-SQL2 to query nodes, but these are just some quick examples.

OSGi Configuration via JCR Nodes

The reasons to configure OSGi services via content nodes is obvious:

• Configurations are deployed with the code
• Configurations can be transferred with content packages
• Configurations can be managed with source control

Defining an OSGi configuration with content nodes is pretty simple:

You create a sling:Folder 'config' and add nodes below it of type sling:OsgiConfig. This node has to have the name of the persistence ID, usually that's the fully qualified class name of the service you want to configure, e.g. com.day.cq.wcm.core.impl.VersionManagerImpl.

Therefore, you can configure the service with an xml file in the folder /apps/yourproject/config with the name com.day.cq.wcm.core.impl.VersionManagerImpl.xml and the following content:

<?xml version="1.0" encoding="UTF-8">
<jcr:root
xmlns:sling="http://sling.apache.org/jcr/sling/1.0"
xmlns:jcr="http://www.jcp.org/jcr/1.0"
jcr:primartyType="sling:OsgiConfig"
versionmanager.maxNumberVersions="{Long}5"
versionmanager.ivPaths="/"
versionmanager.purgePaths="[/etc/feeds,/home/users/public,/home/groups/public]"
versionmanager.createVersionOnActivation="{Boolean}true"
versionmanager.maxAgeDays="{Long}3"
versionmanager.purgingEnabled="{Boolean}true" />

You can add factory configurations (multiple settings for one configuration) by adding a unique identifier to the file name, separated by a hyphen. Setting up the MCM configuration e.g. can be done by providing a file named com.day.cq.mcm.impl.MCMConfiguration-myConfiguration.xml. This will create a binding to the configuration.

Only one open topic remains: You usually have one code base, but multiple configuration according to the various systems: An integration test author system has different configurations compared to a production publish system. The instances can be distinguished by using different runmodes, and the configurations can be runmode specific as well:

Just add the name of the runmode the set of configurations apply to the config-folder, like 'config.author' or 'config.publish'. By this you can maintain all applicable configurations in the version control, deploy the code, activate the packages - and the instance itself picks the proper settings.

JCR Namespaces

All nodes and properties of JCR are stored in different namespaces. The common namespaces are:

• jcr: Basic data storage (part of jcr spec)
• nt: Foundation node types (part of jcr spec)
• rep: Repository internals (part of jcr spec)
• mix: Standard mixin node types (part of jcr spec)
• sling: Added by Sling framework
• cq: Added by the AEM application

Folder Structure of Java Content Repository

You can view the folder structure of JCR from CRXDE Lite. The following table describes the folder structure within the repository:

Folder	Description
/apps	Contains all project code such as components, overlays, client libraries, bundles, i18n translations, and static templates created by an organization
/conf	It contains all configurations for your website. This folder is used to store the dynamic templates and policies for your website.
/content	Contains content created for your website
/etc	It contains resources related to utilities and tools.
/home	Contains AEM users and group information
/libs	It contains the libraries and definitions that belong to the core of AEM. The subfolders in /libs represent the out-of-the-box AEM features.
/oak:index	It contains Jackrabbit Oak index definitions. Each node specifies the details of one index. The standard indexes for the AEM application are visible and help create additional custom indexes.
/system	Is used by Apache Oak only
/tmp	Serves as a temporary working area
/var	It contains files that change and are updated by the system; such as audit logs, statistics, and event-handling. The subfolder /var/classes contain the Java servlets in source and compiled forms that have been generated from the components scripts.

Low-Level APIs Considered Harmful in AEM

For my latest project, we’ve instituted a process where-by every single commit gets code reviewed by at least one other person, and usually by 2 or more people. It’s a process I really enjoy and think has led to exponential gains in code quality. At some point, I’ll probably post more info on the tools we use to facilitate this process and how it works. It’s nothing groundbreaking or revolutionary, but it’s interesting nonetheless. That’s a post for another day.

However, in the course of doing said code reviews, I’ve found myself frequently commenting on my preference for higher-level APIs over their lower-level equivalents. This has drawn some confusion at times, so I thought I would explain that stance, and why I believe it is, in general, the right approach.

When developing for CQ, there is almost always more than one way to solve a given problem, and oftentimes 3 or 4 ways. Partly, this is due to the fact that the platform is built on top of a cascading series of frameworks, each with their own API operating at a given level of abstraction.

Just as a quick example, If I need to manipulate a page’s content I could:

• use the Page Manager to retrieve the Page, then get the content Resource from there
• use the Sling Resource Resolver, to grab the content Resource I need directly
• use a JCR Session to retrieve the Node I need to manipulate

Quite obviously, these layers of abstraction leak to some extent. Even in the simple example above, the first case using the CQ Page Manager API directly involves the Sling Resource API to make any actual updates. Given these leaks, you need to understand those abstractions, and what is happening at each layer of abstraction to effectively work within the platform, so why prefer the higher level approach?

To answer that question, let’s compare 2 code snippets, which accomplish the relatively simple task described above. The first one, using the low lever JCR API and the second one using the higher level CQ and Sling APIs.

private void updatePageLastModified(String pagePath, Session jcrSession) {
    Node pageNode = jcrSession.getNode(pagePath);
    Node contentNode = pageNode.getNode("jcr:content"); 

    if(!contentNode.hasProperty("customUpdateDate")) {
        contentNode.setProperty("customUpdateDate", java.util.Calendar.getInstance());
        jcrSession.save();
    } else {
        Property dateProp = contentNode.getProperty("customUpdateDate");
        java.util.Calendar curDate = dateProp.getDate();
        log.debug("update date already set to {}", curDate);
    }

}

private void updatePageLastModified(String pagePath, ResourceResolver resourceResolver) {
    PageManager pm = resourceResolver.adaptTo(PageManager.class);

    Page myPage = pm.getPage(pagePath);
    Resource cntRes = myPage.getContentResource();

    ModifiableValueMap mvm = cntRes.adaptTo(ModifiableValueMap.class);
    java.util.Calendar curDate = mvm.get("customUpdateDate", java.util.Calendar.class);
    if(curDate!=null) {
        mvm.put("customUpdateDate", java.util.Calendar.getInstance()); 
        resourceResolver.commit();
    } else {
        log.debug("update date already set to {}", curDate);
    } 

}

Both sets of codes are about the same length (in fact, in this instance the low-level approach is slightly fewer lines of code, but that’s due to having to initialize the PageManager from the ResourceResolver). But take a close look at how you check for the existence of the custom date property. Using the Sling ValueMap API, just get the property and if it’s not there, deal with the null case. Using the JCR API, you need an explicit check for the property’s existence before you can get its value and use it. If you call getProperty and the property wasn’t there, an exception would be raised. By using the higher-level API, there is no need to worry about handling this exception case. Basically, the abstraction makes it easier to accomplish the task at hand, which is the whole purpose of abstraction in general. You can focus on structuring the code in a way to make it easy to test, on making sure the algorithm does what it needs to do in a consistent way and handles any edge-cases. Put another way, you can focus your time and effort on your job without worrying about the lowest level semantics of the repository persistence layer.

Additionally, compare lines 3 and 5, where you get the content node/resource from the page. The difference here is small, but in my mind important. By hard coding the “jcr:content” node name, the code is locked into a given representation format for that data. In this simple case, you could work around that by using a constant and move on with life. But especially in more complex situations, having code tied to given storage representation makes the code less flexible. It means that if the format ever changes, there will be a huge and expensive effort to refactor and make things with the new format.

Having just said all of that, there are obviously cases where using the low-level JCR API is appropriate. It’s important to have all the tools in your toolbox, and know when and how to use each of them. In general though, given the choice of 2 tools to accomplish the same task, I’m going to use the one that’s easiest to use and creates the least amount of mess.

Storing data in AEM JCR

Store data in remote AEM JCR repository.
We can store data in the AEM's data repository i.e. the JCR repository very easily as shown below in the code sample. The only thing we have a lookout for is the username and the password we are using to access the repository.
Sometimes we do not have permission for writing to the JCR in those cases this will not work.
Here I am using the "Administrator" login for writing data into the AEM JCR.

Please make necessary changes to the port and the host URL as required.

This creates a node in AEM under the "content" folder as shown here.



The properties that will be set are shown below.


Step1: Download the Jack Rabbit Jar File form 
http://www.apache.org/dyn/closer.cgi/jackrabbit/2.8.1/jackrabbit-standalone-2.8.1.jar and place the Jack Rabbit jar in the build path of your project.

Step 2: Create and execute a class as shown below.

File: StoreData.java
package net.codermag.jcr.test;

import javax.jcr.Node;
import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;

import org.apache.jackrabbit.commons.JcrUtils;
import org.apache.jackrabbit.core.TransientRepository;

public class StoreData {

 public static void main(String[] args) throws Exception {
  
  Repository repository = new TransientRepository();
  repository = JcrUtils.getRepository("http://localhost:4502/crx/server");

  // Create a Session
  Session session = repository.login(new SimpleCredentials("admin","admin".toCharArray()));

  // Create a node that represents the root node. For this, you need root permission.
  Node root = session.getRootNode();

  // Getting the content node (This can be any node like etc/bin....)
  Node contentNode = root.getNode("content");

  if (contentNode.hasNode("codermagnet")) {

   Node coderMagnetNode = contentNode.getNode("codermagnet");
   
   //Setting the primary type. This can be any of the primary properties that AEM supports
   coderMagnetNode.setPrimaryType("nt:unstructured");

   coderMagnetNode.setProperty("name", "John Doe");
   coderMagnetNode.setProperty("email", "John@mail.com");
   coderMagnetNode.setProperty("isEmployed", true);
   coderMagnetNode.setProperty("roles", new String[] { "Manager","Admin", "Developer" });

   System.out.println("Data has been set..!");

  } else {
   System.out.println("Node Not Found..!");
  }
  session.save();
 }
}

Output:
Data has been set..!

JCR SQL 2 FULL Tutorial: CHEAT SHEET

JCR SQL2 Tutorial and Examples
Java content repository- Structured Query Language 2 better known as JCR-SQL2 is a new specification that helps us to query data stored as properties under Nodes. It is a valuable tool for AEM developers to have 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 a 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 an exact match in the 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 that match 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 the node where the given properties are not null.

So if the values of the property are 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 show some JCR SQL2 queries using the CAST property.
The CAST property can be used for the following data types.

BINARY DOUBLE REFERENCE
BOOLEAN LONG STRING
DATE NAME URI
DECIMAL PATH WEAKREFERENCE

How to query JCR data in Adobe AEM

Different handy techniques to write JCR query in AEM/CQ:
For a Detailed Study on JCR Queries Please see this article.
JCR SQL 2 FULL Tutorial: CHEAT SHEET

Java content repository, better known as JCR is a modern approach to our database solutions.
While working on JCR I found many rather simple techniques to write JCR queries from our JAVA application.
Some of them have been described below to help fellow developers.

For this example, we will try to fetch all the "employee" nodes under the "codermagnet" parent node.


Download the Jack Rabbit Jar File form
http://www.apache.org/dyn/closer.cgi/jackrabbit/2.8.1/jackrabbit-standalone-2.8.1.jar
and place the Jack Rabbit jar in the build path of your project.

OPTION 1: Querying JCR using QueryManager API
Create a new class file as shown below.

File: JCRQueryExample.java
package net.codermag.jcr.test;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;

import org.apache.jackrabbit.commons.JcrUtils;
import org.apache.jackrabbit.core.TransientRepository;

public class JCRQueryExample { 
 public static void main(String[] args) throws Exception {

  // Create a Session
  Repository repository = new TransientRepository();
  repository = JcrUtils.getRepository("http://localhost:4502/crx/server");
  Session session = repository.login(new SimpleCredentials("admin","admin".toCharArray()));

  //Building and Executing the Query
  QueryManager queryManager = session.getWorkspace().getQueryManager();
  String sqlStatement = "SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/codermagnet/employees])";
  Query query = queryManager.createQuery(sqlStatement, "JCR-SQL2");
  QueryResult result = query.execute();

  //Doing Something with the nodes returned.. Printing the paths
  NodeIterator iterator = result.getNodes();
  while (iterator.hasNext()) {
   System.out.println(((Node) iterator.next()).getPath());
  }
 }
}

Output:
/content/codermagnet/employees/kuper
/content/codermagnet/employees/john
/content/codermagnet/employees/harry
/content/codermagnet/employees/smith

OPTION 2: Quering JCR using ResourceResolver.findResources() method.
Another very handy and short way of doing this is illustrated below. This is my favorite as its a one-liner query and is cute, clean, and fast. One point that we need to remember while doing this is that for this second option we need the implicit "ResourceResolver" object. So this is more appropriate to be used as some sort of sling service inside our JCR application.

File: JCRQueryExample2.java
package net.codermag.jcr.test;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import javax.jcr.Node;
import javax.jcr.query.Query;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;

public class JCRQueryExample2 {

 public List<Node> getNodes(SlingHttpServletRequest request)
   throws Exception {

  List<Node> nodes = new ArrayList<Node>();
  ResourceResolver resolver = request.getResourceResolver();
  
  // Executing query in One Line....!!!!
  Iterator<Resource> nodeIter = resolver
    .findResources("SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/codermagnet/employees])",
      Query.JCR_SQL2);
   
  // Converting the Resource object to Node and then storing as arraylist
  while (nodeIter.hasNext()) {
   nodes.add(nodeIter.next().adaptTo(Node.class));
  }
  return nodes;
 }
}

Option 3: Quering JCR using QueryBuilder API
File:JCRQueryExample3.java

Note: For testing the below XPATH query beforehand you can use the QueryBuilder debugger console at this URL
http://localhost:4502/libs/cq/search/content/querydebug.html

package com.realogy.sir.core.servlets;

import java.util.HashMap;
import java.util.Map;
import javax.jcr.Session;
import org.apache.sling.api.SlingHttpServletRequest;
import com.day.cq.search.PredicateGroup;
import com.day.cq.search.Query;
import com.day.cq.search.QueryBuilder;
import com.day.cq.search.result.Hit;
import com.day.cq.search.result.SearchResult;

public class JCRQueryExample3 {

 public void getNodes(SlingHttpServletRequest request)throws Exception {

  //Form the map and fill all the required parameters in the map
  Map<String,String> map = new HashMap<String,String>();
  map.put("path", "/content/codermagnet/employees");
  map.put("type", "nt:base");
  map.put("orderby","@name");
  
  //Quering JCR using com.day.cq.search.QueryBuilder API
  PredicateGroup predicateGroup = PredicateGroup.create(map);
  QueryBuilder builder = request.getResourceResolver().adaptTo(QueryBuilder.class);
  Query query = builder.createQuery(predicateGroup, request.getResourceResolver().adaptTo(Session.class));
  SearchResult result = query.getResult();

  //Do something with the results
  for (Hit hit : result.getHits()) {
   String path = hit.getPath();
   System.out.println(path);
  } 
 }
}

Output:
/content/codermagnet/employees/harry
/content/codermagnet/employees/john
/content/codermagnet/employees/kuper
/content/codermagnet/employees/smith

You can use Option 2 and Option 3 when writing servlets or inside scriptlet tags in AEM JSP pages where you have access or can derive the "resourceResolver" implicit object. In the above option, I have derived the "resourceResolver" object from the org.apache.sling.api.SlingHttpServletRequest that is passed to the method.

Connect to the JCR repository using a simple Java main method.

When using Adobe Experience Manager (CQ) as our content management tool we sometimes need to connect to an AEM JCR repository from our integrated development environments (IDE) like Eclipse or NetBeans.

The following code shows a simple executable Java program for connecting to any AEM JCR Repository using a main() method. This can be used to connect to any remote or local JCR repository.

The Steps to do so are as follows:

Step1:
Download the Jack Rabbit Jar File form
http://www.apache.org/dyn/closer.cgi/jackrabbit/2.8.1/jackrabbit-standalone-2.8.1.jar and place the Jack Rabbit jar in the build path of your project.


Step 2:
File: ConnectJCR.java

package net.codermag.jcr.test;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.Repository;
import javax.jcr.Session;
import javax.jcr.SimpleCredentials;

import org.apache.jackrabbit.commons.JcrUtils;
import org.apache.jackrabbit.core.TransientRepository;

public class ConnectJCR {

 public static void main(String[] args) throws Exception {
  Repository repository = new TransientRepository();
  repository = JcrUtils.getRepository("http://localhost:4502/crx/server");

  // Create a Session
  Session session = repository.login(new SimpleCredentials("admin", "admin".toCharArray()));
  //If you get javax.jcr.lock.LockException please use the line below
  //instead of the above one
  //Session session = repository.login( new SimpleCredentials("admin", "admin".toCharArray()),"crx.default");
  
  // Create a node that represents the root node
  Node root = session.getRootNode();
  Node content = root.getNode("content");
  Node subContent = content.getNode("dam");

  //Getting the iterator over the nodes
  NodeIterator childNodeIterator = subContent.getNodes();

  //Iterating over the nodes and printing their names
  while(childNodeIterator.hasNext()){
   Node childNode=(Node) childNodeIterator.next();
   System.out.println(childNode.getName());
   
  }
 }
}

javax.jcr.lock.LockException: Precondition Failed
If you are getting the javax.jcr.lock.LockException with the above program then please follow this link
http://www.codermag.net/2016/10/how-to-fix-javax-jcr-lock-lockexception-precondition-failed.html

Creating custom JCR node type

Sometimes it is required for application to have a custom schema with application-specific namespace, node types, and properties. Although David's model rule #1 is "Data First, Structure Later. Maybe", sometimes "maybe" becomes "must". 

So now I can create a custom node type?
First of all, read carefully official JCR documentation: Node Types and Examples. And now let's implement it.

1. Create a node type definition using a CND descriptor. 
For simplicity, we will put it into existing CQ namespace. (If you want to customize namespace, just define it alongside existing namespaces in CND file).

<cq  = 'http://www.day.com/jcr/cq/1.0'>
<sling = 'http://sling.apache.org/jcr/sling/1.0'>

//---- Custom Node Type ----

[cq:CustomNode] > nt:hierarchyNode, mix:title, mix:modified, mix:versionable
    orderable
    - * (undefined) multiple
    - * (undefined)
    + * (nt:base) = cq:CustomNode version

Here we define node type, which is hierarchical, contains jcr:created, jcr:modified properties and which supports versioning (mix:versionable). If your project is built using maven, put this file to the resources folder of your OSGI bundle. I use CQ-style and put it under CQ-INF/nodetypes folder.

2. Create an OSGi listener that registers our node type during bundle activation.
Now you need to register a newly created definitions. For that, you should invoke JCR API from the links above. In order to simplify things, we will register our node type during bundle activation, so we need to create an OSGI component.

package com.yvv.customnode.bundle;

import org.apache.felix.scr.annotations.Component;
import org.apache.felix.scr.annotations.Reference;
import org.apache.jackrabbit.api.JackrabbitNodeTypeManager;
import org.osgi.service.component.ComponentContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.jcr.Session;
import java.io.InputStream;

@Component
public class CustomNodeTypeListener {

    private final Logger LOGGER = LoggerFactory.getLogger(CustomNodeTypeListener.class);
    public static final String NODE_TYPE_NAME = "cq:CustomNode";

    @Reference
    private org.apache.sling.jcr.api.SlingRepository repository;

    private Session session;

    protected void activate(ComponentContext context) throws Exception {
        session = repository.loginAdministrative(null);
        registerCustomNodeTypes(session);
    }

    protected void deactivate(ComponentContext componentContext) {
        if (session != null) {
            session.logout();
            session = null;
        }
    }

    public void registerCustomNodeTypes(Session session)
            throws Exception {
        JackrabbitNodeTypeManager manager = (JackrabbitNodeTypeManager) session.getWorkspace().getNodeTypeManager();
        if (manager.hasNodeType(NODE_TYPE_NAME)) {
            manager.unregisterNodeType(NODE_TYPE_NAME);
        }
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("CQ-INF/nodetypes/cq-customnode.cnd");
        manager.registerNodeTypes(is, JackrabbitNodeTypeManager.TEXT_X_JCR_CND);
    }
}

3. Verify a newly created node type.
Browse to http://localhost:4502/crx/explorer/nodetypes/index.jsp and under the tab "All registered node types" find cq:CustomNode, open it to view details.

4. Create nodes using your custom type.
You can do it using i.e. curl command:

curl -D -u admin:admin -F"jcr:primaryType=cq:CustomNode" -F"title=some title" http://localhost:4502/content/customnode

Creating AEM JCR valid & Unique name programmatically

The objective of this post is to elaborate on how AEM Page Manager API is being used to create a unique JCR Node or Page name. And if there is a requirement for you to create the same page name programatically then what API you should be using?

Scenario
Let’s consider a scenario where article pages are being created automatically in AEM content hierarchy and some of the promotional content under those pages. And, there is a product detail page which renders promotional content based Product data.

The problem occurs when you to have determine promotional content programatically with some information at the product level. To match appropriate promotional content & render onto product detail pages isn’t straight forward.

Solution
In order to solve above problem, all you have to do is to resolve promotional content page as resource. And that is possible if Your code is able to find content page in JCR Contrent hierarchy.

Let’s consider this example. Title of promotional/Article content: “This is a dummy content article page”. The Same page created in AEM would have hierarchy like this.

/content/<your-website>/en/articles/this-is-a-dummy-content-article-page

Below example shows how you can create valid JCR Resource/Node/Page Name out of given Title of the article.

#Simple Example Class

Public class UninqueValidPageName{

 public static String getValidPageName(final String articleTitle){

     return JcrUtil.createValidName(StringUtils.trim(articleTitle), JcrUtil.HYPHEN_LABEL_CHAR_MAPPING);

}

}

Final Thoughts
This solution seems very simple however it has significant important of solving problem.

JCR Query Cheat Sheet

This is a collection of mini-recipes for doing JCR queries. Please add your own!

	SQL (deprecated in JCR 2.0)	XPath (deprecated in JCR 2.0)	SQL2
All pages	select * from mgnl:content	//element(*, mgnl:content)	select * from [mgnl:page]
Pages with "News" in the title	select * from mgnl:content where title like '%News%'	//element(*, mgnl:content)[jcr:like(@title, '%News%')]	select * from [mgnl:page] where title like '%News%'
Pages where the title exactly matches "News" (case sensitive)	select * from mgnl:content where title like 'News'	//element(*, mgnl:content)[@title = 'News']	select * from [mgnl:page] where title like 'News'
STK pages that have a header image	select * from mgnl:content where image is not null	//element(*, mgnl:content)[@image]	select * from [mgnl:page] where image is not null
Instances of a "Teaser" paragraph	select * from nt:base where mgnl:template = 'stkTeaser'	//*[@mgnl:template = 'stkTeaser']	select * from [nt:base] where [mgnl:template] = 'standard-templating-kit:components/teasers/stkTeaser'
Available paragraph types	select * from nt:base where jcr:path like '/modules/%/paragraphs/%' and type is not null	/jcr:root/modules[1]///paragraphs[1]//[@type]
User with email 'eric@example.com'	select * from mgnl:user where email = 'eric@example.com'	//element(*, mgnl:user)[@email = 'eric@example.com']	select * from [mgnl:user] where email = 'eric@example.com'
Pages that have the word "component"	SELECT * from nt:base WHERE jcr:path like '/ftl-sample-site%' AND contains(*, 'component') AND (jcr:primaryType = 'mgnl:page' OR jcr:primaryType = 'mgnl:area' OR jcr:primaryType = 'mgnl:component') order by jcr:path		SELECT * from [nt:base] AS t WHERE ISDESCENDANTNODE('/ftl-sample-site') AND contains(t.*, 'component')
Template folders in module configuration	select * from mgnl:content where jcr:path like '/modules/%/templates'		select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'templates'
Modules that provide commands	select * from nt:base where jcr:path like '/modules/%/commands'		select * from [mgnl:content] as t where ISDESCENDANTNODE('/modules') and name(t) = 'commands'
All pages with a specific template ordered by title			SELECT p.* FROM [nt:base] AS p WHERE [mgnl:template] = 'xxx:pages/jobs' order by p.[title] asc
Pages under given path with given template	select * from nt:base where jcr:path like '/demo-project/%' AND mgnl:template = 'standard-templating-kit:stkNews'		SELECT parent.* FROM [mgnl:page] AS parent INNER JOIN [mgnl:metaData] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/demo-project') AND child.[mgnl:template] = 'standard-templating-kit:stkNews' ** When using this query, one need to get results via getRows() instead of getNodes() since queries w/ joins can eventually return multiple different node types.
Pages under given path with given template and category ordered by date		/jcr:root/demo-project//element(*, mgnl:metaData)[@mgnl:template = 'standard-templating-kit:pages/stkArticle']/..[@categories = 'ab9437db-ab2c-4df5-bb41-87e55409e8e1'] order by @date
Search a Node with a certain UUID	select * from nt:base where jcr:uuid = '7fd401be-cada-4634-93fa-88069f46297b'		SELECT * FROM [nt:base] WHERE [jcr:uuid] = '7fd401be-cada-4634-93fa-88069f46297b'
Search case insensitive	select * from nt:base where lower(name) like 'name_in_lowercase'		select * from [nt:base] where lower(name) like 'name_in_lowercase'
Search demo-project pages created in given time frame			select * from [mgnl:page] where ISDESCENDANTNODE('/demo-project/') and [jcr:created] > cast('2010-01-01T00:00:00.000+02:00' as date) and [jcr:created] < cast('2014-09-30T23:59:59.000+02:00' as date)
Pages in 'demo-project' which using a specific template ('stkSection' for example) and has the content just been modified by 'eric'		/jcr:root/demo-project/*[mgnl:template='standard-templating-kit:pages/stkSection']/*/content[mgnl:lastModifiedBy='eric']/../..
Get all nodes which have a property 'date' which is not empty and this date starts at least 1 second after current midnight. Useful for events.			SELECT p.* FROM [nt:base] AS p WHERE ISDESCENDANTNODE('/') AND (p.[date] <> '' AND p.[date] > CAST('2015-11-30T00:00:01.000Z' AS DATE)) order by p.[date] asc

Source: https://wiki.magnolia-cms.com/display/WIKI/JCR+Query+Cheat+Sheet