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

Question

我搞错了

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

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

Configuration: PostgreSQL 9.6 +JPA2.1+ Hibernate 5.2.3

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

但是，当我启用缓存时，请不要出错。除了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;

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

预期结果集

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

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

幸运的是，hibernate API允许您指定受查询影响的实体或查询空间。告诉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>

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

Answer 2

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

Answer 3

OMG，您不应该在本机查询中使用缓存，hibernate不是为此而设计的：

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