java - 如何使用 JPA Criteria API/Hibernate 按 Case 语句分组

Question

我正在尝试执行如下查询，通过 case 语句进行选择并通过相同的 case 语句进行分组..

Select USER, 
  (CASE
    WHEN value between 0 AND 2 then '0-2'
    WHEN value between 3 AND 4 then '3-4'
    ELSE '5+'
  END) as CASE_STATEMENT ,
SUM(value)
.....
Group by user, CASE_STATEMENT

使用 JPA 2.0 Criteria API 和 Hibernate。

我的测试用例看起来像...

    CriteriaBuilder cb = em.getCriteriaBuilder()
    CriteriaQuery cq = cb.createQuery(Tuple)
    def root = cq.from(TestEntity)
    def userGet = root.get('user')
    def valueGet = root.get('value')
    def caseExpr =
            cb.selectCase()
                .when(cb.between(valueGet, 0, 2), '0-2')
                .when(cb.between(valueGet, 3, 4), '3-4')
                .otherwise('5+')
    def sumExpr = cb.sum(valueGet)

    cq.multiselect([userGet, caseExpr, sumExpr])
    cq.groupBy([userGet, caseExpr])
    log(typedQuery.unwrap(Query).queryString)
    List<Tuple> tuples = typedQuery.getResultList()

queryString的日志语句读取

SELECT generatedAlias0.USER, 
   CASE 
     WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast(:param0 AS STRING) 
     WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast(:param1 AS STRING) 
     ELSE Cast(:param2 AS STRING) 
   END, 
   Sum(generatedAlias0.value) 
FROM   test AS generatedAlias0 
GROUP  BY generatedAlias0.USER, 
      CASE 
        WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast( 
        :param3 AS STRING) 
        WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast( 
        :param4 AS STRING) 
        ELSE Cast(:param5 AS STRING) 
      END 

当调用 typedQuery.getResultList() 时，我得到以下错误语句

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet

Caused by: org.h2.jdbc.JdbcSQLException: Column "TESTENTITY0_.VALUE" must be in the GROUP BY list; SQL statement:

select testentity0_.user as col_0_0_, case when testentity0_.value between 0 and 2 then cast(? as varchar(255)) when testentity0_.value between 3 and 4 then cast(? as varchar(255)) else cast(? as varchar(255)) end as col_1_0_, sum(testentity0_.value) as col_2_0_ from test testentity0_ group by testentity0_.user , case when testentity0_.value between 0 and 2 then cast(? as varchar(255)) when testentity0_.value between 3 and 4 then cast(? as varchar(255)) else cast(? as varchar(255)) end [90016-194]

我尝试按表达式分组的方式有问题吗？我也尝试过按别名和数字文字 (1, 2) 分组

有没有其他方法可以构建 SQL 以获得相同的结果？

谢谢。

Answer 1

如异常消息所示，问题与 DBMS 级别的 Group By 语句有关。请参阅:https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/

要解决错误，您必须要么

• 将底层 DBMS 的 Group By Mode 设置为限制较少的级别(MySQL 允许 disable only-full-group-by，但是 H2 does not(您可以尝试设置 MODE=MYSQL 在 jdbc 连接字符串中)

  或(更好)

• 将属于 select 语句的所有列添加到 GROUP BY 语句或聚合函数中，如 above 所述.

  您应该能够构建满足 GROUP BY RESTRICTIONS 的嵌套查询。

  为了救援，有一些(可能是特定于 DBMS 的)聚合函数(至少在 MySQL 中)。为了欺骗 JPA 和 Hibernate 来理解这些，有实现这一目标的几种方法，如 https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/和 https://vladmihalcea.com/the-jpa-entitymanager-createnativequery-is-a-magic-wand/

编辑

与上面的陈述相比，下面讨论后的发现是:

• 异常是由 org.h2.expression.ExpressionColumn 类中的 h2 驱动程序 在验证查询语法时引发的
• 该解决方案需要在查询中设置和引用别名(在case 语句 或子查询)，这在Criteria API 目前是不可能的em>(参见 column aliases usually can't be referenced in the query itself)
• 一个变通方法是像这样创建一个 NativeQuery:

List<Tuple> tuples = em.createNativeQuery(
"SELECT generatedAlias0.USER, " +
"   CASE " +
"     WHEN generatedAlias0.value BETWEEN 0 AND 2 THEN Cast(:param0 AS VARCHAR) " +
"     WHEN generatedAlias0.value BETWEEN 3 AND 4 THEN Cast(:param1 AS VARCHAR) " +
"     ELSE Cast(:param2 AS VARCHAR) " +
"   END c, " +
"   Sum(generatedAlias0.value) as sumvalue " +
"FROM test AS generatedAlias0 " +
"GROUP  BY generatedAlias0.USER, c "
)
.setParameter("param0", "0-2")
.setParameter("param1", "3-4")
.setParameter("param2", "5+")
.getResultList();