Search code examples
javajpajpql

Is there an easier way to make a JPQL query with BETWEEN to include NULL values in properties

I have a JPQL query 

    @Query("SELECT e FROM Entry e WHERE :chosenDate BETWEEN e.periodFrom AND e.periodTo")
    List<Entry> findByChosenDate(LocalDate chosenDate);

There is a problem, that when one of the properties "periodFrom" or "periodTo" aren't set the entry won't be selected. I want that the query also include entires with one or two NULL values.

I want that the query select entries in three additional cases:

  1. When "periodFrom" is null and "periodTo" is null the entry should be found.

  2. When "periodFrom" is null and "periodTo" >=:chosenDate the entry should be found.

  3. When "periodFrom" <= :chosenDate and "periodTo" is null the entry should be found.

Is there an easier way to make such a query than :

    @Query("SELECT e FROM Entry e WHERE (:chosenDate BETWEEN e.periodFrom AND e.periodTo) 
        OR (e.periodFrom IS NULL AND e.periodTo IS NULL )
        OR ( e.periodFrom <= :chosenDate AND e.periodTo IS NULL)
        OR (e.periodFrom IS NULL AND e.periodTo >= :chosenDate)") 
    List<Entry> findByChosenDate(LocalDate chosenDate);
Solution

  • I don't know if you find it better, but something like this would be shorter:

    SELECT e FROM Entry e WHERE (:chosenDate BETWEEN COALESCE(e.periodFrom, 0000-01-01) AND COALESCE(e.periodTo, 9999-12-31)