How to handle null values for list using r2dbc ReactiveSortingRepository

Question

I am using the R2DBC library with ReactiveSortingRepository and i am having the below query to fetch some data.

@Query("SELECT DISTINCT item_id FROM item_table" +
            " WHERE year = :year" +
            " AND (:itemName IS NULL OR item_name IN :itemName)"
    )
    Flux<String> findDistinctItemIdByYearAndItemNameIn(
            @Param("itemName") List<String> itemName,
            @Param("year") Integer year);

I am using the above query to fetch some data. But the case is the itemName value may/may not be null. I have added the IS NULL condition also. But I am getting the below error when itemName is passed as null

message": "Cannot encode null parameter of type java.util.List",

What could be the best solution here? Also is there any direct way to use the default implementation of ReactiveSortingRepository instead of using the @Query annotation?

Answer 1

ive found some workaround to make it work both with postgresql and h2 for tests. In your repository service check if list is null or empty, if it is, change it to List.of("") and then modify your query like this:

@Query("SELECT DISTINCT item_id FROM item_table" +
   " WHERE year = :year" +
   " AND (('') IN (:itemName) OR item_name IN (:itemName))")