JPA Criteria API - Add days to DATE using ORACLE

Question

I'm trying to use JPA Criteria API to filter a date between a specific range.

The problem is that I don't have directly the date in my entity, I obtain the date using a date field and adding a number of days (which are in another field) to this date .

I look around and I saw some responses which recommends to calculate the date in java before pass it to criteria builder, but the problem is that I don't know the number of days to add because it depends on the days in a specific column of the table.

The simplified table OBJECT has a field CREATION_DATE of type date and EXPIRATION_DAYS which is a number. I want to filter by a range of possible expiration date, I get the expiration date of each element in OBJECT table adding EXPIRATION_DAYS to CREATION_DATE, the SQL query is:

select * from OBJECT
where CREATION_DATE + EXPIRATION_DAYS 
between to_date('01/01/2018','dd/MM/yyyy') and to_date('01/02/2018','dd/MM/yyyy')

In CriteriaBuilder I make some attempts, but I think the best approach I try to use is the follow:

 CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
 CriteriaQuery<Object> criteria = criteriaBuilder.createQuery(Object.class);
 List<Predicate> predicates...
 predicates.add(criteriaBuilder.between(
        criteriaBuilder.function("AddDate", Date.class, Object.get("creationDate"), criteriaBuilder.literal("day"), Object.get("expirationDays")), 
        dateInit, dateEnd));
 criteria.where(predicates.toArray(new Predicate[]{}));        
 ...
  TypedQuery<Object> query = entityManager.createQuery(criteria);
  return query.getResultList();

I use function("addDate"...) method, the problem is that I thought JPA implementation translate "addDate" to specific database vendor but it's not, because it's throwing the following exception which is pretty clear:

javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: "ADDDATE": invalid identifier

And the query trace:

(AddDate(t0.CREATION_DATE, ?, t0.EXPIRATION_DAYS) BETWEEN ? AND ?)).

How can I achieve this using ORACLE as a database? Or there is a better approach?

`function` passes things straight through, since it is invoking an explicit SQL function. — , Aug 09 '18 at 17:14
@BillyFrost reading some documentation at first I though that there was some name function that using `function` will be translated to specific vendor function... however you're right about how `function`, thanks `:)` — albciff, Aug 14 '18 at 12:50

albciff · Answer 1 · 2018-08-14T14:07:58.717

In Hibernate seems that there is the @Formula annotation which provides the functionality I'm looking for, however there is no such thing in JPA neither in specific implementation I'm using(eclipselink).

I want to avoid the necessity of declare a specific function, but unfortunately I do it this way, first I declare the function ADD_DAYS in Oracle as follows:

create or replace function ADD_DAYS(dateValue DATE, daysValue NUMBER)
return DATE is
begin
    return dateValue + daysValue;
end;

Then I call this function using function method of criteriaBuilder, and passing as parameters the function name, the type of object returned by the oracle function, and the two arguments I define, the column with the date, and the column with the days to add:

criteriaBuilder.function("ADD_DAYS", Date.class, Object.get("creationDate"), Object.get("expirationDays"))

JPA Criteria API - Add days to DATE using ORACLE

1 Answers1