CODEWITHSUNDEEP
hibernatejpaspring-data-jpaspring-datapersistent
Jon Irenicus
Jon Irenicus

Reputation: 45

Spring data getting records valid for given year (with fields fromDate, toDate)

I am trying to use JPA repository to filter some records from DB.

I got an entity with fields:

@Column(name = "from_date", nullable = false)
private ZonedDateTime fromDate;

@Column(name = "to_date", nullable = true)
private ZonedDateTime toDate;

I need to select all the records taking place for given year (lats say 2018). The tricky part is: if 'from_date' is from 2017, but 'to_date' is ie. 2019, I want to include that record.

Could You please give me some idea, how to sort it out? Is it possible to get that with just one method? If possible, I would like to use query methods.

At the moment, I invented method: 

List<ManualCorrection> findByFromDateGreaterThanEqualAndFromDateLessThanAndToDateGreaterThanEqual(ZonedDateTime yearStart,ZonedDateTime yearEnd, ZonedDateTime yearStart2);

However this doesn't give me all the records I'm interested in.

Thanks for Your help guys!

Upvotes: 0

Views: 100

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81970

This one should find all ManualCorrection entities that have a toDate >= yearStart and a fromDate <= yearEnd which seems to be what you are looking for.

 List<ManualCorrection> findByToDateGreaterThanEqualAndFromDateLessThanEqual(
     ZonedDateTime yearStart,
     ZonedDateTime yearEnd
 );

Upvotes: 1

Related Questions