Check date between two other dates spring data jpa
JavaSpringSpring Data-JpaSpring RepositoriesJava Problem Overview
I have this model:
public class Event {
private String name;
private Date start;
private Date end;
}
and repository as
@Repository
public interface EventRepository extends JpaRepository<Event, Long> {
List<Event> findByEventTypeAccount(Account account);
}
What I want to do is, I will pass one date and need to check that date is between start
and end
e.g. (I will pass Sept 30 as date and need to find all entries which have Sept 30 between their start
and end
)
Something like findDateisBetweenStartAndEnd(Date date)
?
Java Solutions
Solution 1 - Java
You should take a look the reference documentation. It's well explained.
In your case, I think you cannot use between because you need to pass two parameters
> Between - findByStartDateBetween … where x.startDate between ?1 and ?2
In your case take a look to use a combination of LessThan
or LessThanEqual
with GreaterThan
or GreaterThanEqual
- LessThan/LessThanEqual
> LessThan - findByEndLessThan … where x.start< ?1 > > LessThanEqual findByEndLessThanEqual … where x.start <= ?1
- GreaterThan/GreaterThanEqual
> GreaterThan - findByStartGreaterThan … where x.end> ?1 > > GreaterThanEqual - findByStartGreaterThanEqual … where x.end>= ?1
You can use the operator And
and Or
to combine both.
Solution 2 - Java
I did use following solution to this:
findAllByStartDateLessThanEqualAndEndDateGreaterThanEqual(OffsetDateTime endDate, OffsetDateTime startDate);
Solution 3 - Java
You can also write a custom query using @Query
@Query(value = "from EntityClassTable t where yourDate BETWEEN :startDate AND :endDate")
public List<EntityClassTable> getAllBetweenDates(@Param("startDate")Date startDate,@Param("endDate")Date endDate);
Edit: For LocalDateTime I just tried this solution for filtering record based on date and type in spring JPA:
Page<MyEntity> findMyEntityByEntityDateBetweenAndType(
@Param("startDate") LocalDateTime startDate,
@Param("endDate") LocalDateTime endDate, @Param("type") String type, Pageable pageable);
Here EntityDate can be like CreatedDate,ModifiedDate etc.
Solution 4 - Java
Maybe you could try
List<Article> findAllByPublicationDate(Date publicationDate);
The detail could be checked in this article:
Solution 5 - Java
You can use JpaRepository
List<Entity> findByColumnDateBetween(LocalDateTime to, LocalDateTime from);