Check that a List parameter is null in a Spring data JPA query

Spring enables us to use object models as query parameter with @Param annotation. An other workaround to check that a list type parameter is empty in dynamic queries is putting a getter method on our search model.

Assume that you have a search filter model named PersonSearchFilter by which you want to search Person model;

@Query(value = "SELECT a FROM Person a WHERE 1=1 "
            + "and (:#{#searchFilter.name} is null or a.name like %:#{#searchFilter.name}%) "
            + "and (:#{#searchFilter.statusListSize} = 0 or a.status in (:#{#searchFilter.statusList})) ")
Optional<List<Person>> findByFilter(@Param("searchFilter") PersonSearchFilter searchFilter);


public class PersonSearchFilter {

    private String name;
    private List<String> statusList;
    
    public String getName() {
        return name;
    }

    public List<String> getStatusList() {
        return statusList;
    }

    public int getStatusListSize() {
        return CollectionUtils.isEmpty(statusList) ? 0:statusList.size();
    }

}

OK I though of a thing after waking up at the middle of the night :

@Query("SELECT DISTINCT c FROM Course c\n" +
       "WHERE c.courseDate < CURRENT_TIME\n" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
       "AND (COALESCE(?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

The solution was simply to use COALESCE in addition to IS NULL so it can work with multiple values. That way if the list contain at least one non-null value, the second expression ((c.category IN ?3)) will do the job of filtering.

I will wait at least a whole night next time before asking a question :)

This doesn't strictly answer your question, but one simple solution is to have another method in your repository that checks your lists before calling the query and defaults them to a list with a dummy value. Something like:

@Query("SELECT DISTINCT c FROM Course c\n" +
       "WHERE c.courseDate < CURRENT_TIME\n" +
       "AND (?1 IS NULL OR (c.id NOT IN (3, 4, 5)))\n" +
       "AND (?2 IS NULL OR (c.title LIKE ?2 OR c.description LIKE ?2))\n" +
       "AND ('DUMMYVALUE' IN ?3 OR (c.category IN ?3)) ")
// make this private to keep it safe
private List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

// public helper method to put a dummy value in the list that you can check for
public List<Course> getNullableCoursesFiltered(Long courseId, String filter, List<Category> categories) {
    if(categories == null) {
        categories = Arrays.asList("DUMMYVALUE");
    }
    return getCoursesFiltered(courseId, filter, categories);
}