如何解决Spring Data JPA执行PostgreSQL原生查询报syntax error at or near ":"错误

问题根因 #

• PostgreSQL的::类型转换符和Hibernate参数解析规则冲突：Hibernate会将SQL中的单冒号:识别为命名参数前缀，'00:00'::time中的冒号会被误解析为参数占位符，导致SQL语法被篡改，触发SQLGrammarException。
• 返回值类型不匹配：SQL的第一个返回字段为TIME类型，第二个为INT类型，但方法定义的返回值是List<Map<String, Integer>>，无法将TIME类型映射为Integer，也会导致结果集提取失败。

解决方法 #

1. 替换类型转换语法规避冒号冲突 #

将'00:00'::time替换为兼容性更好的CAST('00:00' AS time)写法，完全避免冒号识别冲突，也可以选择将::转义为\\:\\:，但CAST写法可读性更高。

2. 调整返回值类型定义 #

两种可选方案：

• 若需要保留完整的小时时间字段，将方法返回值改为List<Map<String, Object>>适配不同类型的返回值；
• 若仅需要小时序号和统计数量，直接返回g.h作为小时字段，两个返回值均为数字，可保留List<Map<String, Integer>>的返回定义。

修改后代码示例 #

保留完整时间返回版本 #

@Query(
        value = "SELECT CAST('00:00' AS time) + g.h * CAST('1 hour' AS INTERVAL) AS hour_time, count(sv.id) AS orders " +
                "FROM generate_series(0, 23, 1) g(h) " +
                "LEFT JOIN paymentvirtualization.summery_virtualizer sv " +
                "ON extract(hour FROM sv.last_updated) = g.h " +
                "AND date_trunc('day', sv.last_updated) = '2019-10-11' " +
                "GROUP BY g.h ORDER BY g.h",
        nativeQuery = true
)
List<Map<String, Object>> getPaymentDistry();

仅返回小时序号和统计量版本 #

@Query(
        value = "SELECT g.h AS hour, count(sv.id) AS orders " +
                "FROM generate_series(0, 23, 1) g(h) " +
                "LEFT JOIN paymentvirtualization.summery_virtualizer sv " +
                "ON extract(hour FROM sv.last_updated) = g.h " +
                "AND date_trunc('day', sv.last_updated) = '2019-10-11' " +
                "GROUP BY g.h ORDER BY g.h",
        nativeQuery = true
)
List<Map<String, Integer>> getPaymentDistry();

动态统计日期扩展版本 #

如果需要动态传入统计日期，避免SQL注入风险，可使用参数占位符写法：

@Query(
        value = "SELECT g.h AS hour, count(sv.id) AS orders " +
                "FROM generate_series(0, 23, 1) g(h) " +
                "LEFT JOIN paymentvirtualization.summery_virtualizer sv " +
                "ON extract(hour FROM sv.last_updated) = g.h " +
                "AND date_trunc('day', sv.last_updated) = CAST(?1 AS DATE) " +
                "GROUP BY g.h ORDER BY g.h",
        nativeQuery = true
)
List<Map<String, Integer>> getPaymentDistry(String statDate);

内容的提问来源于stack exchange，提问作者Dasun