Hibernate Ehcache NOT working for SQL Native query cache

Question

I am getting error

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

I have JPA + Hibernate configurations and query 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 is working fine when I am disabling cache.

But, getting above error on when I am enabling cache. Cache is working fine except for NativeQuery.

Table SCHEMA:

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-set

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

Hibernate can’t know what you are doing when you execute a native sql query and therefore it can’t know what caches need to be invalidated. As long as hibernate doesn’t know which caches are affected it must assume that all data is invalid to ensure data consistency. This means that hibernate will invalidate all caches.

Fortunatly the hibernate API let you specify the entities or query spaces that are affected by your query. Tell hibernate which tables are affected by your query and hibernate will only invalidate caches that are based on that data.

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

with entity name

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

Sometimes you want to execute a native query that doesn’t change any data. To prevent hibernate from invalidating the second level caches you can add an empty query 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 mapping xml

<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