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?

axtavt · Accepted Answer · 2010-11-23T14:28:14.280

24

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

edited Nov 23 '10 at 14:28

answered Nov 23 '10 at 13:49

axtavt

239,438
41
511
482

1

Thanks - that is perfect. My issue now is that for some reason root.get("from") is returning a Path object and not an Expression object? – christophmccann Nov 23 '10 at 13:59
@Christopher: `Path` is a subtype of `Expression`. – axtavt Nov 23 '10 at 14:03
The returned objects from root.get() are of generic supertype object not Date. But the first parameter is of generic supertype Date. All three must have the same supertype. Any ideas? – christophmccann Nov 23 '10 at 14:17
@Christopher: In this case you need to specify property types explicitly, see code. Also fixed typo in the type of `d`. – axtavt Nov 23 '10 at 14:30

score 11 · Answer 2 · answered May 18 '15 at 00:25

11

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();

answered May 18 '15 at 00:25

Bernie

2,253
1
16
18

1

This would be the perfect accepted answer as it is a pure JPA, thanks – Ahmad Khundaqji Feb 15 '19 at 09:40

Pedro Robles · Answer 3 · 2013-10-25T09:39:01.543

7

You should switch your arguments:

builder.between(root.<Date>get("dateColumn"), startDate, endDate);

edited Oct 25 '13 at 09:39

answered Oct 25 '13 at 08:53

Pedro Robles

205
5
11

score 3 · Answer 4 · edited Nov 23 '10 at 13:46

3

This link looks promising: http://www.javalobby.org/articles/hibernatequery102/

You can use the criteria.ge("date", root.get("from")); and criteria.le("date"), root.get("to")); to create the between claus

If those aren't available, then you might need to look into writing HQL.

edited Nov 23 '10 at 13:46

jmj

237,923
42
401
438

answered Nov 23 '10 at 13:45

Patrick Farrell

428
3
12

Using JPA/Hibernate Criteria to pull between a date

4 Answers4

Linked