英文:
JPA Repository : Cannot bind parameter for timezone
问题
@Query(
value =
"SELECT EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone ?4) AS hour,\n"
+ " COUNT(STO.id) AS count, SUM(STO.grandtotalprice) AS sum, AVG(STO.grandtotalprice) AS average\n"
+ "FROM store.storeorder AS STO\n"
+ "WHERE STO.store_id=?1 AND STO.createddate >= ?2 AND STO.createddate < ?3 AND STO.orderstatus IN ('CLOSED')\n"
+ "GROUP BY EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone ?4) \n"
+ "ORDER BY hour ASC;",
nativeQuery = true)
List<ReportHourly> hourlyReport(
UUID storeId, LocalDateTime from, LocalDateTime to, String timeZone);
英文:
I have the following code in JPA repository and it works.
@Query(
value =
"SELECT EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone 'Asia/Seoul') AS hour,\n"
+ " COUNT(STO.id) AS count, SUM(STO.grandtotalprice) AS sum, AVG(STO.grandtotalprice) AS average\n"
+ "FROM store.storeorder AS STO\n"
+ "WHERE STO.store_id=?1 AND STO.createddate >= ?2 AND STO.createddate < ?3 AND STO.orderstatus IN ('CLOSED')\n"
+ "GROUP BY EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone 'Asia/Seoul') \n"
+ "ORDER BY hour ASC;",
nativeQuery = true)
List<ReportHourly> hourlyReport(
UUID storeId, LocalDateTime from, LocalDateTime to);
However, when I try to input timezone as parameter like below, it fails with saying
org.postgresql.util.PSQLException: ERROR: column "createddate" must appear in the GROUP BY clause or be used in an aggregate function
@Query(
value =
"SELECT EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone ?4) AS hour,\n"
+ " COUNT(STO.id) AS count, SUM(STO.grandtotalprice) AS sum, AVG(STO.grandtotalprice) AS average\n"
+ "FROM store.storeorder AS STO\n"
+ "WHERE STO.store_id=?1 AND STO.createddate >= ?2 AND STO.createddate < ?3 AND STO.orderstatus IN ('CLOSED')\n"
+ "GROUP BY EXTRACT(HOUR FROM STO.createddate\\:\\:timestamptz at time zone ?4) \n"
+ "ORDER BY hour ASC;",
nativeQuery = true)
List<ReportHourly> hourlyReport(
UUID storeId, LocalDateTime from, LocalDateTime to, String timeZone);
I am not sure why parameterization doesn't work for this case.
答案1
得分: 0
这里的问题在于 JDBC 并不真正了解索引绑定参数，它只知道用于绑定的 ?。
这意味着两个 ?4 的出现会被翻译为两个不同的绑定参数，因此 Postgres 在 select 子句中看到一个表达式，该表达式既不是 GROUP BY 的一部分，也不是聚合函数。
由于根据构造，这两者实际上是相同的，所以您可以将小时表达式包装在 MAX(...) 或任何其他聚合函数中，该函数在应用于单行时返回参数值，这样应该就没问题了。
英文:
I think the problem here is that JDBC doesn't really know about indexed bind arguments, it only knows about ? for binding.
This means the two occurrences fo ?4 get translated into two different bind parameters and therefore Postgres sees an expression in the select clause that is not part of the GROUP BY nor an aggregate function.
Since by construction the two actually are the same you should be fine wrapping the expression for hour in MAX(...) or any other aggregate function that returns the argument value when applied to a single row.
专注分享java语言的经验与见解，让所有开发者获益！
评论