JAVA JPA Checking a date lies between two dates

Question

I am using JAVA JPA Criteria Builder to check a whether a date lies between two dates or not. I am passing the date into the function which is having a query on a table and that table have two dates columns. Now i want to check the date which i have passed lies between the two or not.

 private CorpClientsCommRateD getCorpClientsCommRateD(Date date) {
    CorpClientsCommRateD commissionRate=null;
    try {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<CorpClientsCommRateD> query = builder.createQuery(CorpClientsCommRateD.class);
        Root<CorpClientsCommRateD> root = query.from(CorpClientsCommRateD.class);

        List<Predicate> conditions = new ArrayList();
        conditions.add(builder.between(date,root.get("effFromDate"),root.get("effToDate")));

        query.select(root)
                .where(conditions.toArray(new Predicate[]{}));

        TypedQuery<CorpClientsCommRateD> commissionRates = em.createQuery(query);
        commissionRate=commissionRates.getSingleResult();
    }
    catch(Exception exp)
    {
        return commissionRate;
    }
    return commissionRate;
}

I am getting a error that Error:(148, 35) java: no suitable method found for between(java.util.Date,javax.persistence.criteria.Path,javax.persistence.criteria.Path)

Kindly help

Change `root.get("effFromDate")` to explicitly cast the type to `root.get("effFromDate")` for both inputs — Kenneth Clark, Jan 19 '18 at 04:45
Possible duplicate of [Compare Date entities in JPA Criteria API](https://stackoverflow.com/questions/9449003/compare-date-entities-in-jpa-criteria-api) — Kenneth Clark, Jan 19 '18 at 04:50
[Using JPA/Hibernate Criteria to pull between a date](https://stackoverflow.com/questions/4256561/using-jpa-hibernate-criteria-to-pull-between-a-date)? It’s not my home field, so I may be missing something. In any case, use your search engine to check if this questions has been asked before. — Ole V.V., Jan 19 '18 at 05:32

score 1 · Answer 1 · answered Jan 19 '18 at 07:17

Your first argument to between is being passed as a Java literal, so to use that in Criteria you need to convert it to either a query parameter

builder.parameter(Date.class, "mydate")

or convert it to a Criteria literal.

builder.literal(date)

But then you could have got that by simply looking at the javadocs for that method and working out how to get a Criteria "Expression"

JAVA JPA Checking a date lies between two dates

1 Answers1