JPA - Returning entities that are After StartDate and Before EndDate

Question

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.

`findByStartDate... JPA didnt like this`. There is a reason JPA didnt like it; the JPA API does not have anything to do with methods starting findXXX. That is Spring Data JPA API. Totally different API — , Dec 20 '17 at 07:24
@DN1 Yeah, tagged Spring JPA api. Thanks for the edit anyway. — Suresh Atta, Dec 20 '17 at 07:28
Is the field you check for `startDate` and `endDate` just a date, or does it contain contain time data as well? — coladict, Jan 09 '18 at 14:44
@Just date is fine. I am not really needed the time. 00:00:00 is fine. — Suresh Atta, Jan 09 '18 at 15:14
What version of spring data JPA are you using? What is the exact type of `Date`? Is it `java.sql.Date` or `java.util.Date`? — Sasha Shpota, Jan 09 '18 at 22:50
As I said, it returns empty. However less than and greater than works.. looks so strange. It is `java.util.Date` and `Spring Data JPA 1.10` — Suresh Atta, Jan 13 '18 at 03:22

score 22 · Answer 1 · answered Jan 09 '18 at 14:26

22

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.

answered Jan 09 '18 at 14:26

Sasha Shpota

9,436
14
75
148

I have already mentioned in my question that I have tried using `findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate)` – Suresh Atta Jan 09 '18 at 15:15
2

As I mentioned in the answer this is the working code, it follows the documentation and I verified it on real example. Try to copy-paste it, it might be that you made a typo while checking. – Sasha Shpota Jan 09 '18 at 22:43
@ꜱᴜʀᴇꜱʜᴀᴛᴛᴀ Could the problem be that you have swapped the 'After' and 'Before' predicates in your method name? The method name must be findByStartDate**Before**AndEndDate**After**. – jarnbjo Jan 12 '18 at 16:05
As I said, it returns empty. However less than and greater than works.. looks so strange. – Suresh Atta Jan 13 '18 at 03:20
if given dates are same what will be result. – Lova Chittumuri Jun 17 '19 at 11:53

score 5 · Answer 2 · answered Jan 09 '18 at 13:25

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.

score 3 · Answer 3 · answered Dec 20 '17 at 06:50

3

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

answered Dec 20 '17 at 06:50

hiren

1,742
13
20

I want records that lies between the given date. – Suresh Atta Dec 20 '17 at 06:52
1

between what given date ? to get "between" data you have to have two dates – hiren Dec 20 '17 at 06:53
It's the reverse case what I needed. I give the date, and need all the entities, that start date > than given and < end date. – Suresh Atta Dec 20 '17 at 06:55
1

Ok, In this case you can pass same date values in both argument. Like `findByStartDateAfterAndEndDateBefore(Date givenDate, Date givenDate)` – hiren Dec 20 '17 at 06:57
Or create a default method that does that for you. – Bruno Medeiros Dec 20 '17 at 06:59
@HirenPandit That returns null however :( – Suresh Atta Dec 20 '17 at 07:25
try one thing use `findByStartDateBeforeAndEndDateAfter(Date givenDate, Date givenDate)` – hiren Dec 20 '17 at 07:31

score 2 · Answer 4 · answered Jan 10 '18 at 07:19

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.

score 2 · Answer 5 · answered Jan 12 '18 at 06:21

    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;
   }

score 1 · Answer 6 · edited Jan 16 '18 at 10:50

1

I use it with no problems like this:

findAllByEntityNotNullAndDateBetween(Date begin, Date end);

Have you already tried it?

edited Jan 16 '18 at 10:50

Suresh Atta

120,458
37
198
307

answered Jan 16 '18 at 10:47

fabioresner

915
14
21

1

Not a single date, I have to check with start and end dates. Consider reading the question again ? – Suresh Atta Jan 16 '18 at 10:50

JPA - Returning entities that are After StartDate and Before EndDate

6 Answers6