Hibernate Ehcache不适用于SQL本机查询缓存

Question

我得到错误

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

我有JPA Hibernate配置和使用Eh缓存的查询缓存和二级缓存。

配置：PostgreSQL 9.6 JPA 2.1 Hibernate 5.2.3。最终的

我正在尝试使用SqlResultSetMapping[自定义结果集类]执行NativeQuery。当我禁用缓存时，一切正常。

但是，在启用缓存时出现上述错误。除了NativeQuery，缓存工作正常。

表架构：

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本机查询：

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;

SqlResultsMapping

 @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();

预期结果集

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

堆栈跟踪

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

当执行本机sql查询时，Hibernate不知道您在做什么，因此它不知道哪些缓存需要失效。只要hibernate不知道哪些缓存受到影响，它就必须假设所有数据都无效，以确保数据的一致性。这意味着hibernate将使所有缓存失效。

幸运的是，HibernateAPI允许您指定受查询影响的实体或查询空间。告诉hibernate哪些表会受到查询的影响，而hibernate只会使基于该数据的缓存无效。

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

使用实体名称

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

有时您希望执行不更改任何数据的本机查询。为了防止Hibernate使第二级缓存无效，您可以添加一个空查询空间同步。

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();

在Hibernate映射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>

本机sql查询对hibernates二级缓存的影响

Answer 2

天啊，你不应该在本机查询中使用缓存，Hibernate不是为此设计的：

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

Answer 3

在您的情况下，EHCache只能用于JPQL查询。这也意味着您必须重写查询，以避免使用子选择、联合或类似的本机sql构造。