如何使用 JPA @Query 调用 SQL dateAdd 方法 [英] How to call SQL dateAdd method using JPA @Query

问题描述

我必须搜索超过 6 个月的行.我已将时间(日期时间)作为表中的列之一插入.如果它是一个 sql 查询，它就像这样:-

I have to search for row, which is older than 6 months. I have insert time(datetime) as one of the column in my table. if it would have been a sql query, it is straight forward like this :-

select * from myTable where insertTime<=dateadd(month, -6, getDate());

但我使用的是 JPA，所以我不得不写这样的东西 :-

But I am using JPA, so I had to write something like this :-

select * from myTable where insertTime<=function('DATEADD',month, -6, function('getDate'));

我不知道如何在上面的查询中放置月份参数.

I am not sure how to put month paramater in above query.

我在上述查询中遇到以下错误

I am getting following error on above query

创建名为myRepository"的 bean 时出错:调用 init方法失败；嵌套异常是 java.lang.IllegalArgumentException:查询验证失败....

Error creating bean with name 'myRepository': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query ....

我应该如何在上面的查询中放置 month 字段?可以通过 JPA 吗?

How should I put month field in above query? Is it possible through JPA?

提前致谢.

推荐答案

感谢 SPeL 支持，您可以尝试使用以下技巧:

Thanks to SPeL support you can try to use the following trick:

public interface MyEntityRepo extends JpaRepository<MyEntity, Integer> {

    @Query("select e from MyEntity e where e.createdAt <= ?#{@myEntityRepo.sixMonthsBefore()}")
    List<MyEntity> getAllSixMonthsOld();

    default Instant sixMonthsBefore() {
        return Instant.now().minus(6, ChronoUnit.MONTHS);
    }
}

这篇关于如何使用 JPA @Query 调用 SQL dateAdd 方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！