JPQL分页查询报错：QuerySyntaxException，期望EOF却找到')'

问题场景 #

原本返回List<KpiLibraryData>的JPQL查询可正常运行，但修改为返回Page<KpiLibraryData>实现分页时，出现语法错误。

原List查询代码 #

@Query("SELECT new com.kpisoft.performance.kpi.vo.KpiLibraryData(kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus, " +
            "(SELECT COUNT(klc.employee.id) FROM KpiLibraryCascade klc WHERE klc.libraryId = kdl.id AND klc.programId = :programId) as employeeCount ) " +
            "FROM KpiLibrary kdl " +
            "LEFT JOIN KpiLibraryCascade klc ON kdl.id = klc.libraryId " +
            "WHERE (kdl.role = :role OR kdl.role IS NULL) " +
            "AND kdl.tenant.id = :tenantId " +
            "GROUP BY kdl.id " +
            "ORDER BY kdl.goalSourceStatus DESC, employeeCount DESC")
List<KpiLibraryData> findKpiLibraryDatSorted(@Param("programId") Integer programId,
                                             @Param("role") String role,
                                             @Param("tenantId") Integer tenantId);

分页查询代码（报错版本） #

@Query("SELECT new com.kpisoft.performance.kpi.vo.KpiLibraryData(kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus, " +
            " (SELECT COUNT(klc.employee.id) FROM KpiLibraryCascade klc WHERE klc.libraryId = kdl.id AND klc.programId = :programId) as employeeCount ) " +
            " FROM KpiLibrary kdl " +
            " LEFT JOIN KpiLibraryCascade klc ON kdl.id = klc.libraryId " +
            " AND klc.programId = :programId " +
            " WHERE (kdl.role = :role OR kdl.role IS NULL) " +
            " AND kdl.tenant.id = :tenantId " +
            " GROUP BY kdl.id " +
            " ORDER BY kdl.goalSourceStatus DESC, employeeCount DESC ")
Page<KpiLibraryData> findKpiLibraryDatPagination(@Param("programId") Integer programId,
                                                 @Param("role") String role,
                                                 @Param("tenantId") Integer tenantId,
                                                 Pageable pageable);

报错信息（翻译后） #

引发原因：org.hibernate.hql.internal.ast.QuerySyntaxException: 预期遇到语句结束符(EOF)，但在第1行第140位附近发现')'
错误生成的count查询语句：
select count(klc) FROM com.kpisoft.performance.kpi.entity.KpiLibraryCascade klc WHERE klc.libraryId = kdl.id AND klc.programId = :programId) as employeeCount ) FROM com.kpisoft.performance.kpi.entity.KpiLibrary kdl LEFT JOIN com.kpisoft.performance.kpi.entity.KpiLibraryCascade klc ON kdl.id = klc.libraryId AND klc.programId = :programId WHERE (kdl.role = :role OR kdl.role IS NULL) AND kdl.tenant.id = :tenantId GROUP BY kdl.id

问题原因 #

Spring Data JPA处理Page返回类型时，会自动生成count查询以获取总记录数。原查询中的两个问题导致语法错误：

1. 子查询后的as employeeCount别名在自动生成count查询时被保留，破坏了count查询的语法结构；
2. LEFT JOIN中额外添加的AND klc.programId = :programId条件多余，且干扰了分组逻辑，同时子查询已经完成了programId的过滤。

修复方案 #

方案1：修正原查询语法 #

移除子查询的别名，同时调整ORDER BY语句（因别名无法在自动生成的count查询中识别）：

@Query("SELECT new com.kpisoft.performance.kpi.vo.KpiLibraryData(kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus, " +
        "(SELECT COUNT(klc.employee.id) FROM KpiLibraryCascade klc WHERE klc.libraryId = kdl.id AND klc.programId = :programId) ) " +
        "FROM KpiLibrary kdl " +
        "LEFT JOIN KpiLibraryCascade klc ON kdl.id = klc.libraryId " +
        "WHERE (kdl.role = :role OR kdl.role IS NULL) " +
        "AND kdl.tenant.id = :tenantId " +
        "GROUP BY kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus " +
        "ORDER BY kdl.goalSourceStatus DESC, (SELECT COUNT(klc.employee.id) FROM KpiLibraryCascade klc WHERE klc.libraryId = kdl.id AND klc.programId = :programId) DESC")
Page<KpiLibraryData> findKpiLibraryDatPagination(@Param("programId") Integer programId,
                                                 @Param("role") String role,
                                                 @Param("tenantId") Integer tenantId,
                                                 Pageable pageable);

方案2：优化查询逻辑（推荐） #

去掉子查询，直接通过关联查询统计员工数，避免重复子查询，同时符合JPQL GROUP BY规范：

@Query("SELECT new com.kpisoft.performance.kpi.vo.KpiLibraryData(kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus, COUNT(DISTINCT klc.employee.id)) " +
        "FROM KpiLibrary kdl " +
        "LEFT JOIN KpiLibraryCascade klc ON kdl.id = klc.libraryId AND klc.programId = :programId " +
        "WHERE (kdl.role = :role OR kdl.role IS NULL) " +
        "AND kdl.tenant.id = :tenantId " +
        "GROUP BY kdl.id, kdl.kpiName, kdl.description, kdl.goalSourceStatus " +
        "ORDER BY kdl.goalSourceStatus DESC, COUNT(DISTINCT klc.employee.id) DESC")
Page<KpiLibraryData> findKpiLibraryDatPagination(@Param("programId") Integer programId,
                                                 @Param("role") String role,
                                                 @Param("tenantId") Integer tenantId,
                                                 Pageable pageable);

说明：使用COUNT(DISTINCT klc.employee.id)确保同一个员工不会被重复统计，GROUP BY子句必须包含SELECT中所有非聚合字段（JPQL语法要求）。

内容的提问来源于stack exchange，提问作者Avinash S