java - Using Collection in PGSQL native Query in Spring Data as parameter

Question

Is it possible to use it as says topic?

    @Query("select p as payment from Payment p" +
        " left join Payment pp on pp = p" +
        " where p.uploadData.user = :user " +
        " and p.uploadData.active = false " +
        " and p.timestamp >= :timestamp_from " +
        " and p.timestamp < :timestamp_to " +
        " and (concat(p.address.building.building, ', ',p.address.apartment) in (case when :addressNull = true then concat(p.address.building.building, ', ',p.address.apartment)  else :addressList end))" +
        " and (concat(p.account.account,'') in (case when  :accountNull = true then concat(p.account.account,'') else :accountList end))" +
        " and (concat(p.service.service,'') in (case when  :serviceNull = true then concat(p.service.service,'') else :serviceList end))")
List<Payment> findAllUsingTimestampAndFilter(@Param("user") User user,
                                             @Param("timestamp_from") ZonedDateTime from,
                                             @Param("timestamp_to") ZonedDateTime to,
                                             @Param("addressList") Collection<String> address,
                                             @Param("accountList") Collection<String> account,
                                             @Param("serviceList") Collection<String> service,
                                             @Param("addressNull") boolean addressFlag,
                                             @Param("accountNull") boolean accountFlag,
                                             @Param("serviceNull") boolean serviceFlag);

When i'm using it this way, i'm getting an error when any collection parameter contains more than one item in it.

org.postgresql.util.PSQLException: ERROR: CASE types record and text cannot be matched
Position: 1061

Already tried

...(:serviceNull or (p.service.service in :serviceList))

and got another error.

Simplifying, i tried a lot of things, but still getting errors. Anyone have idea, how can to fix it?

P.S. Sorry, if there's error in my post, it's my first question on stackoverflow and my english is not so good.

Answer 1

等待大神答复