JPA IN子句查询参数数量多于传入值问题排查与解决

问题背景 #

我有一个标准JPA仓库：

@Repository
public interface SainsburysProductDimListRepository
    extends JpaRepository<ProductDim, Long>,
            JpaSpecificationExecutor<ProductDim> {

  List<ProductDim> findBySkuNoIn(List<Long> skuIds);
}

调用方式如下：

var matchingSkuIds = ... // 填充唯一非空的ID列表
var skus = productDimRepository.findBySkuNoIn(matchingSkuIds);
...

出现异常情况：

• 当matchingSkuIds.size()为28133时，Hibernate日志显示生成的IN子句参数数量为32768
• 当传入41739个ID时，参数数量达到65536，触发PostgreSQL限制报错：

org.postgresql.util.PSQLException: PreparedStatement can have at most 65,535 parameters. Please consider using arrays, or splitting the query in several ones, or using COPY.

额外参数的来源 #

这是Hibernate的IN子句参数填充优化导致的：为了复用PreparedStatement、减少语句编译开销，Hibernate会将IN子句的参数列表长度向上取整到最近的2的幂次方（比如2^15=32768、216=65536）。即使传入的ID数量小于这个值，也会填充额外的占位符，最终导致参数总数超过实际ID数。

解决方案 #

1. 拆分查询批次 #

将大ID列表拆分为多个小批次，每个批次的ID数量控制在30000左右（确保填充后的参数数不超过65535），分别查询后合并结果：

// 在业务层实现批次查询
public List<ProductDim> getSkusInBatch(List<Long> skuIds) {
    int batchSize = 30000;
    List<ProductDim> allSkus = new ArrayList<>();
    for (int i = 0; i < skuIds.size(); i += batchSize) {
        int endIndex = Math.min(i + batchSize, skuIds.size());
        allSkus.addAll(productDimRepository.findBySkuNoIn(skuIds.subList(i, endIndex)));
    }
    return allSkus;
}

2. 使用PostgreSQL数组参数 #

利用PostgreSQL支持数组的特性，将ID列表转为数组作为单个参数传入，避免大量占位符：

// 修改仓库接口，添加数组查询方法
@Repository
public interface SainsburysProductDimListRepository
    extends JpaRepository<ProductDim, Long>,
            JpaSpecificationExecutor<ProductDim> {

  List<ProductDim> findBySkuNoIn(List<Long> skuIds);

  // JPAQL版本
  @Query("SELECT p FROM ProductDim p WHERE p.skuNo IN (:skuIds)")
  List<ProductDim> findBySkuNoInArray(@Param("skuIds") Long[] skuIds);

  // 原生SQL版本（性能更优）
  @Query(value = "SELECT * FROM product_dim WHERE sku_no = ANY(:skuIds)", nativeQuery = true)
  List<ProductDim> findBySkuNoInArrayNative(@Param("skuIds") Long[] skuIds);
}

调用时转换为数组：

var skus = productDimRepository.findBySkuNoInArray(matchingSkuIds.toArray(new Long[0]));

3. 关闭Hibernate参数填充优化 #

在Hibernate配置中添加以下参数，禁用IN子句的参数填充行为，让参数数量与实际ID数一致：

hibernate.query.in_clause_parameter_padding=false

注意：此方案会降低PreparedStatement的复用率，可能对性能有轻微影响，需根据业务场景权衡。

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