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.

Jacobah

How much fine-grained control of your query do you need? If you are using Spring Boot, have you considered using the auto-generated CRUD methods based on the method name? That way you can use the @Nullable annotation on your parameters and it might just work out of the box. See docs.spring.io/spring-data/jpa/docs/current/reference/html/… – Leucocyte

Failing that, I believe in MySQL it is valid syntax to say "where X in (NULL)" so I think your query above should work. Your problem seems to be in extracting the ResultSet, not in running the query. Can you add the full code for your Course class along with the entity mappings and the full stacktrace of the error you are getting? – Leucocyte

(And of course, although this is probably not the answer you want, but the simplest solution is to have two separate query methods, one that checks the category and one that doesn't, and a delegator method that checks your categories list in Java code and calls the appropriate query based on whether it is null or not i.e. if it's null, don't include it in the query at all) – Leucocyte

Thanks Matt, I already use method name based queries for other needs, but actually the query I speak about here has been simplified for the example and cannot be done that way. By the way where X in (NULL) works as expected, it is the (?3) IS NULL statement which is causing the problem. My Course class and entity mapping is very basic, nothing special. I indeed call the repository method from a manager class and I can do some trick there as you suggested but I'm pretty sure there is a cleaner way of doing this :) – Jacobah

lol I found this question while searching for a solution in spring-data-mongo. I figured a clean way to make it work using SpEl here – Protease

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

Jacobah

This seems a hack. Hibernate should give an official solution. – Maloy

@CDT: It seems that at least Spring team is investigating this problem: See DATAJPA-209. – Diarmid

Also for me the only solution that has worked. Have tried with is empty, is null , with no success. – Inshore

this solution is for MySql but not working with SQL Server. – Angarsk

It works fine with PostgreSQL – Gamboge

I had to add a second value to the COALESCE function in Oracle : AND (COALESCE(?3, NULL) IS NULL – Halbeib

It does not work with DB2 (AS400) – Cephalad

I was doing a "((:employeeIds) IS NULL)" in my Query. It returned "ORA-00920: invalid relational operator" when I gave 2 or more values to employeeIds array. This solved my issue and worked fine with Oracle DB. – Thersathersites

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

Leucocyte

How can you define methods in a Repository? Aren't you dealing with interfaces? – Phares

@geneb. Java 8 introduced the ability to put concrete methods in interfaces – Leucocyte

this is not a answer this is a huge code smell – Toll

Answer 3

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

}

Loudhailer

Did you try :#{#searchFilter.statusList.size} instead of :#{#searchFilter.statusListSize}? It should work with SpEL. – Dode

Answer 4

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

Taproot