java - JPA Criteria 查询分组依据并获取最后一条记录

Question

该实体员工

@Entity
@Table(name="employee")
@IdClass(EmployeeKey.class)
public class Employee implements Serializable{
    private static final long serialVersionUID = -4060941180859761486L;

    @Id @Column(name ="DATA_DT")
    private Date registDate;
    @Id @Column(name ="EMPE_NO")
    private String empeId;

    @Column(name ="TTL_NM_TH")
    private String titleNameTh;
    @Column(name ="TTL_NM_EN")
    private String titleNameEn;
    @Column(name ="EMPE_NM_TH")
    private String empeNameTh;
    @Column(name ="EMPE_LST_NM_TH")
    private String empeLastNameTh;
    @Column(name ="EMPE_POS")
    private String empePosition;


    @ManyToOne
    @JoinColumn(name = "DEPT_CODE" , nullable = true)
    @NotFound(action = NotFoundAction.IGNORE)
    private OrganizationUnit empeDeptCode;


//get set

}

this entity ou

@Entity
@Table(name="ou")
public class OrganizationUnit implements Serializable{
    private static final long serialVersionUID = -4038118923332034407L;
    @Id 
    @Column(name ="OU_CODE")
    private String ouCode;
    @Column(name ="OU_TP_CODE")
    private String ouTpCode;
    @Column(name ="OU_NM")
    private String ouName;
    @Column(name ="BR_GRP")
    private String ouBusinessGroup;
    @Column(name ="GRP_OU_CODE")
    private String groupOUCode;
    @Column(name ="SCTR_OU_CODE")
    private String sctrOUCode;
    @Column(name ="DEPT_OU_CODE")
    private String departmentOUCode;
    @Column(name ="EFF_DT")
    private Date effectiveDate;
    @Column(name ="END_DT")
    private Date endDate;

//get set

}

现在我创建一个像这样的条件查询。我尝试从 criteria-Api 创建搜索功能。我的表employee有两个id，每天我都会在employee中插入数据。搜索功能需要显示最后一条记录。

public List<Employee> findByAvanceSearch(SearchEmpRequestModel data) {
    // TODO Auto-generated method stub
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);


    Root<Employee> employee = cq.from(Employee.class);
    List<javax.persistence.criteria.Predicate> predicates = new ArrayList<>();

    if(data.getEmpNumber() != null) {

        //predicates.add( cb.and(cb.equal(employee.get("empeId"), data.getEmpNumber()), cb.greatest(employee.get("registDate"))));
        predicates.add(cb.equal(employee.get("empeId"), data.getEmpNumber()));
    }
    if(data.getEmpThainame()!=null) {

        predicates.add(cb.like(employee.get("titleNameTh"), "%"+data.getEmpThainame()+"%"));
    }
    if(data.getEmpDept()!=null) {

        predicates.add(cb.like(employee.get("empeDeptCode").get("departmentOUCode"), "%"+data.getEmpDept()+"%"));

    }
    if(data.getEmpGrp()!=null) {

        predicates.add(cb.like(employee.get("empeDeptCode").get("sctrOUCode"), "%"+data.getEmpGrp()+"%"));

    }
    if(data.getEmpSctr()!=null) {

        predicates.add(cb.like(employee.get("empeDeptCode").get("groupOUCode"), "%"+data.getEmpSctr()+"%"));

    }


    cq.orderBy(cb.asc(employee.get("registDate"))).groupBy(employee.get("empeId")).where((javax.persistence.criteria.Predicate[]) predicates.toArray(new javax.persistence.criteria.Predicate[0]));
    return em.createQuery(cq).getResultList();
}

我需要从分组中获取最后一条记录，但从这段代码中我得到了第一条记录。任何人都知道我应该在 hibernate 标准中使用什么？谢谢。

Answer 1

使用子查询通过使用 max 和 groupby 来查找 id，希望对某人有帮助

subquery.select(cb.max(subroot.get("id")))
        .where(predicates.stream().toArray(Predicate[]::new))
        .groupBy(subRoot.get("empid"));