title: JPA Criteria API - Add days to DATE using ORACLE
body: <p>I'm trying to use JPA Criteria API to filter a date between a specific range. </p>
  
  <p>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 .</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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') 
  </code></pre>
  
  <p>In <code>CriteriaBuilder</code> I make some attempts, but I think the best approach I try to use is the follow:</p>
  
  <pre><code> CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
   CriteriaQuery&lt;Object&gt; criteria = criteriaBuilder.createQuery(Object.class);
   List&lt;Predicate&gt; 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&lt;Object&gt; query = entityManager.createQuery(criteria);
    return query.getResultList();
  </code></pre>
  
  <p>I use <code>function("addDate"...)</code> 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the query trace:</p>
  
  <p><code>(AddDate(t0.CREATION_DATE, ?, t0.EXPIRATION_DAYS) BETWEEN ? AND ?))</code>.</p>
  
  <p>How can I achieve this using ORACLE as a database? Or there is a better approach?</p>