在Hibernate 6.2中加入CTE

ubof19bj 于 2023-05-29 发布在其他

关注(0)|答案(1)|浏览(208)

Hibernate 6.2引入了CTE支持，但不清楚如何在单个HQL查询中使用多个CTE。假设我有City实体和以下CTE查询：

TypedQuery<Integer> typedQuery = em.createQuery(
        """
           with max_cities as (
             SELECT id id from City c ORDER BY population DESC
             LIMIT 20 
          ), min_cities as (
             SELECT id id from City c ORDER BY population ASC
             LIMIT 20
          )
           SELECT m1.id from max_cities m1 join min_cities m2
              on m1.id = m2.id
        """, 
        Integer.class);

它包含两个CTE（min_cities和max_cities）。而Hibernate并没有抱怨这一点。但是错误信息在这里非常清楚：

Caused by: java.lang.IllegalArgumentException: Could not resolve entity reference: min_cities
    at org.hibernate.metamodel.model.domain.internal.JpaMetamodelImpl.resolveHqlEntityReference(JpaMetamodelImpl.java:166)

所以Hibernate允许在FROM子句中使用第一个CTE，但在JOIN子句中需要实体（而不是CTE）。然而，MySQL文档指出，加入两个CTE是非常法律的的：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

Hibernate支持加入CTE吗？

Hibernate

来源：https://stackoverflow.com/questions/76351608/joining-ctes-in-hibernate-6-2

1条答案

按热度按时间

vwkv1x7d1#

这看起来像是ExpectingEntityJoinDelegate#consumeIdentifier中的一个bug：

@Override
public void consumeIdentifier(String identifier, boolean isTerminal, boolean allowReuse) {
    if ( path.length() != 0 ) {
        path.append( '.' );
    }
    path.append( identifier );
    if ( isTerminal ) {
        final String fullPath = path.toString();
        final EntityDomainType<?> joinedEntityType = creationState.getCreationContext()
                .getJpaMetamodel()
                .resolveHqlEntityReference( fullPath );
        // here impl assumes that resolveHqlEntityReference may return nulls,
        // however it never does that. Most probably getHqlEntityReference
        // should be used instead
        if ( joinedEntityType == null ) {
            final SqmCteStatement<?> cteStatement = creationState.findCteStatement( fullPath );
            if ( cteStatement != null ) {
                join = new SqmCteJoin<>( cteStatement, alias, joinType, sqmRoot );
                creationState.getCurrentProcessingState().getPathRegistry().register( join );
                return;
            }
            throw new SemanticException( "Could not resolve join path - " + fullPath );
        }

顺便说一句，交叉连接确实按预期工作，即：

"""
   with max_cities as (
     SELECT id id from City c ORDER BY population DESC
     LIMIT 20
  ), min_cities as (
     SELECT id id from City c ORDER BY population ASC
     LIMIT 20
  )
   SELECT m1.id from max_cities m1, min_cities m2
      where m1.id = m2.id
"""

赞(0）回复(0）举报 2023-05-29

我来回答

在Hibernate 6.2中加入CTE

1条答案

相关问题

热门标签

最新问答