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

Question

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.

Answer 1

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

Answer 2

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)