我正在用 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() . 我们的想法是 certification 最新记录 certification_status 基于 MAX(id) , GROUP BY certification_id 以及 WHERE certification_status_type_id = ? . 此方法有五（5）个参数：

Integer certificationTypeId,
Integer certificationStatusTypeId,
LocalDate dateFrom,
LocalDate dateTo,
String client

组合可以是0到5。因此，我选择应用jpa标准。到目前为止，我已经编写了以下代码：

@Override
public List<CertificationDto> findAll(
        Integer certificationTypeId,
        Integer certificationStatusTypeId,
        LocalDate dateFrom,
        LocalDate dateTo,
        String client) {

    var certifications = this.certificationRepository.findAll((root, criteriaQuery, criteriaBuilder) -> {
        var predicates = new ArrayList<Predicate>();
        // This is working
        if (certificationTypeId != null && certificationTypeId > 0) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.join(Certification_.certificationType, JoinType.INNER), certificationTypeId)));
        }

        // To do
        if (certificationStatusTypeId != null && certificationStatusTypeId > 0) {
            /*
             * I have no idea how to apply my SQL statement:
             * SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;
             */

             // Not sure what is the direction of the ff codes
            var certificationStatuses = root.join(Certification_.certificationStatuses);
            predicates.add(criteriaBuilder.and(criteriaBuilder.max(certificationStatuses.get(CertificationStatus_.id)))); 
            // It says Cannot resolve method 'and(javax.persistence.criteria.Expression<N>)'
            // Not also sure how to pass in parameter certificationStatusTypeId
        }

        // This is working
        if (dateFrom != null) {
            var offsetDateTimeFrom = OffsetDateTime.of(dateFrom, LocalTime.MIDNIGHT, ZoneOffset.ofHours(8));
            var dateTimeFrom = Date.from(offsetDateTimeFrom.toInstant());
            var offsetDateTimeTo = OffsetDateTime.of(Objects.requireNonNullElse(dateTo, dateFrom), LocalTime.MAX, ZoneOffset.ofHours(8));
            var dateTimeTo = Date.from(offsetDateTimeTo.toInstant());
            predicates.add(criteriaBuilder.and(criteriaBuilder.between(
                    root.get(Certification_.createdAt),
                    criteriaBuilder.literal(dateTimeFrom),
                    criteriaBuilder.literal(dateTimeTo)))
            );
        }

        // This is working
        if (client != null && !client.isBlank()) {
            predicates.add(criteriaBuilder.and(criteriaBuilder.like(criteriaBuilder.lower(root.join(Certification_.client).get(Client_.name)), "%" + client.toLowerCase() + "%")));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    });
    return certifications.stream()
            .map(this.certificationMapper::fromEntityToDto)
            .collect(Collectors.toUnmodifiableList());
}

我的标准 certificationTypeId , dateFrom , dateTo ，和 client 你在工作。对于 certificationStatusTypeId ，我要基于以下sql语句创建条件：

SELECT * FROM certification_status WHERE id IN (SELECT MAX(id) FROM certification_status GROUP BY certification_id) and certification_status_type_id = ?;

此语句在数据库中起作用。但不确定这是否是选择记录所基于的查询的最佳组合 MAX(id) , GROUP BY certification_id 以及 WHERE certification_status_type_id = ? .
谢谢你的帮助。谢谢您。

**更新

这是我的代码：

if (certificationStatusTypeId != null && certificationStatusTypeId > 0) {
        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(criteriaBuilder.in(subRoot.get(CertificationStatus_.id)).value(subQuery)));
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal(subRoot.get(CertificationStatus_.certificationStatusType).get(CertificationStatusType_.id), certificationStatusTypeId)));
    }

但它会抛出以下错误：

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 )]

查看生成的jpql，它看起来与我的sql语句类似。我只是不明白为什么 Invalid path .

**更新我的道歉！我的sql语句实际上类似于：

select
    // props of Certification
from certification c
inner join certification_type ct on ct.id = c.certification_type_id
inner join client cl on cl.certification_id = c.id
inner join certification_status cs on cs.certification_id = c.id
inner join certification_status_type cst on cst.id = cs.certification_status_type_id
where cs.id in (select max(cs2.id) form certification_status cs2 group by cs2.certification_id) and cs.certification_status_type_id = ?;

更新
这就解决了。多亏了 Thorben Janssen 感谢你耐心地调查我的问题。
这是我代码的最后一部分：

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)));
    }