CODEWITHSUNDEEP
javaspringhibernatejpacriteria
reiley
reiley

Reputation: 3761

Call oracle method add_months using CriteriaQuery

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.

Upvotes: 1

Views: 435

Answers (1)

Nikos Paraskevopoulos
Nikos Paraskevopoulos

Reputation: 40308

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);

Upvotes: 1

Related Questions