Add CriteriaBuilder.between(Date) to Predicate?

Question

I am new to JPA

I am trying to query a table where my input date value should be between the startDate and endDate of the database record

I am trying to do:

List<Predicate> conditionsList = new ArrayList<Predicate>();
conditionsList.add(criteriaBuilder.between(inputDate, root.get("startDate"), root.get("endDate")));

I found the below solution from Using JPA/Hibernate Criteria to pull between a date:

ParameterExpression<Date> d = criteriaBuilder.parameter(Date.class);
criteriaBuilder.between(d, root.<Date>get("startDate"), root.<Date>get("endDate"));

But how to set the Parameterexpression value to inputDate variable value before adding the CriteriaBuilder to the Predicate?

Can you show your root class? – jonhid Jan 23 '17 at 12:37 — jonhid, Jan 23 '17 at 12:37

score 16 · Answer 1 · answered Jun 22 '17 at 20:51

You can try this way.


    Predicate date =  cb.between(root.get("date"), dateBefore, dateAfter);
    predicate.add(date);

this way works for my case.

But for your case (using ParameterExpression).


    return entityManager.createQuery(query)
    .setParameter(d, currentDate, TemporalType.DATE).getResultList();

When you create the query you set parameters. currentDate is your date, d is ParameterExpression that you create before.

I prefer the first way, it is more intuitive and logical for me.

score 14 · Answer 2 · answered Jan 23 '17 at 12:43

14

Something like this should work...

List<Predicate> conditionsList = new ArrayList<Predicate>();
Predicate onStart = criteriaBuilder.greaterThanOrEqualTo(root.get("startDate"), inputDate);
Predicate onEnd = criteriaBuilder.lessThanOrEqualTo(root.get("endDate"), inputDate);
conditionsList.add(onStart);
conditionsList.add(onEnd);
criteriaQuery.select(root).where(conditionsList.toArray(new Predicate[]{}));

answered Jan 23 '17 at 12:43

jonhid

2,075
1
11
18

Thanks, I was wondering whether builder.between(date) is possible through ParameterExpression or some other way possible – firstpostcommenter Jan 23 '17 at 13:08
this does'nt work ... take for example start_date = 2020 and end_date = 2022 and you trying to find it using the query in the question by providing the input 2021 – Ahmad Khundaqji Feb 15 '19 at 08:36

score 14 · Answer 3 · answered Apr 17 '20 at 10:15

14

Try this:

criteriaBuilder.between(criteriaBuilder.literal(inputDate),
                        root.<Date>get("startDate"), 
                        root.<Date>get("endDate"));

answered Apr 17 '20 at 10:15

Nelson Azevedo

311
3
4

Add CriteriaBuilder.between(Date) to Predicate?

3 Answers3

Linked