Spring Boot 3.4.0 JPA分页查询含IN子句（null参数）报错问题

问题场景 #

使用Spring Boot 3.4.0编写JPA分页查询时，当List类型的currencies参数为null时，触发参数数量不匹配错误，参数非空或移除分页时查询正常。

JPA查询代码 #

@Query(value = "select c from Movement c left join c.currency cu WHERE c.user.id=?1 and (?2 IS NULL or cu.iso IN ?2)")
Page<Movement> findAllByUser( UUID userId, List<String> currencies,Pageable pageable);

SQL参数跟踪日志 #

2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (1:VARCHAR) <- [d55f237c-7eab-4bcd-a688-10fdb3ed09a3]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (2:JAVA_OBJECT) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (3:VARCHAR) <- [null]
2025-03-12 17:49:07,518 [http-nio-9090-exec-4] TRACE o.h.o.jdbc.bind - binding parameter (4:INTEGER) <- [50]

生成的原生SQL WHERE子句 #

where a1_0.core_user_id=? and (? is null or c1_0.iso in (?)) fetch first ? rows only

错误信息 #

2025-03-12 17:49:07,593 [http-nio-9090-exec-4] ERROR c.e.c.c.CustomExceptionHandlerResolver - At least 3 parameter(s) provided but only 2 parameter(s) present in query
org.springframework.dao.InvalidDataAccessApiUsageException: At least 3 parameter(s) provided but only 2 parameter(s) present in query

测试验证结果 #

• 仅保留?2 IS NULL条件时，查询正常执行
• currencies参数非空时，查询正常执行
• 仅当currencies为null且使用IN ?2时触发错误
• 移除分页后查询正常；移除currencies参数保留分页也正常

分页代码 #

Sort.Direction sortDirection = Sort.Direction.DESC;
String sortField = "c.id";
Sort.Order queryOrder = new Sort.Order(sortDirection, sortField);
Pageable pagingSort = PageRequest.of(1, 10,Sort.by(queryOrder));

解决方案 #

问题根源是Hibernate在处理IN ?2且参数为null时，会错误地将该参数解析为多个占位符，结合分页参数后导致实际传递的参数数量与SQL中的占位符数量不匹配。以下是三种可行解决方式：

方式1：改用命名参数替换位置参数 #

通过命名参数明确参数映射，避免位置参数解析错误：

@Query(value = "select c from Movement c left join c.currency cu WHERE c.user.id=:userId and (:currencies IS NULL or cu.iso IN :currencies)")
Page<Movement> findAllByUser(@Param("userId") UUID userId, @Param("currencies") List<String> currencies, Pageable pageable);

方式2：提前处理null参数，使用空集合替代 #

调用查询前将null参数转为空集合，同时调整查询条件判断集合是否为空：

// 调用时处理参数
List<String> queryCurrencies = currencies == null ? Collections.emptyList() : currencies;

// 修改后的JPA查询
@Query(value = "select c from Movement c left join c.currency cu WHERE c.user.id=?1 and (?2 IS EMPTY or cu.iso IN ?2)")
Page<Movement> findAllByUser(UUID userId, List<String> currencies, Pageable pageable);

方式3：使用Spring Data JPA派生查询（推荐） #

拆分逻辑为两个派生查询，避免手动JPQL的参数问题：

// 派生查询定义
Page<Movement> findByUserId(UUID userId, Pageable pageable);
Page<Movement> findByUserIdAndCurrencyIsoIn(UUID userId, List<String> currencies, Pageable pageable);

// 调用时分支处理
if (currencies == null || currencies.isEmpty()) {
    return movementRepository.findByUserId(userId, pageable);
} else {
    return movementRepository.findByUserIdAndCurrencyIsoIn(userId, currencies, pageable);
}

内容的提问来源于stack exchange，提问作者NiBE