You need to enable JavaScript to run this app. Spring Data JPA Criteria 实现按外键分组取MAX(id)动态查询
优惠活动
大模型
产品
解决方案
定价
更多

Spring Data JPA Criteria 实现按外键分组取MAX(id)动态查询

基于Spring Boot与Spring Data JPA的动态查询实现

核心实体定义

@Entity
public class Certification {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "certification_type_id")
    private CertificationType certificationType;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "certification", cascade = CascadeType.ALL)
    private Set<CertificationStatus> certificationStatuses;
}

@Entity
public class CertificationType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Enumerated(EnumType.STRING)
    private Code code;
    private String name;
    private String description;
    ...
    public enum Code {
        ...
    }
}

@Entity
public class CertificationStatus {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @ManyToOne
    @JoinColumn(name = "certification_id")
    private Certification certification;
    @ManyToOne
    @JoinColumn(name = "certification_status_type_id")
    private CertificationStatusType certificationStatusType;
    private String remarks;
}

@Entity
public class CertificationStatusType {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    @Enumerated(EnumType.STRING)
    private Code code;
    private String name;
    private String description;
    @OneToMany(fetch = FetchType.EAGER, mappedBy = "certificationStatusType", cascade = CascadeType.ALL)
    private Set<CertificationStatus> certificationStatuses;
    
    public enum Code {
        ...
    }
}

业务需求

目标为实现CertificationService.findAll()方法,核心规则如下:

  • 检索关联最新CertificationStatus的Certification记录,最新状态的判定逻辑为:取CertificationStatus表中按certification_id分组的最大id,且匹配指定的certification_status_type_id
  • 方法共支持5个可选查询参数,参数组合可为0到5个,因此选用JPA Criteria实现动态查询:
    • Integer certificationTypeId
    • Integer certificationStatusTypeId
    • LocalDate dateFrom
    • LocalDate dateTo
    • String client
  • 当前已完成certificationTypeId、时间范围、客户端名称对应的查询条件编写,仅剩余certificationStatusTypeId对应的过滤逻辑待实现

开发过程中的报错记录

  1. 初始参考的原生SQL逻辑:
    SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
    
  2. 第一次基于参考SQL编写JPA代码时,抛出方法无法解析的编译错误
  3. 调整代码加入子查询后,抛出HQL路径无效的运行时报错:
    org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select generatedAlias0 from entity.Certification as generatedAlias0 where ( generatedAlias1.id in (select max(generatedAlias1.id) from entity.CertificationStatus as generatedAlias1 group by generatedAlias1.certification.id) ) and ( generatedAlias1.certificationStatusType.id=2 )]
    

最终可正常运行的实现

经逻辑梳理,调整为多表关联查询实现:先在主查询中关联Certification的certificationStatuses属性,再搭配独立子查询按certification_id分组取最大id,对应代码如下:

if (certificationStatusTypeId != null && certificationStatusTypeId > 0) {
    var certificationStatuses = root.join(Certification_.certificationStatuses);
    Subquery<Long> subQuery = criteriaQuery.subquery(Long.class);
    Root<CertificationStatus> subRoot = subQuery.from(CertificationStatus.class);
    subQuery.select(criteriaBuilder.max(subRoot.get(CertificationStatus_.id)))
            .groupBy(subRoot.get(CertificationStatus_.certification).get(Certification_.id));
    predicates.add(criteriaBuilder.and(certificationStatuses.get(CertificationStatus_.id).in(subQuery)));
    predicates.add(criteriaBuilder.and(criteriaBuilder.equal(certificationStatuses.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
}

内容的提问来源于stack exchange,提问作者Julez

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 00:57:03