postgresql - 使用 JPA 的 Criteria API 按日期间隔分组

Question

我正在尝试使用 JPA 的 Criteria API 按日期间隔对实体进行分组。我使用这种查询实体的方式，因为这是服务 API 请求的服务的一部分，这些请求可能会要求任何实体的任何字段，包括排序、过滤、分组和聚合。除了按日期字段分组外，一切正常。我的底层 DBMS i PostgreSQL。

举一个最小的例子，这是我的实体类:

@Entity
@Table(name = "receipts")
public class DbReceipt {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private Date sellDate;
    // Many other fields
}

这个例子讨论了对我的“月”间隔进行分组(因此按年+月分组)，但最后我正在寻找一种可以让我按任何间隔分组的解决方案，例如“年”、“日”或“分钟” ”。

我想要实现的是以下查询，但使用 Criteria API:
SELECT TO_CHAR(sell_date, 'YYYY-MM') AS alias1 FROM receipts GROUP BY alias1;
我这样做的尝试是这样的:

@Service
public class ReceiptServiceImpl extends ReceiptService {
    @Autowired
    private EntityManager em;

    @Override
    public void test() {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
        Root<?> root = query.from(DbReceipt.class);
        Expression<?> expr = cb.function("to_char", String.class, root.get("sellDate"), cb.literal("YYYY-MM"));

        query.groupBy(expr);
        query.multiselect(expr);

        TypedQuery<Object[]> typedQuery = em.createQuery(query);
        List<Object[]> resultList = typedQuery.getResultList();
    }
}

我使用的原因 to_char功能而不是 MONTH类似的是我需要像 2019-05 这样的实体和 2020-05不能组合在一起。我还将这个例子缩小到只有年和月以保持简短，但目标是按任何日期间隔分组。

上面的代码创建了以下查询(启用了 SQL 日志记录)，这会导致错误:

Hibernate: select to_char(dbreceipt0_.sell_date, ?) as col_0_0_ from receipts dbreceipt0_ group by to_char(dbreceipt0_.sell_date, ?)
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] WARN  o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 0, SQLState: 42803
24-05-2020 12:16:30.071 [http-nio-1234-exec-5] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions - ERROR: column "dbreceipt0_.sell_date" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 16

对我来说，这是由于整个表达式被放入查询的“group by”部分，而不仅仅是别名。现在，我尝试为表达式分配一个别名(它返回 Selection<T> 并且 groupBy 接受表达式，因此我只能在 multiselect 中真正使用它)，但这并没有影响查询的执行方式 -没有改变。

如何使用 Criteria API 实现上述按年和月分组？也许除了使用 to_char 之外还有其他方法?也许有一种方法可以为 groupBy 赋予别名会导致它按别名而不是整个表达式分组的方法？

Answer 1

我认为这是 PostgreSQL 中的一个错误(错误来自那里，而不是来自 Hibernate)。我已经使用 EclipseLink + Derby 尝试了对您的代码稍加修改的版本，并且运行良好。
请注意，我必须使用数字而不是字符串，因为 Derby DB 没有等效的 TO_CHAR功能。

Expression<Integer> year = cb.function("YEAR", Integer.class, root.get("sellDate"));
Expression<Integer> month = cb.function("MONTH", Integer.class, root.get("sellDate"));
Expression<Integer> expr = cb.sum(month, cb.prod(12, year));
query.groupBy(expr);
query.multiselect(expr);

这将返回以下 SQL:

SELECT (MONTH(MY_DATE) + (12 * YEAR(MY_DATE))) 
FROM MY_DATE_TABLE 
GROUP BY (MONTH(MY_DATE) + (12 * YEAR(MY_DATE)))

请注意，在 JPA 条件查询中没有用于操作日期的可移植解决方案。如果要同时查询的组数不太高，我会采用更实用的方法，您可以在 Java 中找到日期并将它们作为文字传递给查询构建器。

另一种解决方法是使用 groupBy(root.get("sellDate")) 查询然后根据所需的时间段在 Java 中聚合结果。

Post Scriptum:我认为它不相关，但是我修改了查询的返回类型 Object[]至 Object .