Level 4

Solved

Query JCR_SQL2

Forum|Forum|2 years ago
July 18, 2023
3 replies
1663 views

HI,

Within a Model associated with a Component a JCR_SQL2 Query is performed with the method resolver.findResources(query, Query.JCR_SQL2);

Is there a way to cache the result of the query and not repeat it every time?

Thank you

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

Best answer by yuriy_shestakov

The provided solution is not good - the cached resource will contain the resource resolver that is used for accessing the related nodes, so it won't be closed after the query is finished. Next time when those resources are accessed, they will be returned using credentials from another request, so possible there might be a security issue. After the resolver is closed automatically (I don't know exactly - in several minutes, or hours, maybe after a day), the code will return error so exception will be thrown which will break the whole page where this code is executed. It will be a very vague bug that will crash your prod at unpredictable times and give you a huge headache.
This way you are able to store resources only in a model that will be re-created for the next web request, so it would work only if you use the same model in several places on a page and then purge them when the current SlingHttpServletRequest is destroyed.

However, the provided approach can be fixed if you adapt your resources to necessary sling models and cache those models, without storing a resource, the resource resolver, or other critical things in their fields. Also, you should use WeakHashMap / weak references to store such cached objects.

In almost all cases the default dispatcher cache + out-of-the-box sling models adapter factory cache are enough for good publisher performance. In other cases where it is not enough the provided solution (or the ehcache solution) is ok while you store adapted models, but you should take care of invalidating/updating the cache once your resources are updated (new pages are published, nodes' properties are updated as a result of a workflow execution, etc.).

TarunKumar

Community Advisor

Hi @robertol6836527 ,

Are you getting the result in form of JSON, you cache the json file in AEM dispatcher or you can cache the entire page where the component is being used.

aanchal-sikka

Community Advisor

Hello @robertol6836527

You might have to create your own cache to access these results and periodically flush them. Like: Map(Example added below), Ehcache etc.

The implementation depends on how extensively you use this feature.

import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;

// Define a cache object, such as a Map, to store the query results
private Map<String, Iterator<Resource>> queryCache = new HashMap<>();

public Iterator<Resource> getCachedResources(ResourceResolver resolver, String query) {
    // Check if the query results are already cached
    if (queryCache.containsKey(query)) {
        return queryCache.get(query);
    } else {
        // Execute the query and cache the results if not already cached
        Iterator<Resource> resources = resolver.findResources(query, Query.JCR_SQL2);
        queryCache.put(query, resources);
        return resources;
    }
}

Aanchal Sikka

yuriy_shestakovAccepted solution

Level 3

The provided solution is not good - the cached resource will contain the resource resolver that is used for accessing the related nodes, so it won't be closed after the query is finished. Next time when those resources are accessed, they will be returned using credentials from another request, so possible there might be a security issue. After the resolver is closed automatically (I don't know exactly - in several minutes, or hours, maybe after a day), the code will return error so exception will be thrown which will break the whole page where this code is executed. It will be a very vague bug that will crash your prod at unpredictable times and give you a huge headache.
This way you are able to store resources only in a model that will be re-created for the next web request, so it would work only if you use the same model in several places on a page and then purge them when the current SlingHttpServletRequest is destroyed.

However, the provided approach can be fixed if you adapt your resources to necessary sling models and cache those models, without storing a resource, the resource resolver, or other critical things in their fields. Also, you should use WeakHashMap / weak references to store such cached objects.

In almost all cases the default dispatcher cache + out-of-the-box sling models adapter factory cache are enough for good publisher performance. In other cases where it is not enough the provided solution (or the ehcache solution) is ok while you store adapted models, but you should take care of invalidating/updating the cache once your resources are updated (new pages are published, nodes' properties are updated as a result of a workflow execution, etc.).

aanchal-sikka

Community Advisor

Thanks @yuriy_shestakov for the details on the issue suggested in other answer and how it can be mitigated.

Really appreciated !

Aanchal Sikka

Nishant-Singh

Adobe Employee

if your component is not dynamic in nature the you can render the component and that can be cached on dispatcher / CDN.

if your component is Dynamic then you can implement Client side implementation by using Ajax to hit the model with some selector and json extension.

with that selector you can write logic to execute the Query and return the data in json format.

Cache the JSON on dispatcher.

Removing the Cache JSON, you can either use TTL or ACS commons feature - Dispatcher Flush rules. (https://adobe-consulting-services.github.io/acs-aem-commons/features/dispatcher-flush-rules/index.html)

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded