[FIXED] JPA Criteria query group by and get last record

Issue

this entity employee

@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

}

Now i create a criteria query like this. i try to create search function from criteria-Api. my table employee has two id every day i will insert data in employee . search function need to show last record .

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

I need to get last record from group by but from this code i get frist record. Any one knows what should I use in the hibernate criteria .? Thank.

Solution

I think , This one will solve your problemGet record with max id, using Hibernate Criteria

Answered By - Cemalettin Altınsoy
Answer Checked By - Mildred Charles (JavaFixing Admin)