Hibernate Ehcache does NOT work for SQL Native query cache

Question

I get an error

aliases expected length is 1; actual length is 4 at org.hibernate.transform.CacheableResultTransformer.transformTuple 

I have JPA + Hibernate configurations and request cache and second level cache using Eh-Cache.

Configuration: PostgreSQL 9.6 + JPA 2.1 + Hibernate 5.2.3.Final

I am trying to execute NativeQuery with SqlResultSetMapping [custom result set class]. Everything works fine when I turn off the cache.

But, exceeding the error when I turn on the cache. The cache works fine except for NativeQuery.

SCHEMA table:

 PK first second third 

---

  1 A abc C 2 A abc C 3 A xyz D 4 B abc C 5 B xyz C 6 B abc D 7 A xyz C 8 A abc D 

SQL Native QUERY:

 SELECT t.first,t.second, COUNT(t.second) total, COALESCE(t1.ccount, 0) ccount, COALESCE(t2.dcount, 0) dcount FROM test t LEFT JOIN (SELECT COUNT(third) AS ccount, FIRST, SECOND FROM test WHERE third = 'C' GROUP BY SECOND,FIRST) t1 ON (t1.first = t.first AND t1.SECOND = t.SECOND) LEFT JOIN (SELECT COUNT(third) AS dcount, FIRST, SECOND FROM test WHERE third = 'D' GROUP BY SECOND,FIRST) t2 ON (t2.first = t.first AND t2.SECOND = t.SECOND) GROUP BY t.SECOND, t.first; 

SqlResultSetMapping

  @SqlResultSetMapping(name = "RESULT_SET_NAME", classes = { @ConstructorResult( targetClass = TestResult.class, columns = { @ColumnResult(name = "first", type = String.class), @ColumnResult(name = "second", type = String.class), @ColumnResult(name = "total", type = String.class), @ColumnResult(name = "ccount", type = String.class), @ColumnResult(name = "dcount", type = String.class) }) }) query = getEntityManager().createNativeQuery(nativeQuery, "RESULT_SET_NAME"); query.setHint("org.hibernate.cacheable", true); result = query.getResultList(); 

Expected Result

 first second total ccount dcount ------ ------ ------ ------ -------- A abc 3 2 1 B abc 2 1 1 A xyz 2 1 1 B xyz 1 1 0 

stack trace

 aliases expected length is 1; actual length is 4 java.lang.IllegalStateException: aliases expected length is 1; actual length is 4 at org.hibernate.transform.CacheableResultTransformer.transformTuple(CacheableResultTransformer.java:155) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:770) at org.hibernate.loader.Loader.processResultSet(Loader.java:985) at org.hibernate.loader.Loader.doQuery(Loader.java:943) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349) at org.hibernate.loader.Loader.doList(Loader.java:2615) at org.hibernate.loader.Loader.listUsingQueryCache(Loader.java:2460) at org.hibernate.loader.Loader.list(Loader.java:2422) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:335) at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2129) at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:981) at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:147) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1398) at org.hibernate.Query.getResultList(Query.java:417) 

Answer 1

OMG, you should not use the cache with your own requests, hibernate is not intended for this:

https://www.link-intersystems.com/blog/2011/10/08/impact-of-native-sql-queries-on-hibernates-second-level-cache/

Answer 2

In your case, EHCache can only be used with JPQL queries. It also means that you will have to rewrite your query to avoid using subselects, unions, or similar built-in sql constructs.

Answer 3

Hibernate can not know what you do when you run your own SQL query, and therefore it cannot know which caches should be invalidated. While hibernation does not know which caches are affected, it must assume that all data is invalid to ensure data consistency. This means that hibernation will invalidate all caches.

The Fortunatly Hibernation API allows you to specify the objects or request spaces that your request affects. Tell hibernate which tables your query affects, and hibernation will invalidate caches based on this data.

 SQLQuery sqlQuery = session.createSQLQuery("UPDATE CUSTOMER SET ... WHERE ..."); sqlQuery.addSynchronizedEntityClass(Person.class); int updatedEntities = sqlQuery.executeUpdate(); 

with the name of the object

 sqlQuery.addSynchronizedEntityClass(Person.class); sqlQuery.addSynchronizedEntityName("com.link_intersystems.xhibernate.testclasses.Person"); sqlQuery.addSynchronizedQuerySpace("SOME_TABLE"); 

Sometimes you want to execute your own query that does not modify any data. To prevent hibernation from invalidating second-level caches, you can add empty request space synchronization.

 SQLQuery sqlQuery = session.createSQLQuery("ALTER SESSION SET NLS_COMP = 'BINARY'"); sqlQuery.addSynchronizedQuerySpace(""); /* * Only the empty query space "" will be invalidated. * So no cache will be invalidated, because no table with an empty name exists */ int updatedEntities = sqlQuery.executeUpdate(); 

in hibernate xml display

 <sql-query name="setNLSCompBinary"> <!-- an empty synchronize tag prevents hibernate from invalidating second level caches --> <synchronize table="" /> ALTER SESSION SET NLS_COMP = 'BINARY' </sql-query> 

impact-of-native-sql-queries-on-hibernates-second-level-cache