CODEWITHSUNDEEP
sqljpaspring-data-jpaspring-datahql
Yann39
Yann39

Reputation: 15729

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

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.

Upvotes: 21

Views: 51793

Answers (4)

prashanth pochampally
prashanth pochampally

Reputation: 21

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

Upvotes: 2

Selman Gun
Selman Gun

Reputation: 141

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

}

Upvotes: 5

Yann39
Yann39

Reputation: 15729

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

Upvotes: 50

Matt
Matt

Reputation: 3363

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

Upvotes: 6

Related Questions