java - PostgreSQL 查询在 pgAdmin 中有效，但在 Java EclipseLink 中无效

Question

我有一个 SQL 计算每个日期截断的行数(月、日、小时)。想想历史图表。如果在 pgAdmin 中执行，查询工作正常，但在使用 EclipseLink 的 Java 中失败。

pgAdmin 查询:

SELECT date_trunc( 'hour', delivered_at ),
       COUNT(date_trunc( 'hour', delivered_at )) AS num 
FROM messages 
WHERE channel_type='EMAIL' 
  AND created_at>='2016-02-28 16:01:08.882' 
  AND created_at<='2016-02-29 16:01:08.882' 
GROUP BY date_trunc( 'hour', delivered_at );

JPQL 命名查询

SELECT FUNCTION('date_trunc', 'hour', m.deliveredAt ),
       COUNT(FUNCTION('date_trunc', 'hour', m.deliveredAt )) AS num 
 FROM Message m 
 WHERE m.channelType = :channelType 
   AND m.createdAt >= :fromDate 
   AND m.createdAt <= :toDate 
 GROUP BY FUNCTION('date_trunc', 'hour', m.deliveredAt )

EclipseLink调试日志:

SELECT date_trunc(?, delivered_at), COUNT(date_trunc(?, delivered_at)) FROM messages 
WHERE (((channel_type = ?) AND (created_at >= ?)) AND (created_at <= ?)) GROUP BY date_trunc(?, delivered_at)
bind => [hour, hour, EMAIL, 2015-12-27 00:00:00.0, 2015-12-27 00:00:00.0, hour]

错误:

ERROR: column "messages.delivered_at" must appear in the GROUP BY clause or be used in an aggregate function Position: 23

PostgreSQL 日志:

2016-03-01 13:22:08 CET ERROR: column "messages.delivered_at" must appear in the GROUP BY clause or be used in an aggregate function at character 23 2016-03-01 13:22:08 CET STATEMENT: SELECT date_trunc($1, delivered_at), COUNT(delivered_at) FROM messages WHERE (((channel_type = $2) AND (created_at >= $3)) AND (created_at <= $4)) GROUP BY date_trunc($5, delivered_at) 2016-03-01 13:22:08 CET LOG: execute S_2: SELECT 1

如果我从 EclipseLink 记录的查询中替换绑定(bind)变量并在 pgAdmin 中执行它，则查询有效。这是怎么回事？

编辑:澄清一下，它也可以使用 em.createNativeQuery。

Answer 1

PostgreSQL 可以有 trouble解析参数绑定(bind)，在这种情况下表现为带有参数内联工作的 native SQL，而 JPA 生成的默认为绑定(bind)参数的 SQL 失败。

一种解决方案是通过传递 "eclipselink.jdbc.bind-parameters" 来关闭参数绑定(bind)值为“false”作为特定查询的查询提示，或作为持久性单元属性以默认关闭所有查询的参数绑定(bind)。