JPA原生查询IN子句传多元素列表报PSQLException如何解决？

报错修复方案 #

核心报错原因 #

你这个报错是两个低级语法错误共同导致的：

1. countQuery中title条件拼接后缺少空格，导致text)AND两个关键字连在一起，SQL解析异常
2. countQuery的WHERE条件最后多写了1个多余的右括号，导致SQL语法结构被破坏，PostgreSQL误将括号包裹的多值参数识别为record类型，触发AND参数必须为布尔类型的报错

完整修复步骤 #

1. 修正countQuery的语法问题：补充title条件后的空格，删除末尾多余的右括号
2. 统一所有IN子句的写法，给列表参数包裹括号，避免JPA解析异常
3. 删除排序逻辑中多余的:sortByDeadline常量排序：你后续的CASE表达式已经实现了根据参数选择排序字段的逻辑，前面加ORDER BY :sortByDeadline属于无效逻辑，会导致排序结果不符合预期
4. （可选优化）列表参数的非空判断使用SpEL写法，避免多值列表的解析异常：(:#{#userId == null} OR b.user_id IN (:userId))

修复后的完整代码参考：

@Query(value = "SELECT DISTINCT * " +
            "FROM chores WHERE id in (SELECT a.id " +
            "FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id " +
            "WHERE (a.is_deleted=FALSE " +
            "AND a.family_id=:familyId " +
            "AND (:#{#userId == null} OR b.user_id IN (:userId)) " +
            "AND (:#{#status == null} OR a.status IN (:status)) " +
            "AND (:title IS NULL OR a.title LIKE cast(:title as text)) " +
            "AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)) " +
            "ORDER BY CASE WHEN :sortByDeadline THEN a.deadline END DESC " +
            ", CASE WHEN NOT :sortByDeadline THEN a.created_at END DESC)",
            countQuery = "SELECT COUNT(DISTINCT a.id) FROM chores as a INNER JOIN chores_assign_users as b ON a.id=b.chore_id " +
                    "WHERE a.is_deleted=FALSE " +
                    "AND a.family_id=:familyId " +
                    "AND (:#{#userId == null} OR b.user_id IN (:userId)) " +
                    "AND (:#{#status == null} OR a.status IN (:status)) " +
                    "AND (:title IS NULL OR a.title LIKE cast(:title as text)) " +
                    "AND (:from IS NULL OR :to IS NULL OR cast(created_at as VARCHAR) >= :from AND cast(created_at as VARCHAR) <= :to)",
            nativeQuery = true)
ArrayList<Chore> findAlLFilteredByUserAndStatusAndTitleSortedByCreatedAtOrDeadLine(@Param("familyId") int familyId,
                                                                                   @Param("userId") List<Integer> userId,
                                                                                   @Param("status") List<String> status,
                                                                                   @Param("title") String title,
                                                                                   @Param("sortByDeadline") boolean sortByDeadline,
                                                                                   @Param("from") String from,
                                                                                   @Param("to") String to,
                                                                                   Pageable pageable);

类型转换合理性说明 #

你把integer类型的userId转成String列表再在SQL中强转匹配的处理方式不是本次报错的直接原因，但这个写法非常不合理：

• 性能损耗：对user_id字段做varchar强转会导致该字段上的索引失效，数据量稍大时查询性能会大幅下降
• 稳定性风险：存在字符串转数字的隐式转换异常风险，一旦传入非数字格式的字符串会直接触发SQL报错
• 冗余逻辑：完全没有必要做类型转换，直接将参数定义为List<Integer>，SQL中直接匹配user_id IN (:userId)即可

内容的提问来源于stack exchange，提问作者Trọng Nghĩa Lê Đình