HQL调用自定义函数GROUP BY报错及JPA返回原始类型求助

1. HQL调用数据库函数分组查询报错 #

场景说明 #

MySQL中可正常执行的查询：

SELECT GetTaskStatus(t.due_date, t.completed_date) as status, 
       count(*) as total  
FROM tasks t  
GROUP BY status

其中GetTaskStatus是已注册的数据库函数，在HQL的WHERE子句中使用正常，但执行以下HQL时报错：

@Query("SELECT GetTaskStatus(t.dueDate, t.completedDate) as status FROM Task t GROUP BY status")

报错信息：

Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode 
 \-[METHOD_CALL] MethodNode: '('
    +-[METHOD_NAME] IdentNode: 'GetTaskStatus' {originalText=GetTaskStatus}
    \-[EXPR_LIST] SqlNode: 'exprList'
       +-[DOT] DotNode: 'task0_.due_date' {propertyName=dueDate,dereferenceType=PRIMITIVE,getPropertyPath=dueDate,path=t.dueDate,tableAlias=task0_,className=br.com.fisgar.crm.entities.Task,classAlias=t}
       |  +-[ALIAS_REF] IdentNode: 'task0_.id' {alias=t, className=br.com.fisgar.crm.entities.Task, tableAlias=task0_}
       |  \-[IDENT] IdentNode: 'dueDate' {originalText=dueDate}
       \-[DOT] DotNode: 'task0_.completed_date' {propertyName=completedDate,dereferenceType=PRIMITIVE,getPropertyPath=completedDate,path=t.completedDate,tableAlias=task0_,className=br.com.fisgar.crm.entities.Task,classAlias=t}
          +-[ALIAS_REF] IdentNode: 'task0_.id' {alias=t, className=br.com.fisgar.crm.entities.Task, tableAlias=task0_}
          \-[IDENT] IdentNode: 'completedDate' {originalText=completedDate}

解决方案 #

HQL解析器无法识别在GROUP BY子句中引用SELECT里的方法调用别名，因为它无法推断该方法节点的数据类型，可通过以下两种方式解决：

方式一：在GROUP BY中重复函数调用

修改HQL，将函数调用完整写入GROUP BY子句：

@Query("SELECT GetTaskStatus(t.dueDate, t.completedDate) as status, COUNT(*) as total FROM Task t GROUP BY GetTaskStatus(t.dueDate, t.completedDate)")

方式二：使用原生SQL查询

直接使用原生SQL绕过HQL的解析限制，指定nativeQuery = true：

@Query(value = "SELECT GetTaskStatus(t.due_date, t.completed_date) as status, count(*) as total FROM tasks t GROUP BY status", nativeQuery = true)

2. JPA Repository返回原始列值类型（如List<Map<String,Object>>） #

无需创建新的实体/DTO类，可通过以下两种方式让Repository返回List<Map<String, Object>>：

方式一：原生SQL查询 #

使用原生SQL时，JPA会自动将结果映射为Map，键为查询中的列别名，值为对应字段值：

@Query(value = "SELECT GetTaskStatus(t.due_date, t.completed_date) as status, count(*) as total FROM tasks t GROUP BY status", nativeQuery = true)
List<Map<String, Object>> getTaskStatusCounts();

方式二：HQL的new map()语法 #

在HQL中使用new map()手动构建Map，指定键的别名：

@Query("SELECT new map(GetTaskStatus(t.dueDate, t.completedDate) as status, COUNT(*) as total) FROM Task t GROUP BY GetTaskStatus(t.dueDate, t.completedDate)")
List<Map<String, Object>> getTaskStatusCounts();

内容的提问来源于stack exchange，提问作者Rafael Lima