Spring-data-jdbc query with nullable collection

Question

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?

Andrei Lisa · Accepted Answer · 2023-08-26 07:56:36Z

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 ().

answered Aug 26, 2023 at 7:56

Andrei Lisa

5,2392 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

alexid Over a year ago

Thanks, but this didn't help as I expect that the collection itself can be null

Andrei Lisa Over a year ago

what do you mean, if collection is null nothing will be returned instead of error that was previously

alexid Over a year ago

When I call findAllByTypeAndLanguages(null) I get this: 2023-09-09 10:29:01 ERROR o.a.c.c.C.[.[.[.[dispatcherServlet] - Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.jdbc.BadSqlGrammarException: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1 When the languages collection is not null, it works fine. My idea is that if the collection is null, only type filter should be applied.

Andrei Lisa Over a year ago

can you also try to execute this sql in native version ? In a pgAdmin for example and try to pass there a param with null value.

Collectives™ on Stack Overflow

Spring-data-jdbc query with nullable collection

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related