CODEWITHSUNDEEP
javaspringdatespring-data-jpajpql
Suresh Atta
Suresh Atta

Reputation: 122026

JPA - Returning entities that are After StartDate and Before EndDate

I have two dates in my entity. ie.

Date startDate;
Date endDate;

How do I query so that given a date, it will return all entities where the specified date lies between startDate and endDate?

I already tried the following:

findByStartDateAfterAndEndDateBefore(Date givenDate);

And Spring-Data-JPA didn't like this and running into errors. There is no specific error and the repo just can't be injected to my class.

What is the correct way? I know this can be done easily wqith Hibernate criteria or with Native SQL but trying to do that in Spring JPA.

Is this a problem with the query itself or some sort of incompatibility between the Date types Spring uses?

Tried findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate) and that returns null however.

Upvotes: 23

Views: 36790

Answers (6)

fabioresner
fabioresner

Reputation: 953

I use it with no problems like this:

findAllByEntityNotNullAndDateBetween(Date begin, Date end);

Have you already tried it?

Upvotes: 1

DHARMENDRA SINGH
DHARMENDRA SINGH

Reputation: 615

    Public List<EntityClass> findData(Date startDate,Date endDate)
   {
     Query<EntityClass> _q = em.createQuery("select a.* from _tableName a 
                         where a.startDate>= :d1 AND a.endDate<= :d2");
    _q.setParameter("d1", startDate, TemporalType.DATE);
    _q.setParameter("d2", endDate, TemporalType.DATE);
    List<EntityClass> result = query.getResultList();
    em.getTransaction().commit();
    return result;
   }

Upvotes: 2

coladict
coladict

Reputation: 5095

Since you only need the Date object as an equivalent of LocalDate, this should do the trick.

default List<AnEntity> findByStartDateAfterAndEndDateBefore(Date startDate, Date endDate) {
    Calendar cal = Calendar.getInstance();
    cal.setTime(endDate);
    cal.add(Calendar.DATE, -1);
    return findByStartDateBetween(startDate, cal.getTime());
}

Because you want it to include the start, but not the end, and SQL BETWEEN is inclusive, we just move the end one day back.

Upvotes: 2

Sasha Shpota
Sasha Shpota

Reputation: 10320

You can't use only one parameter because of Spring Data restrictions, but you can workaround it using code like this:

List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date startDate, Date endDate);

default List<AnEntity> findByStartDateBeforeAndEndDateAfter(Date givenDate) {
    return findByStartDateBeforeAndEndDateAfter(givenDate, givenDate);
}

This code should cover your needs. I also verified it with Spring Boot 1.5.9. using spring-data-get-started example.

Upvotes: 24

Suresh Atta
Suresh Atta

Reputation: 122026

To my surprise LessThan and GreaterThan working and Before and After failing badly.

I never thought I can use less than and greater than for dates and always look at date related functions like between, before, after.

That's mostly because of the documentation example 

After

findByStartDateAfter

… where x.startDate > ?1

Before

findByStartDateBefore

… where x.startDate < ?1

So I looked more close into docs of Spring Data JPA and found something interesting with the below example 

 LocalDate date = new LocalDate().minusYears(2);
 return builder.lessThan(root.get(_Customer.createdAt), date);

So while comparing datatime the authors using the criteria lessthan for time property.

So given a shot with less than and worked and again gave a shot with greater than aswell and later together. So I came up with a conclusion 

public MyDateEntity findByStartDateLessThanAndEndDateGreaterThan(Date sDate, Date eDate);

And this is working so far. And I believe, there must be a clean way to handle dates probably with before,after,between but I just can't figure that out.

Would be great if someone figure that out.

Upvotes: 6

hiren
hiren

Reputation: 1812

You are using AND condition in your JPA query but providing only one parameter. You should use like findByStartDateAfterAndEndDateBefore(Date startDate, Date endDate);

Upvotes: 3

Related Questions