Date comparison using the JPA criteria API

Question

I got a range date picker with two dates: start and end, where both can be empty. I would like to filter a table, where the entity has exact one date: date.

So, here are some examples. I'd like to match. Imaging the date to match is the current date (17/07/2016).

null - 17/07/2016 -> match
17/07/2016 - null -> match
17/07/2016 - 17/07/2016 -> match
null - null -> match all

Those are the edge cases in my opinion and the reason why I am struggling so much.

Actually my code looks like:

CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Transaction> cq = cb.createQuery(Transaction.class);
Root<Transaction> root = cq.from(Transaction.class);

List<Predicate> predicates = new ArrayList<>();

Predicate startPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), start);
Predicate endPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), end);

predicates.add(startPredicate);
predicates.add(endPredicate);
cq.select(root).where(predicates.toArray(new Predicate[] {}));

TypedQuery<Transaction> query = getEm().createQuery(cq);
query.setFirstResult(firstRow);
query.setMaxResults(maxRow);
List<Transaction> resultList = query.getResultList();

I'd like to get a query like this:

SELECT * FROM transaction
WHERE ((cast(date AS DATE) >= '2016-07-16' OR cast(date AS DATE) IS NULL))
       AND ((cast(date AS DATE) <= '2016-07-17' OR cast(date AS DATE) IS NULL))

Please note: The static date is to simulate a start and end date. and date is the table column.

I know that my code is wrong. It matches only ranges, without considering null values. Also, if start and end is the same day, I will get zero results.

Do you have you any idea? How can I edit my code to match all the mentioned patterns?

Please try to construct the corresponding SQL query first on the back-end database directly producing the expected result. For example, do you want to display all rows between start date (inclusive / exclusive) and end date (inclusive / exclusive) including null values in those columns like `SELECT * FROM table_name WHERE (cast(start_date AS DATE) > '07/17/2016' OR cast(start_date AS DATE) IS NULL) AND (cast(end_date AS DATE) < '07/20/2016' OR cast(end_date AS DATE) IS NULL)` (or using the `BETWEEN` clause)? It would then be easier and clearer to fabricate the corresponding criteria query. — Tiny, Jul 17 '16 at 19:29
Sorry mate, I did not think about that option. In my head was only criteria api, because I thought the query is to complicated (in my head).Your query looks quite good so far. I added an update to my question. Would you mind to have a look? :) — alexander, Jul 17 '16 at 20:01

score 15 · Accepted Answer · answered Jul 17 '16 at 21:31

I have an existing database table named discount with two columns of type TIMESTAMP named discount_start_date and discount_end_date in a MySQL database. So, please adjust your query according to the name of the table and respective columns in that table.

The complete criteria query based on the SQL statement given in the question can be constructed as follows (I hope the code would be self-explanatory).

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Discount> criteriaQuery = criteriaBuilder.createQuery(Discount.class);
Root<Discount> root = criteriaQuery.from(entityManager.getMetamodel().entity(Discount.class));

SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date startDate = dateFormat.parse("24-02-2016");
java.util.Date endDate = dateFormat.parse("24-03-2016");

ParameterExpression<java.util.Date> parameter = criteriaBuilder.parameter(java.util.Date.class);

Predicate startDatePredicate = criteriaBuilder.greaterThanOrEqualTo(root.get(Discount_.discountStartDate).as(java.sql.Date.class), parameter);
Predicate endDatePredicate = criteriaBuilder.lessThanOrEqualTo(root.get(Discount_.discountEndDate).as(java.sql.Date.class), parameter);

Predicate startDateOrPredicate = criteriaBuilder.or(startDatePredicate, root.get(Discount_.discountStartDate).isNull());
Predicate endDateOrPredicate = criteriaBuilder.or(endDatePredicate, root.get(Discount_.discountEndDate).isNull());

Predicate and = criteriaBuilder.and(startDateOrPredicate, endDateOrPredicate);
criteriaQuery.where(and);

List<Discount> list = entityManager.createQuery(criteriaQuery)
        .setParameter(parameter, startDate, TemporalType.DATE)
        .setParameter(parameter, endDate, TemporalType.DATE)
        .getResultList();

It produces the following SQL query of your interest (both fields are inclusive as you stated).

select
    discount0_.discount_id as discount1_15_,
    discount0_.discount_code as discount2_15_,
    discount0_.discount_end_date as discount3_15_,
    discount0_.discount_percent as discount4_15_,
    discount0_.discount_start_date as discount5_15_ 
from
    project.discount discount0_ 
where
    (
        cast(discount0_.discount_start_date as date)>=? 
        or discount0_.discount_start_date is null
    ) 
    and (
        cast(discount0_.discount_end_date as date)<=? 
        or discount0_.discount_end_date is null
    )

Tested on Hibernate 4.3.6 final but average ORM frameworks should produce the same query without any modifications.

In this method setParameter(parameter, startDate, TemporalType.DATE), the last parameter i.e. TemporalType.DATE is only needed, if you have a column of type DATETIME or TIMESTAMP in your database and you want to compare dates ignoring the time portion of such columns. You can simply exclude that parameter, if your columns do not have a time portion like DATE (MySQL).

You can, if necessary, also use other date (time) handling APIs like java.time or JodaTime replacing Date along with SimpleDateFormat

This works fine so far. But it dit not match the case same day. I will always get an empty result set. Do you have any idea how to fix it? — alexander, Jul 18 '16 at 06:35
My query looks like: ....`where (transactio0_.date>=? or transactio0_.date is null) and (transactio0_.date<=? or transactio0_.date is null)` — alexander, Jul 18 '16 at 07:05
This query on a test table returns the correct result set : `SELECT * FROM test WHERE (start_date >= '2016/07/17' OR start_date IS NULL) AND (end_date <= '2016/07/17' OR end_date IS NULL)`. It returns rows matching the given dates in the respective columns including null rows, if any. Casting is completely unnecessary, if the type of both columns is `DATE` (thus, not `DATETIME` or `TIMESTAMP`). Thus, `TemporalType.DATE` in the `setParameter()` method along with `Expression.as()` in those two predicates can safely be ignored and they have no visible effect in getting the result set in this case — Tiny, Jul 18 '16 at 10:15
Both the columns in the `test` table namely `start_date` and `end_date` are of type `DATE` in MySQL. Therefore, the given criteria query is expected to work as intended (again, casting is unnecessary, if the type of the columns is really `DATE`. Just exclude `TemporalType.DATE` along with `Expression.as()`. I added them because I tested on an existing table having columns of type `TIMESTAMP`). — Tiny, Jul 18 '16 at 11:02

Date comparison using the JPA criteria API

1 Answers1

Linked