CODEWITHSUNDEEP
javaspringhibernatespring-mvcspring-data-jpa
Maddy
Maddy

Reputation: 2224

Spring JPA method to find entities with beforeAndEqual a date AND afterAndEqual a date

I have an entity Tournament as follows:

class Tournament {
    //other attributes
    private LocalDate startDate;
    private LocalDate endDate;
}

This represents a tournament that runs for few days/months, from startDate to enddate. I need to retrieve all Tournaments that run today, at this moment, somthing like startDate <= today && endDate >= today, using Spring JPA and paging.

The closest I found was following:

@Repository
public interface TournamentRepository extends PagingAndSortingRepository<Tournament, Long> {

    Page<Tournament> findByStartBeforeAndEndAfter(LocalDate date, LocalDate dateCopy, Pageable page); //today's date is passed as date and dateCopy

}

Method call:

tournamentRepository.findByStartBeforeAndEndAfter(LocalDate.now(), LocalDate.now(), page);

This can be interpreted as startDate < today && endDate > today, so it doesn't work if a tournament runs today and only for 1 day.

Is there a better way to do this using Spring JPA without having to write a custom query?

Upvotes: 6

Views: 21796

Answers (3)

Abdullah Mohammad Motiullah
Abdullah Mohammad Motiullah

Reputation: 12592

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP are predefined functions in JPA. You can use that.

Try this

@Repository
public interface TournamentRepository extends PagingAndSortingRepository<Tournament, Long> {

    @Query("Select t from Tournament t where t.startDate <= CURRENT_DATE and t.endDate >= CURRENT_DATE")
    Page<Tournament> findByStartBeforeAndEndAfter(Pageable page);

}

Upvotes: 1

CIPHER007
CIPHER007

Reputation: 376

You can use @Query annotation and write his own query like below code.

@Repository
    public interface TournamentRepository extends PagingAndSortingRepository<Tournament, Long> {
        @Query("select t from Tournament t where t.startDate <=:date and t.endDate >=: dateCopy")
        Page<Tournament> findByStartBeforeAndEndAfter(@Param("date")LocalDate date,@Param("dateCopy") LocalDate dateCopy, Pageable page); //today's date is passed as date and dateCopy

    }

You can also try native query like this

@Repository
public interface TournamentRepository extends PagingAndSortingRepository<Tournament, Long> {
    @Query("select * from tournament where start_date <=:date and end_date >=: dateCopy",nativeQuery=true)
    List<Tournament> findByStartBeforeAndEndAfter(@Param("date")LocalDate date,@Param("dateCopy") LocalDate dateCopy); //today's date is passed as date and dateCopy

}

Upvotes: 2

karim mohsen
karim mohsen

Reputation: 2254

You can use LessThan, GreaterThan, LessThanEqual, GreaterThanEqual with dates.

@Repository
public interface TournamentRepository extends PagingAndSortingRepository<Tournament, Long> {

    Page<Tournament> findByGreaterThanEqualStartDateAndLessThanEqualEndDate(LocalDate date, LocalDate dateCopy, Pageable page); //today's date is passed as date and dateCopy

}

Upvotes: 10

Related Questions