使用Hibernate Criteria API返回每个组的第一行

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

我是Hibernate的新手,我正在尝试编写一个条件查询来返回给定日期员工的最新状态

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

示例:对于日期6/1/2017,这将是返回的数据

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.

我能够使用id添加group by,并按状态日期按降序排列行。有没有办法我只能选择每组的顶行?我尝试在status_date上使用max函数,但这不起作用。

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

1 个解决方案

#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.

由于您希望输出聚合,因此不要将聚合用作条件,因此不应将其放在having子句中。您必须将聚合添加到选择列表中。

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

根据您的编写,您试图使用JPA Criteria,而不是Hibernate Criteria。对于Hibernate Criteria解决方案,您可以尝试阅读@default区域设置建议

#1