java - Hibernate Ehcache 不适用于 SQL Native 查询缓存

Question

我遇到了错误

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

我有 JPA + Hibernate 配置和查询缓存以及使用 Eh-Cache 的二级缓存。

配置:PostgreSQL 9.6 + JPA 2.1 + Hibernate 5.2.3.Final

我正在尝试使用 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;

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 无法知道您在执行 native 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");

有时您希望执行不更改任何数据的 native 查询。为防止 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