1

While using spring-data-jdbc and postgresql to store and search data, I was not able to come up with a query inside @Query annotation which can cover collections which are nulls. I mean the following simplified example:

@Query("SELECT * FROM dialects WHERE type = 'Artificial' AND (:languages IS NULL OR language IN (:languages))")
findAllByTypeAndLanguages(@Param("languages") List<String> languages);

When I call the repository with languages = null, I get an error: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1

I didn't find an example in the documentation how this can be done. Of course, I can handle null values in the java code and call repository methods accordingly, but in my case the search can have a few collections as input parameters and it will be a mess if I cover each case with separate query..

I tried to use also String arrays and it didn't work. However, it seems to work with List, so does it mean that spring-data-jdbc cannot work with collections of objects?

user51
  • 8,843
  • 21
  • 79
  • 158
alexid
  • 11
  • 1

1 Answers1

0

The query should have next one body:

@Query("SELECT * FROM dialects WHERE type = 'Artificial' AND ((:languages) IS NULL OR language IN (:languages))")
findAllByTypeAndLanguages(@Param("languages") List<String> languages);

Because when you have a collection of parameters that can be NULL, the query parameter also should be in ().

Andrei Lisa
  • 1,361
  • 3
  • 11