PostgreSQL含::类型转换查询在Spring Data JPA用占位符报错求解决

问题描述 #

原PostgreSQL查询语句：

select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                    +'6d 18:06:56'::interval as created_at,
       count(*)filter(where status='ACTIVE') as active,
       count(*)filter(where status='BLOCKED') as blocked
from companies
where now()-'12 months'::interval < created_at
group by 1 order by 1;

尝试在Spring Data JPA仓库中使用时的代码：

@Query(value = """
            select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                   +'6d 18:06:56'::interval as created_at,
                                   count(*)filter(where status='ACTIVE') as active,
                                   count(*)filter(where status='BLOCKED') as blocked
                            from companies
                            where now()- :months + ' months'::interval < created_at
                            group by 1 order by 1
            """, nativeQuery = true)
List<Result> companiesMonths(@Param("months") Integer months);

出现错误：

SQL Error: 0, SQLState: 42601
ERROR: syntax error at or near ":"

错误原因 #

错误出现在条件语句now()- :months + ' months'::interval < created_at：PostgreSQL无法识别这种整数参数与interval字符串直接拼接的语法，必须明确构造合法的interval类型才能进行时间运算。

正确实现方式 #

方式一：字符串拼接构造interval #

通过||操作符将整数参数转为文本后拼接成interval格式字符串，再转为interval类型：

@Query(value = """
            select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                   +'6d 18:06:56'::interval as created_at,
                                   count(*)filter(where status='ACTIVE') as active,
                                   count(*)filter(where status='BLOCKED') as blocked
                            from companies
                            where now() - (:months || ' months')::interval < created_at
                            group by 1 order by 1
            """, nativeQuery = true)
List<Result> companiesMonths(@Param("months") Integer months);

方式二：使用PostgreSQL内置函数make_interval #

直接通过make_interval函数构造月数对应的interval，语法更清晰：

@Query(value = """
            select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                   +'6d 18:06:56'::interval as created_at,
                                   count(*)filter(where status='ACTIVE') as active,
                                   count(*)filter(where status='BLOCKED') as blocked
                            from companies
                            where now() - make_interval(months := :months) < created_at
                            group by 1 order by 1
            """, nativeQuery = true)
List<Result> companiesMonths(@Param("months") Integer months);

方式三：Java端构造Interval参数传入 #

在调用方法时直接构造符合PostgreSQL要求的Interval对象（如org.postgresql.util.PGInterval或java.time.Period），避免在SQL中拼接：

// 仓库方法定义
@Query(value = """
            select date_trunc('month',created_at-'6d 18:06:56'::interval)
                                   +'6d 18:06:56'::interval as created_at,
                                   count(*)filter(where status='ACTIVE') as active,
                                   count(*)filter(where status='BLOCKED') as blocked
                            from companies
                            where now() - :period < created_at
                            group by 1 order by 1
            """, nativeQuery = true)
List<Result> companiesMonths(@Param("period") Period period);

// 调用示例
repository.companiesMonths(Period.ofMonths(12));

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