Spring Boot 3.1.6中JPQL按实体Group By报错问题求助

问题背景 #

升级Spring Boot从2.5.4到3.1.6后，原本正常运行的JPA分组查询触发SQL语法错误：

ERROR: column "b1_1.id" must appear in the GROUP BY clause or be used in an aggregate function<EOL>

涉及实体类 #

@Entity
public class Brand {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false)
    @EqualsAndHashCode.Include
    @Schema(description = "Brand's id", example = "1")
    private Long id;
    // ... 其他字段
}

@Entity
public class Product {
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "product_brands", joinColumns = @JoinColumn(name = "product_id"),
        inverseJoinColumns = @JoinColumn(name = "brands_id"))
    private List<Brand> brands;
    // ... 其他字段
}

原查询方法 #

@Query("select new com.mypackage.Statistic(br, count(p)) " +
        "from Lot l join l.products p join p.brands br " +
        "group by br " +
        "order by count(p) desc")
List<Statistic<Brand>> groupByProductBrand();

生成的错误SQL #

select b1_1.id, ..., count(p1_0.products_id) 
from lot l1_0
join (lot_products p1_0 join product p1_1 on p1_1.id=p1_0.products_id) on l1_0.id=p1_0.lot_id 
join (product_brands b1_0 join brand b1_1 on b1_1.id=b1_0.brands_id) on p1_1.id=b1_0.product_id 
group by b1_0.brands_id 
order by count(p1_0.products_id) desc offset;

可以看到，SQL中select的b1_1.id来自brand表，而group by的b1_0.brands_id来自中间关联表product_brands，尽管值相同，但数据库会判定为不同列，导致查询失败。

解决方案 #

修改JPQL的group by子句，明确指定按Brand实体的主键id分组，而非整个实体：

@Query("select new com.mypackage.Statistic(br, count(p)) " +
        "from Lot l join l.products p join p.brands br " +
        "group by br.id " +
        "order by count(p) desc")
List<Statistic<Brand>> groupByProductBrand();

如果需要分组后完整返回Brand实体，也可以在group by中包含Brand的所有字段（主键通常已足够，因为主键唯一标识实体）：

@Query("select new com.mypackage.Statistic(br, count(p)) " +
        "from Lot l join l.products p join p.brands br " +
        "group by br.id, br.[其他字段] " +
        "order by count(p) desc")
List<Statistic<Brand>> groupByProductBrand();

原因分析 #

Spring Boot 3.1.6配套的Hibernate版本为6.2.x，而Spring Boot 2.5.4使用的是Hibernate 5.4.x。Hibernate 6对JPQL到SQL的转换逻辑做了严格调整：

• 在Hibernate 5中，当使用group by br（按实体分组）时，框架会自动处理为按实体主键分组，生成的SQL会将group by指向实体表的主键列。
• 而Hibernate 6在处理多对多关联的分组时，错误地将group by指向了中间关联表的外键列，导致select和group by的列来源不一致，触发数据库的SQL标准校验错误。

内容的提问来源于stack exchange，提问作者Gauthier Tassin