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 certificationTypeIdInteger certificationStatusTypeIdLocalDate dateFromLocalDate dateToString client
- 当前已完成certificationTypeId、时间范围、客户端名称对应的查询条件编写,仅剩余certificationStatusTypeId对应的过滤逻辑待实现
开发过程中的报错记录
- 初始参考的原生SQL逻辑:
SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?; - 第一次基于参考SQL编写JPA代码时,抛出方法无法解析的编译错误
- 调整代码加入子查询后,抛出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
相关产品推荐
相关产品推荐

