[FIXED] JPA2 / Hibernate query: using Date as a query param ignoring the "time" part

Issue

I want to select some records of a criteria query based on a date field:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery();
Root<Macchinario> from = cq.from(Macchinario.class);
cq.select(from);

cq.where(
                    cb.and(
                        cb.like(from.<String>get("marca"), "%"+ricerca.getMarca()+"%"),
                        cb.like(from.<String>get("modello"), "%"+ricerca.getModello()+"%"),
                        cb.like(from.<String>get("matricola"), "%"+ricerca.getMatricola()+"%"),

                        cb.equal(from.get("dataInserimento"), ricerca.getDataInserimento())
                    )
                );

dataInserimento is a java.util.Date

The "Macchinario" I'm looking for has "2012-05-23 10:16:00" in the db.

ricerca.getDataInserimento() returns "2012-05-23 00:00:00".

How can I pass that parameter, telling jpa to ignore the "time part" of the Date?

Solution

You could write an util and use it to truncate the time part from the date:

DateHelper.java

public static Date getDateWithoutTime(Date date) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(date);
    cal.set(Calendar.HOUR_OF_DAY, 0);
    cal.set(Calendar.MINUTE, 0);
    cal.set(Calendar.SECOND, 0);
    cal.set(Calendar.MILLISECOND, 0);
    return cal.getTime();
}

And then change

cb.equal(from.get("dataInserimento"), ricerca.getDataInserimento())

to

cb.equal(from.get("dataInserimento"), 
         DateHelper.getDateWithoutTime(ricerca.getDataInserimento()))

Update

Truncating time part from the value we get from db seems impossible to do with out-of-box functionality provided by JPA or Hibernate. Hibernate provide extraction of year, month and day values from the date column though, which we are going to make us of.

Calendar dateCalendar = Calendar.getInstance();
dateCalendar.setTime(ricerca.getDataInserimento());

Path<Date> dataInserimento = from.get("dataInserimento");
Predicate timelessPredicate = cb.and(
        cb.equal(cb.function("year", Integer.class, dataInserimento), dateCalendar.get(Calendar.YEAR)),
        cb.equal(cb.function("month", Integer.class, dataInserimento), dateCalendar.get(Calendar.MONTH) + 1),
        cb.equal(cb.function("day", Integer.class, dataInserimento), dateCalendar.get(Calendar.DATE)));

cq.where(..., timelessPredicate);

What we did here, we compared year, month and day values from database with provided input date separately with help of hibernate functions and calendar power.

This will do the trick.

Answered By - JMelnik