Postgres时间间隔Spring数据动态参数不起作用 [英] Postgres Interval Spring Data Dynamic Parameter not working
问题描述
我需要将动态参数传递给Spring Data Postgres本机查询。该参数在一个区间表达式中。
I need to pass in a dynamic param to a Spring Data Postgres Native Query. The param is in an interval expression.
在运行代码时,我从pgAdmin验证以下内容仅返回 1个结果(正确):
At the time of running the code, I verify from pgAdmin that the following returns 1 result only (correctly):
从callbacks_t中选择*,其中created_date< =(now()-间隔 18小时)
问题:
1)在Spring Data代码中,以下内容?1
表示法错误地返回 2个结果:
1) In the Spring Data code, the following ?1
notation returns 2 results incorrectly:
@Query(value="select * from recalls_t where created_date <= (now() - interval '?1 hour')",
nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(@Param("hours") int hours);
2)然后,我在此线程中尝试了以下解决方案: Postgres Interval无法使用本地spring数据JPA查询
2) Then I tried the solution in this thread: Postgres Interval not working with native spring data JPA query
他们说使用单位间隔的倍数。但这也会错误地返回 2个结果:
They said to use a multiple of a single-unit interval. But this also returns 2 results incorrectly:
@Query(value="select * from recalls_t where created_date <= (now() -
(interval '1 hour') * :hours)",
nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(@Param("hours") int hours);
我发现解决此问题的唯一方法是在Spring中硬编码数字 18数据查询。因此,现在没有动态参数了,我可以正确得到1个结果。
The only way I found to fix this is to hard-code the number "18" in my Spring Data query. So now there's no dynamic param, and I get 1 result correctly. None of the Dynamic Param implementations work.
推荐答案
尝试通过传递字符串吗?
Try by passing a String ?
@Query(value="select * from recalls_t where created_date <= (now() - interval ?1)",
nativeQuery=true)
public List<RecallsT> findActiveRecallsInLastHrs(String interval);
// ...
findActiveRecallsInLastHrs("1 hour");
这篇关于Postgres时间间隔Spring数据动态参数不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!