How to fetch data based on date using criteria query?

Question

I'm working with criteria query and want to fetch data based on date. However, criteria query like condition does not work with date.

i get date like 2020-08-25 from user.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<FermBatch> cq = cb.createQuery(MyClass.class);
Root<MyClass> root = cq.from(MyClass.class);
cq.where(cb.like(root.get(MyClass_.date), date+"%"));
return em.createQuery(cq).getResultList();

With this like condition, it shows error:

The method like(Expression, String) in the type CriteriaBuilder is not applicable for the arguments (Path, String)

And in my db date is stored in like this 2020-08-25 03:02:06 How can I get all data based on passed date (only using date, time does not matter).

I also tried this solution from other stackoverflow question link:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<MyClass> cq = cb.createQuery(MyClass.class);
Root<MyClass> entity = cq.from(MyClass.class);
cq.select(entity);

List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(cb.like(cb.lower(entity.get(MyClass_.date).as(String.class)), "%"+date.toLowerCase()+"%")); 

Path<Tuple> tuple = entity.<Tuple>get("date");
if(tuple.getJavaType().isAssignableFrom(Date.class)){
    Expression<String> dateStringExpr = cb.function("DATE_FORMAT", String.class, entity.get("date"), cb.literal("'%d/%m/%Y %r'"));
    predicates.add(cb.like(cb.lower(dateStringExpr), "%"+date.toLowerCase()+"%"));
}

cq.where(predicates.toArray(new Predicate[]{}));
TypedQuery<MyClass> query = em.createQuery(cq); 
return query.getResultList();

This returned an empty arraylist. So what am I doing wrong?

Does this answer your question? [Date comparison using the JPA criteria API](https://stackoverflow.com/questions/38424516/date-comparison-using-the-jpa-criteria-api) — Eklavya, Aug 25 '20 at 12:09
@Rono thank you for reply. i posted answer that worked for me. — chiranjiv, Aug 25 '20 at 12:46

score 0 · Answer 1 · answered Aug 25 '20 at 12:45

I'll suggest to pass the "data" as milliseconds; it's always easier.

Anyway you should not use the operator "like" on a DATE; you should use equals, lower, or greather than.

long millis = 1598359174854l; // Tue Aug 25 2020 12:39:34
Predicate pred = criteriaBuilder.greaterThanOrEqualTo(
                    root.get(MyClass_.date),
                    new Date(millis);

If you have the String, you should first convert the string to DATE, and then you have to compare:

String date = "2020-08-25";
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
dateFormat.setLenient(false);
Date cal = dateFormat.parse(date);

score 0 · Answer 2 · answered Aug 25 '20 at 12:45

This code is worked for me

    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<MyClass> query = builder.createQuery(MyClass.class);
    Root<MyClass> root = query.from(MyClass.class);
    
    Predicate predicate = builder.conjunction();
    predicate = builder.like(root.get(MyClass_.date).as(String.class), "%"+date+"%"); 
    
    query.where(predicate).select(root);;
    
    return em.createQuery(query).getResultList();

Thank you all.

How to fetch data based on date using criteria query?

2 Answers2