Spring Data JPA非原生@Query高效统计分组查询结果数方案咨询

问题 #

需要在Spring Data JPA中使用非原生@Query方法统计包含group by和having子句的查询结果数量。原原生查询如下：

select count(*) from (
    select 1 from table t 
    where t.field_a = 1
    group by t.id
    having count(*) = 2) a;

由于Hibernate 5不允许在from子句中使用子查询，只能寻找替代方案，但当前找到的方案查询效率极低：

select count(*) from table t
where t.field_a = 1 and
2 = (select count(*) from table temp where temp.id = t.id);

希望找到一种和原查询效率相当的Spring Data JPA非原生查询写法，同时避免查询内层结果后在Java中调用size()统计（会引发大量冗余数据传输问题）。

解决方案 #

优化子查询方案（接近原查询效率） #

基于现有子查询逻辑，通过添加联合索引提升效率，同时用HQL编写非原生@Query：

假设对应实体类为TableEntity，字段fieldA对应field_a、id对应原表id，可以这样实现：

@Repository
public interface TableEntityRepository extends JpaRepository<TableEntity, Long> {

    @Query("select count(distinct t.id) from TableEntity t " +
           "where t.fieldA = :fieldA " +
           "and (select count(temp.id) from TableEntity temp " +
           "     where temp.id = t.id and temp.fieldA = :fieldA) = :targetCount")
    Long countMatchingGroups(@Param("fieldA") Integer fieldA, @Param("targetCount") Integer targetCount);
}

关键优化点：

• 给数据表添加联合索引idx_id_fielda (id, field_a)，让子查询可以直接通过索引快速统计符合条件的记录数，避免全表扫描，效率可接近原原生查询。
• 使用count(distinct t.id)确保统计的是唯一分组ID的数量，避免重复计数。

两次查询组合方案（效率略低于原查询但无冗余传输） #

如果不想依赖索引优化，可以通过两次HQL查询组合得到结果，避免Java层的冗余数据传输：

@Repository
public interface TableEntityRepository extends JpaRepository<TableEntity, Long> {

    // 统计符合fieldA条件的所有唯一ID数量
    @Query("select count(distinct t.id) from TableEntity t where t.fieldA = :fieldA")
    Long totalUniqueIds(@Param("fieldA") Integer fieldA);

    // 统计符合fieldA条件但分组记录数不等于targetCount的ID数量
    @Query("select count(distinct t.id) from TableEntity t " +
           "where t.fieldA = :fieldA " +
           "group by t.id having count(*) != :targetCount")
    Long invalidGroupIds(@Param("fieldA") Integer fieldA, @Param("targetCount") Integer targetCount);

    // 最终有效分组数 = 总唯一ID数 - 无效分组数
    default Long countValidGroups(Integer fieldA, Integer targetCount) {
        return totalUniqueIds(fieldA) - invalidGroupIds(fieldA, targetCount);
    }
}

该方案通过两次数据库层面的统计查询得到结果，不会传输大量冗余数据，效率优于Java层调用size()的方式。

版本升级方案（完全匹配原查询效率） #

如果项目可以升级到Hibernate 6+，则支持from子句的子查询，可以直接写出和原原生查询逻辑一致的HQL，效率完全匹配原查询：

@Query("select count(*) from (select 1 from TableEntity t " +
       "where t.fieldA = :fieldA group by t.id having count(*) = :targetCount)")
Long countMatchingGroups(@Param("fieldA") Integer fieldA, @Param("targetCount") Integer targetCount);

内容的提问来源于stack exchange，提问作者Daniel Pop