我想在Postgres 9.4数据库系统上使用版本4.2.21中的Hibernate通过JPA 2.0执行本机SQL查询.
基本上,根据我在stackoverflow上的最新帖子,我尝试将大量对象/记录放入"临时"桶中.
设置可以简化为以下设置,其中包含带有id字段和给定时间戳的表"MyObject":
CREATE TABLE myobject ( id bigint NOT NULL, lastseen timestamp without time zone, )
我应该执行查询的代码是这样的:
Query q = getEntityManager().createNativeQuery( "select count(id),date_part('day', :startDate - c.lastseen) AS " + "difference from myobject c " + "group by date_part('day', :startDate - c.lastseen) order by difference asc"); q.setParameter("startDate", startDate); List
通过pgAdmin3使用示例日期执行此查询将按预期返回结果.
但是,如果我尝试通过Hibernate执行相同的查询作为本机查询,它会失败并出现以下异常:
Caused by: org.postgresql.util.PSQLException: FEHLER: column „myobject.lastseen“ must appear in the group by clause or be used in an aggregate function Position: 40 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:305) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) ... 94 more
这个异常似乎是有效且自我解释但为什么我可以通过PgAdmin3执行相同的查询?Hibernate SQL解析器比pgAdmin3更严格还是补偿了一些错误?
那么我的SQL查询如何制定以便通过Hibernate使其可执行?
编辑:
由于某种原因,以下SQL语句(带有显式子选择)通过PgAdmin3以及Hibernate工作:
select count(id), difference from (select c.id,c.lastseen,date_part('day', :startDate - c.lastseen) AS difference from myobject c) AS temporalBucket group by difference order by difference asc
但是这仍然没有回答给定代码片段中先前查询的问题.
此查询也应该有效,没有子查询:
SELECT count(id) -- or even better: count(*) AS ct
, date_part('day', :startDate - c.lastseen) AS difference
FROM myobject c
GROUP BY difference
ORDER BY difference;
我怀疑的原因是:Hibernate使用预处理语句,并且两次出现:startDate
作为两个参数传递.这样,Postgres不能假设两个表达式(在SELECT
列表中和中GROUP BY
)都是相同的......
使用等效的SQL命令PREPARE
演示,这适用:
PREPARE test1 AS
SELECT count(*) AS ct
, date_part('day', $1 - c.lastseen) AS difference
FROM myobject c
GROUP BY date_part('day', $1 - c.lastseen)
ORDER BY difference;
虽然这不是:
PREPARE test2 AS
SELECT count(*) AS ct
, date_part('day', $1 - c.lastseen) AS difference
FROM myobject c
GROUP BY date_part('day', $2 - c.lastseen)
ORDER BY difference;
..并引发与你展示的相同的异常.
您可以使用我建议的查询先验地避免此问题.
有关:
PostgreSQL在哪里计算条件