Using JPA/Hibernate Criteria to pull between a date

Question

I am trying to using the following code to pull a list of Experience objects from a MySQL table. Each experience has a from datetime column and a to datetime column and I only want to pull rows where todays date falls in between the from and to.

I am using JPA 2.0 running off of Hibernate.

    Date currentDate = new Date();
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Experience> query = builder.createQuery(Experience.class);
    Root<Experience> root = query.from(Experience.class);
    builder.between(currentDate, root.get("from"), root.get("to"));
    return entityManager.createQuery(query).getResultList();

My issue is that builder.between() obviously wont allow me to pass a Date object.

Is there a better solution to my problem?

Answer 1

You can pass it as a parameter:

ParameterExpression<Date> d = builder.parameter(Date.class);
builder.between(d, root.<Date>get("from"), root.<Date>get("to")); 
return entityManager.createQuery(query)
    .setParameter(d, currentDate, TemporalType.DATE).getResultList(); 

Note that in this case you need to specify desired temporal type.

Also you can rely on the database's current date: builder.currentDate(), builder.currentTimestamp(), etc

Answer 2

You're just missing the call to CriteriaBuilder.literal():

Date currentDate = new Date();
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Experience> query = builder.createQuery(Experience.class);
Root<Experience> root = query.from(Experience.class);
builder.between(builder.literal(currentDate), root.get("from"), root.get("to"));
return entityManager.createQuery(query).getResultList();