How to cache results of a Spring Data JPA query method without using query cache?

Question

I have a Spring Boot app with Spring Data JPA (hibernate backend) repository classes. I've added a couple custom finder methods, some with specific @Query annotation to tell it how to get the data. I have already set up EhCache for the hibernate 2nd level cache, but so far, the only way I can get these results caching is to enable the hibernate query cache. I'd prefer to define a specific cache and store the actual domain objects there just as if it were a normal finder. Below is my repo code:

public interface PromotionServiceXrefRepository extends PagingAndSortingRepository<PromotionServiceXref, Integer> {

  @Query("SELECT psx FROM Customer c " +
         "JOIN c.customerProductPromotions cpp " +
         "JOIN cpp.productPromotion pp " +
         "JOIN pp.promotion p JOIN p.promotionServiceXrefs psx " +
         "WHERE c.customerId = ?1")
  @QueryHints(@QueryHint(name = "org.hibernate.cacheable", value = "true"))
  @Cache(usage = CacheConcurrencyStrategy.READ_ONLY, region = "promotionServiceXrefByCustomerId")
  Set<PromotionServiceXref> findByCustomerId(int customerId);
}

And here is the "promotionServiceXrefByCustomerId" cache I defined, that is NOT being used:

<cache name="promotionServiceXrefByCustomerId" overflowToDisk="true" diskPersistent="true"
       maxEntriesLocalHeap="3000000" eternal="true" diskSpoolBufferSizeMB="20" memoryStoreEvictionPolicy="LFU"
       transactionalMode="off" statistics="true">
</cache>

What am I doing wrong? If I enable StandardQueryCache then this data gets cached there and hibernate does not execute a query. But when I disable the query caching, this does not get cached. What am I doing wrong here? PLEASE HELP!

Aureole

Why should the @Cache annotation do anything for a non entity ? That annotation is meant to be on entities not on arbitrary classes or interfaces. – Kinetic

I'm trying to figure it out... so any help would be appreciated. The other find... methods that are specified in the PagingAndSortingRepository that are created at runtime by Spring provide caching using JPA/Hibernate's 2nd level caching. That works properly. But this finder method I created, I can't figure out how to get IT to cache... – Aureole

I doubt the findAll is caching unless you have caching annotations on your entities (which is where they should go). If you are trying (or expecting) a non-cacheable entity to be cached it won't work, only the query cache will work in that case. – Kinetic

Thanks. That's what I've figured out. I've been trying to use the hibernate 2nd level cache for the entity to cache a custom query. I don't want to use Query Caching so I am now using Spring's Cache abstraction and caching at the service level for these custom finders. – Aureole

Answer 1

The reason the code you have is not working is that @Cache is not intended to work that way. If you want to cache the results of a query method execution, the easiest way is to use Spring's caching abstraction.

interface PromotionServiceXrefRepository extends PagingAndSortingRepository<PromotionServiceXref, Integer> {

  @Query("…")
  @Cacheable("servicesByCustomerId")
  Set<PromotionServiceXref> findByCustomerId(int customerId);

  @Override
  @CacheEvict(value = "servicesByCustomerId", key = "#p0.customer.id")
  <S extends PromotionServiceXref> S save(S service);
}

This setup will cause results of calls to findByCustomerId(…) be cached by the customer identifier. Note, that we added an @CacheEvict to the overridden save(…) method, so that the cache we populate with the query method is evicted, whenever an entity is saved. This probably has to be propagated to the delete(…) methods as well.

Now you can go ahead an configure a dedicated CacheManager (see the reference documentation for details) to plug in whichever caching solution you prefer (using a plain ConcurrentHashMap here).

 @Configuration
 @EnableCaching
 class CachingConfig {

   @Bean
   CacheManager cacheManager() {

     SimpleCacheManager cacheManager = new SimpleCacheManager();
     cacheManager.addCaches(Arrays.asList(new ConcurrentMapCache("servicesByCustomerId)));

     return cacheManager;
   }
 }

Satiated

Thanks. I did start going this route but ran into an issue when I had multiple methods in a class that I needed to have caching abstraction around but were actually called from another method in that same class. I would have needed to go AspectJ route. It turned out, I've abandoned the need to cache this data for business reasons right now so this "problem" is no longer a problem. But you are right, the best route is definitely moving to Spring's Cache abstraction. Thanks! – Aureole

Doesn't it lead to all kind of problems and strange behaviour, if we cache the Entities in the Spring Cache? – Mylan

Spring recommends to only annotate concrete classes (docs.spring.io/spring/docs/current/spring-framework-reference/… paragraph after "Method visibility and cache annotations") and I run into this error using it on an interface: "Could not generate CGLIB subclass of class [class com.sun.proxy.$Proxy180]". What is the recommended way to deal with such a situation? (this is with Spring 4.1.7) – Cantilena

cacheManager.addCaches(Arrays.asList(new ConcurrentMapCache("servicesByCustomerId))); would be confusing as the cache manager name don't have to be the same as the key defined in the cachable method – Richma

Wouldn't it be preferable to use @CachePut on <S extends PromotionServiceXref> S save(S service); ? Also your use of @Cacheable will result in hundreds or thousands of distinct caches in a medium- to large-sized app. Is that desirable? I would have expected e.g. @Cacheable("services") in combination with a custom key. – Subvention

See also github.com/spring-projects/spring-data-examples/blob/master/jpa/… – Magnusson

I tried to use this solution, but the cached entities are detached when pulled from the cache, obviously. which can be a problem with lazy loaded collection. – Steelhead

Should we also override and place @CacheEvict on saveAll and delete* methods? – Trochophore

Answer 2

You need to be aware that by giving up on the Hibernate QueryCache your are responsible for invalidating the queries that become stale when saving, updating, deleting entities that influenced the query result(what Oliver is doing by setting CacheEvict on save) - which I think can be a pain- or at least you need to take into account and ignore it if it's not really a problem for your scenario.

Keloid

Answer 3

First I quote your question:

What am I doing wrong?

The way you are trying to name the cache is not appropriate to how hibernate will use it. Check org.hibernate.engine.spi.CacheInitiator which uses org.hibernate.internal.CacheImpl which is based on:

if ( settings.isQueryCacheEnabled() ) {
    final TimestampsRegion timestampsRegion = regionFactory.buildTimestampsRegion(
            qualifyRegionName( UpdateTimestampsCache.REGION_NAME ),
            sessionFactory.getProperties()
    );
    updateTimestampsCache = new UpdateTimestampsCache( sessionFactory, timestampsRegion );
    ...
}

And UpdateTimestampsCache.REGION_NAME (equals to org.hibernate.cache.spi.UpdateTimestampsCache) is what you are missing as the cache name. For the query cache you'll have to use exactly that cache name and no other!

Now few other thoughts related to your problem:

• removing @Cache and setting cache name to org.hibernate.cache.spi.UpdateTimestampsCache will allow your query to be cached with ehcache by hibernate (spring cache abstraction is not involved here)
• setting a hardcoded cache name won't make you happy I'm sure but at least you know why this happens
• Balamaci Serban (the post just below) is painfully right

Below is the configuration from one of my projects where ehcache + @Query + @QueryHints work as expected (ehcache/ehcache-in-memory.xml file):

<?xml version="1.0" encoding="UTF-8"?>
<ehcache name="in-memory" xmlns="http://ehcache.org/ehcache.xsd">
    <!--<diskStore path="java.io.tmpdir"/>-->

    <!--
        30d = 3600×24×30 = 2592000
    -->

    <cache name="org.hibernate.cache.internal.StandardQueryCache"
           maxElementsInMemory="9999" eternal="false"
           timeToIdleSeconds="2592000" timeToLiveSeconds="2592000"
           overflowToDisk="false" overflowToOffHeap="false"/>

    <cache name="org.hibernate.cache.spi.UpdateTimestampsCache"
           maxElementsInMemory="9999" eternal="true"
           overflowToDisk="false" overflowToOffHeap="false"/>

    <defaultCache maxElementsInMemory="9999" eternal="false"
                  timeToIdleSeconds="2592000" timeToLiveSeconds="2592000"
                  overflowToDisk="false" overflowToOffHeap="false"/>
</ehcache>

and hibernate.properties:

hibernate.jdbc.batch_size=20
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.validator.autoregister_listeners=false
hibernate.cache.use_second_level_cache=true
hibernate.cache.use_query_cache=true
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
hibernate.hbm2ddl.auto=update
net.sf.ehcache.configurationResourceName=ehcache/ehcache-in-memory.xml
hibernate.dialect=org.hibernate.dialect.H2Dialect

and some versions from pom.xml for which my explanation applies:

<springframework.version>5.0.6.RELEASE</springframework.version>
<spring-security.version>5.0.5.RELEASE</spring-security.version>
<spring-data-jpa.version>2.1.0.RELEASE</spring-data-jpa.version>
<hibernate.version>5.2.13.Final</hibernate.version>
<jackson-datatype-hibernate5.version>2.9.4</jackson-datatype-hibernate5.version>

And the full working test is image.persistence.repositories.ImageRepositoryTest.java found here: https://github.com/adrhc/photos-server/tree/how-to-cache-results-of-a-spring-data-jpa-query-method-without-using-query-cache
Yep, run mvn clean install or change my env.sh if you really want to use my shell scripts. Check then the number of sql queries on behalf of 3x imageRepository.count() call.

Thickskinned