Query JCR_SQL2

[robertol6836527]

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

[TarunKumar]

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]

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;
    }
}

[yuriy_shestakov · Accepted solution]

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]

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

Really appreciated !

[Nishant-Singh]

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.ht...