AEM JCR SQL2 Full Text Search Case Insensitive

One of the key features of AEM is its support for searching and querying content stored in its Java Content Repository (JCR). In this article, we will explore how to perform a case-insensitive full-text search using the JCR_SQL2 query language in AEM and provide a practical example of achieving this through a servlet.


Performing Case-Insensitive Full Text Search

A case-insensitive full-text search is a common requirement when you want to retrieve content regardless of the letter casing used in the search query. In AEM, the JCR_SQL2 query language is used to interact with the JCR repository. To perform a case-insensitive full-text search, you can utilize theLOWER()function to convert both the search query and the content being searched to lowercase. This ensures that the search is not case-sensitive.


Example Query Code

Consider a scenario where you want to search for assets that are descendants of a specific path and meet certain criteria, while also performing a case-insensitive search for specific keywords. Below is an example of a JCR_SQL2 query code block that achieves this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.json.JSONArray;
import org.json.JSONObject;

import javax.servlet.Servlet;
import java.io.IOException;
import javax.jcr.query.Query;
import javax.jcr.Session;
import javax.jcr.Node;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.jcr.query.RowIterator;

@Component(
    service = { Servlet.class },
    property = {
        "sling.servlet.methods=GET",
        "sling.servlet.paths=/bin/mysearchservlet"
    }
)
public class MySearchServlet extends SlingAllMethodsServlet {

    @Override
    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");

        // Retrieve the search query parameter
        String queryParameter = request.getParameter("query");

        // Validate if the query parameter is not null or empty
        if (queryParameter == null || queryParameter.isEmpty()) {
            JSONObject errorResponse = new JSONObject();
            errorResponse.put("error", "Missing query parameter");
            response.getWriter().write(errorResponse.toString());
            return;
        }

        // Retrieve the JCR session
        Session session = request.getResourceResolver().adaptTo(Session.class);

        try {
            // Define your query
            String queryString = "SELECT * FROM [cq:Page] AS page " +
                "WHERE ISDESCENDANTNODE(page, '/content/myproject')" +
                "    AND (" +
                "        LOWER(page.[jcr:content/jcr:title]) LIKE '%" + queryParameter + "%'" +
                "        OR LOWER(page.[jcr:content/cq:template]) LIKE '%" + queryParameter + "%'" +
                "        OR LOWER(page.[jcr:content/jcr:description]) LIKE '%" + queryParameter + "%'" +
                "    )" +
                "ORDER BY [jcr:score] DESC";

            // Get the QueryManager and create the query
            QueryManager queryManager = session.getWorkspace().getQueryManager();
            Query query = queryManager.createQuery(queryString, Query.JCR_SQL2);

            // Execute the query
            QueryResult result = query.execute();

            // Process the results and build JSON response
            JSONArray resultsArray = new JSONArray();
            RowIterator rows = result.getRows();
            while (rows.hasNext()) {
                Node pageNode = rows.nextRow().getNode("page");
                JSONObject pageData = new JSONObject();
                pageData.put("title", pageNode.getProperty("jcr:content/jcr:title").getString());
                pageData.put("template", pageNode.getProperty("jcr:content/cq:template").getString());
                pageData.put("description", pageNode.getProperty("jcr:content/jcr:description").getString());
                resultsArray.put(pageData);
            }

            // Respond with JSON search results
            response.getWriter().write(resultsArray.toString());
        } catch (Exception e) {
            JSONObject errorResponse = new JSONObject();
            errorResponse.put("error", e.getMessage());
            response.getWriter().write(errorResponse.toString());
            return;
        } finally {
            // Close the JCR session
            session.logout();
        }
    }
}

Example Try Catch

When utilizing service users in Java, it’s essential to incorporate try-catch blocks for exception handling. Java introduced enhanced try-catch blocks, which allow you to catch multiple exceptions using a single catch block, in version 7. Before Java 7, try-catch blocks could only handle one exception type per catch block. With the improvements in Java 7, developers gained the ability to efficiently catch and manage various types of exceptions within a structured framework. These enhanced try-catch blocks serve as a powerful means of error management, preventing application crashes, facilitating user-friendly error messages, assisting in the debugging process, and bolstering overall code reliability.

Moreover, when working with service users, it’s important to remember that the JCR session should always be properly closed. Failing to close the session can lead to lingering threads and resource leaks. Incorporating try-with-resources or explicit session closure ensures that resources are released and system resources are utilized optimally. This practice contributes to maintaining the stability and efficiency of your application.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.resource.ResourceResolverFactory;
import org.json.JSONArray;
import org.json.JSONObject;

import javax.servlet.Servlet;
import java.io.IOException;
import java.util.Collections;
import java.util.Map;

@Component(
    service = { Servlet.class },
    property = {
        "sling.servlet.methods=GET",
        "sling.servlet.paths=/bin/mysearchservlet"
    }
)
public class MySearchServlet extends SlingAllMethodsServlet {

    @Reference
    private ResourceResolverFactory resolverFactory;

    @Override
    protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws IOException {
        response.setContentType("application/json");
        response.setCharacterEncoding("UTF-8");

        // Retrieve the search query parameter
        String queryParameter = request.getParameter("query");

        // Validate if the query parameter is not null or empty
        if (queryParameter == null || queryParameter.isEmpty()) {
            JSONObject errorResponse = new JSONObject();
            errorResponse.put("error", "Missing query parameter");
            response.getWriter().write(errorResponse.toString());
            return;
        }

        final Map<String, Object> authInfo = Collections.singletonMap(
                ResourceResolverFactory.SUBSERVICE,
                "sourcedCodeSystemUser");

        try (ResourceResolver resourceResolver = resolverFactory.getServiceResourceResolver(authInfo)) {
            // Define your query
            String queryString = "SELECT * FROM [cq:Page] AS page " +
                "WHERE ISDESCENDANTNODE(page, '/content/myproject')" +
                "    AND (" +
                "        LOWER(page.[jcr:content/jcr:title]) LIKE '%" + queryParameter + "%'" +
                "        OR LOWER(page.[jcr:content/cq:template]) LIKE '%" + queryParameter + "%'" +
                "        OR LOWER(page.[jcr:content/jcr:description]) LIKE '%" + queryParameter + "%'" +
                "    )" +
                "ORDER BY [jcr:score] DESC";

            // Get the QueryManager and create the query
            QueryManager queryManager = resourceResolver.adaptTo(QueryManager.class);
            Query query = queryManager.createQuery(queryString, Query.JCR_SQL2);

            // Execute the query
            QueryResult result = query.execute();

            // Process the results and build JSON response
            JSONArray resultsArray = new JSONArray();
            RowIterator rows = result.getRows();
            while (rows.hasNext()) {
                Node pageNode = rows.nextRow().getNode("page");
                JSONObject pageData = new JSONObject();
                pageData.put("title", pageNode.getProperty("jcr:content/jcr:title").getString());
                pageData.put("template", pageNode.getProperty("jcr:content/cq:template").getString());
                pageData.put("description", pageNode.getProperty("jcr:content/jcr:description").getString());
                resultsArray.put(pageData);
            }

            // Respond with JSON search results
            response.getWriter().write(resultsArray.toString());
        } catch (Exception e) {
            JSONObject errorResponse = new JSONObject();
            errorResponse.put("error", "An error occurred while processing the request.");
            response.getWriter().write(errorResponse.toString());
            return;
        }
    }
}

Curl Example:

1
curl "http://localhost:4502/bin/mysearchservlet?query=my_search_keyword"

Output:

1
2
3
4
5
6
7
8
9
10
11
12
[
    {
        "title": "Page Title 1",
        "template": "/conf/phonak/settings/wcm/templates/mytemplate",
        "description": "This is the description of Page 1"
    },
    {
        "title": "Page Title 2",
        "template": "/conf/phonak/settings/wcm/templates/anothertemplate",
        "description": "Description for Page 2"
    }
]

In this example, theLOWER()function is applied to both the properties being searched and the search queryfulltext This ensures that the search is case-insensitive, allowing you to retrieve relevant assets regardless of letter casing.

Practical Example: Implementing the Search in a Servlet

To practically implement the above query within a servlet in AEM, you would typically perform the following steps:

  • Create a Java servlet that extendsSlingAllMethodsServletor implementsSlingServlet
  • In the servlet’sdoGetordoPostmethod, create an instance of theQueryManagerto execute the JCR_SQL2 query.
  • Use the query string from the example above, replacing placeholders likeproductCFsRootPathandfulltextwith actual values.
  • Process the query result, extract the relevant data, and respond to the client with the search results.

  • Conclusion

    Performing a case-insensitive full-text search using the JCR_SQL2 query language in Adobe Experience Manager allows you to provide a seamless search experience for users regardless of their choice of letter casing. By utilizing theLOWER()function, you can effectively eliminate case sensitivity from your search queries and retrieve the most relevant content. Incorporating this technique into your AEM projects can enhance user experience and ensure that content discovery is efficient and user-friendly.


    Hello, I am an enthusiastic Adobe Community Advisor and a seasoned Lead AEM Developer. I am currently serving as an AEM Technical Lead at MNPDigital.ca, bringing over a decade of extensive web engineering experience and more than eight years of practical AEM experience to the table. My goal is to give back to the AEM Full Stack Development community by sharing my wealth of knowledge with others. You can connect with me on LinkedIn.

    Leave a Reply

    Your email address will not be published. Required fields are marked *


    Back To Top