Use Hibernate Criteria API to return first row of each group

Question

I am new to Hibernate and I am trying to write a criteria query to return the latest status of employee on a given date

id  |   Status  | status_date
1   |   Active  |   1/10/2017
2   |   Active  |   1/10/2017
...
1   |   Inactive|   5/10/2017
...
1   |   Active  |   9/10/2017

So I will be passing a date to the query and want to find the latest status of every employee on that date The expected result will be something like this

Example: For date 6/1/2017, this will be the returned data

id  |   Status  |   Date
1   |   Inactive|   5/10/2017
2   |   Active  |   1/10/2017

I was able to add group by with id and order the rows by status date in descending order. Is there a way I can select only the top row for each group? I tried to use the max function on status_date but that does not work.

CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = builder.createQuery(Employee);
Root<Employee> root = cq.from(Employee.class);
cq.select(root);
cq.groupBy(root.get("id"));
cq.orderBy(builder.desc(root.get("status_date")));
cq.having(builder.max(root.get("status_date")));

Answer 1

Since you want to output aggregation, not use aggregation as condition so you should not place it in having clause. You must add the aggregation to selection list instead.

First you must create aggregation result class (It's usual to different to your entity class):

public static class StatusEntityResult {
    private String userId;
    private String status;
    private Date statusDate;
    // getter, setter, constructor with all parameters here
}

Then create a query using it as result:

public List<StatusEntityResult> queryStatus() throws ParseException {
    // Date condition
    Date targetDate = new SimpleDateFormat("yyyy-MM-dd").parse("2017-10-06");

    CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();

    // Use StatusEntityResult as result
    CriteriaQuery<StatusEntityResult> cq = builder.createQuery(StatusEntityResult.class);
    Root<Employee> root = cq.from(Employee.class);

    // Select `id`, `status` and `max(status_date)`
    cq.multiselect(root.get("id"), root.get("status"), builder.max(root.get("statusDate")))
            .where(builder.lessThanOrEqualTo(root.get("statusDate"), targetDate))
            .groupBy(root.get("id"))
            .orderBy(builder.desc(root.get("statusDate")));

    return this.entityManager.createQuery(cq).getResultList();
}

And the result is:

Side note

From what you wrote, you was attempting to use JPA Criteria, not Hibernate Criteria. For Hibernate Criteria solution, you can try to read @default locale suggestion