英文:
Call oracle method add_months using CriteriaQuery
问题
我有一个查询，我想使用 CriteriaQuery 来执行。
查询:
select * from MY_TABLE where updated_at <= add_months(TRUNC(SYSDATE) + 1, -3)
基本上是在表中查找所有 update_at 日期早于3个月的记录。
我尝试了这个博客中介绍的方法。
问题是我有一个参数 -3，但我期望的返回类型是 Date。
代码:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(MyTable.class);
Root<MyTable> root = cq.from(MyTable.class);
Expression<Date> truncExpr = cb.function("TRUNC", Date.class, cb.currentTimestamp());
Expression<Date> addMonthsExpr = cb.function("add_months", Date.class, truncExpr, months); // <----Compilation error.
Predicate datePredicate = cb.lessThanOrEqualTo(root.get("updated_at"), addMonthsExpr);
请提供建议。
英文:
I've a query that I would like to execute using CriteriaQuery.
Query:
select * from MY_TABLE where updated_at <= add_months(TRUNC(SYSDATE) + 1, -3)
It is basically finding all records in table whose update_at date is earlier than 3 months.
I tried the approach presented at this blog.
Issue is I've -3 as the parameter, but my expected return type is Date.
Code:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(MyTable.class);
Root<MyTable> root = cq.from(MyTable.class);
Expression<Date> truncExpr = cb.function("TRUNC", Date.class, cb.currentTimestamp());
Expression<Date> addMonthsExpr = cb.function("add_months", Date.class, truncExpr, months); // <----Compilation error.
Predicate datePredicate = cb.lessThanOrEqualTo(root.get("updated_at"), addMonthsExpr);
Please suggest.
答案1
得分: 1
months变量的定义没有显示，但我猜它是一个整数，例如：
int months = -3; // 或者从方法参数中获取，类型为int
正确的方法如下（除非你知道这个方法，否则不太直观）：
Expression<Integer> months = cb.literal(-3);
英文:
The definition of the months variable is not displayed, but my guess is that it is an integer, e.g.:
int months = -3; // or from a method argument of type int anyway
The correct way is the following (unintuitive unless you know about it):
Expression<Integer> months = cb.literal(-3);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论