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?

Solution

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

Comments

[alexander]

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]

My query looks like: ....where (transactio0_.date>=? or transactio0_.date is null) and (transactio0_.date<=? or transactio0_.date is null)

[Tiny]

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]

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