CODEWITHSUNDEEP
javasqlpostgresqlspring-data-jpaspring-repositories
DV82XL
DV82XL

Reputation: 6629

JPA query to filter before, after and between optional start and end dates

I'd like to write a JPA Repository query that can findBy with two optional query parameters, startDate and endDate:

startDate endDate Return
null null All
null endDate Before End
startDate null After Start
startDate endDate Between Start and End

How can this be implemented concisely? For example, using a single JPA @Query method with a SQL statement that can handle null or Optional<Date> parameters?

EDIT: I'm using PostgreSQL 13.

Upvotes: 4

Views: 9007

Answers (2)

DV82XL
DV82XL

Reputation: 6629

Here's a naïve solution using 4 methods and a switch. It's clunky, but it works. This approach can get particularly verbose if more complex JPQL or SQL queries need to be implemented, since the 4 Repository methods and queries would need to be duplicated.

Repository

@Repository
public interface MyRepository extends JpaRepository<MyObject> {
  List<MyObject> findByDateBetween(Date beforeDate, Date afterDate);
  List<MyObject> findByDateBefore(Date beforeDate);
  List<MyObject> findByDateAfter(Date afterDate);
  List<MyObject> findAll();

Service

public List<MyObject> search(Date startDate, Date endDate) {
  int i = (startDate!=null ? 1 : 0) | (endDate!=null ? 2 : 0);
  switch(i) {
    case 0:
      return repository.findAll();
    case 1:
      return repository.findByDateAfter(startDate);
    case 2:
      return repository.findByDateBefore(endDate);
    case 3:
      return repository.findByDateBetween(startDate, endDate);
  }
}

(credit to this answer by Marc Gravell for the switch encoding)

Upvotes: 2

Yassin Hajaj
Yassin Hajaj

Reputation: 21965

You can use the following query to achieve this. I took the freedom to use <= and >= but of course you can choose < and > instead or one of both. The idea was to give you a functioning query already.

@Query("SELECT m FROM MyObject m WHERE (:beforeDate is null or m.beforeDate >= :beforeDate) and (:afterDate is null or m.afterDate <= :afterDate)")
List<MyObject> findByDateBetween(Date beforeDate, Date afterDate);

Upvotes: 2

Related Questions