在JPA中按截断日期分组 [英] Group by trunced date in JPA
问题描述
我需要帮助!
我需要为
SELECT date_trunc('day', start_time)
FROM Example
GROUP BY date_trunc('day', start_time)
(PostgreSQL)
(PostgreSQL)
我有代码:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Example> root = query.from(Example.class);
Expression<Date> exp = cb.function("date_trunc", Date.class , cb.literal("day"), root.get("startTime"));
List<Object[]> result = entityManager.createQuery(query.select(exp).groupBy(exp)).getResultList();
但我得到了认可:
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2122)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1905)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)
at org.hibernate.loader.Loader.doQuery(Loader.java:925)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
at org.hibernate.loader.Loader.doList(Loader.java:2622)
at org.hibernate.loader.Loader.doList(Loader.java:2605)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)
at org.hibernate.loader.Loader.list(Loader.java:2429)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1339)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
... 122 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: column "examplei0_.start_time" must appear in the GROUP BY clause or be used in an aggregate function
Position: 69
我该怎么办?
休眠SQL:
select date_trunc(?, examplei0_.start_time) as col_1_0_ from examplei0_ group by date_trunc(?, examplei0_.start_time)
如果我在数据库控制台中执行它,它将起作用.
It works if I execute it in Database console.
推荐答案
我猜Postgres看到了
I'm guessing Postgres sees
select date_trunc(?, examplei0_.start_time) as col_1_0_ from examplei0_ group by date_trunc(?, examplei0_.start_time)
并拒绝,因为第一个date_trunc(?, examplei0_.start_time)
不必与第二个date_trunc(?, examplei0_.start_time)
相同,而无需查看传入的实际参数.
And rejects because the first date_trunc(?, examplei0_.start_time)
is not necessarily the same as the second date_trunc(?, examplei0_.start_time)
without looking at the actual arguments passed in.
在这种情况下,您需要休眠以生成未参数化'day'
的查询.或者,在postgres date_trunc_day(timestamp)
中创建一个调用date_trunc('day', timestamp)
的函数,然后调用新函数.
If that's the case, you'd need hibernate to generate a query where 'day'
is not parametrized. Alternatively, create a function in postgres date_trunc_day(timestamp)
that calls date_trunc('day', timestamp)
and call the new function instead.
这篇关于在JPA中按截断日期分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!