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

Question

I have a Spring Boot application and use Spring Data JPA to query a MySQL database.

I need to get a list of courses filtered with some parameters.

I usually use the syntax param IS NULL or (/*do something with param*/) so that it ignores the parameter if it is null.

With simple datatypes I have no problems but when it comes to a List of objects I don't know how to check for NULL value. How can I check if the ?3 parameter is NULL in the following query ?

@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 ((?3) IS NULL OR (c.category IN ?3)) ")
List<Course> getCoursesFiltered(Long courseId, String filter, List<Category> categories);

Error is :

could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet[SQL: 1241, 21000]

And in the stack trace I can see :

Caused by: java.sql.SQLException: Operand should contain 1 column(s)

Indeed generated query would be ... AND ((?3, ?4, ?5) IS NULL OR (c.category IN (?3, ?4, ?5))) if my list contains 3 elements. But IS NULL cannot be applied to multiple elements (query works fine if my list contain only one element).

I have tried size(?3) < 1, length(?3) < 1, (?3) IS EMPTY, etc. but still no luck.

Answer 1

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

Answer 2

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