JPA/Hibernate中处理大型IN子句的高效方案（SQL Server 8623错误）

方案1：拆分IN子句为小批量查询，内存合并结果 #

把数千个值的集合拆分成多个小批次（建议每批不超过1000个，符合SQL Server对IN子句的优化上限），分别执行查询后在内存中合并结果，再处理分页逻辑。

示例代码逻辑：

List<Long> largeIds = ...; // 你的大型ID集合
int batchSize = 1000;
List<T> resultList = new ArrayList<>();

for (int i = 0; i < largeIds.size(); i += batchSize) {
    int end = Math.min(i + batchSize, largeIds.size());
    List<Long> batchIds = largeIds.subList(i, end);
    
    // 构建对应批次的CriteriaQuery
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<T> cq = cb.createQuery(YourEntity.class);
    Root<YourEntity> root = cq.from(YourEntity.class);
    cq.where(root.get("id").in(batchIds));
    
    TypedQuery<T> query = entityManager.createQuery(cq)
            .setLockMode(LockModeType.NONE)
            .setHint(QueryHints.READ_ONLY, true);
    
    resultList.addAll(query.getResultList());
}

// 内存中处理分页：取第0到第10条
List<T> paginatedResult = resultList.stream()
        .skip(0)
        .limit(10)
        .collect(Collectors.toList());

注意：若查询结果存在重复数据，需先去重再分页；该方法适合数据量未达到内存临界值的场景，需提前评估内存占用。

方案2：用临时表存储大型集合，关联查询替代IN子句 #

1. 创建SQL Server会话级临时表，将所有值批量插入
2. 在Criteria Query中通过JOIN关联临时表，替代原IN子句逻辑
3. 执行分页查询后销毁临时表

示例代码：

// 1. 创建临时表并批量插入数据
String createTempTableSql = "CREATE TABLE #TempIds (id BIGINT PRIMARY KEY)";
entityManager.createNativeQuery(createTempTableSql).executeUpdate();

// 用JDBC批量插入提升效率
Session session = entityManager.unwrap(Session.class);
Connection conn = session.doReturningWork(connection -> connection);
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO #TempIds (id) VALUES (?)");

List<Long> largeIds = ...;
for (Long id : largeIds) {
    pstmt.setLong(1, id);
    pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();

// 2. 构建关联临时表的Criteria Query
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(YourEntity.class);
Root<YourEntity> root = cq.from(YourEntity.class);

// 通过子查询关联临时表（需为临时表创建对应JPA实体类）
Subquery<Long> subquery = cq.subquery(Long.class);
Root<TempIdEntity> tempRoot = subquery.from(TempIdEntity.class);
subquery.select(tempRoot.get("id"));

cq.where(cb.in(root.get("id")).value(subquery));

TypedQuery<T> query = entityManager.createQuery(cq)
        .setLockMode(LockModeType.NONE)
        .setHint(QueryHints.READ_ONLY, true)
        .setFirstResult(0)
        .setMaxResults(10);

List<T> result = query.getResultList();

// 3. 清理临时表
entityManager.createNativeQuery("DROP TABLE #TempIds").executeUpdate();

注意：会话级临时表会随会话结束自动销毁；需为临时表定义对应JPA实体类，或直接用原生SQL关联；JDBC批量插入比JPA自带方法效率更高。

方案3：使用SQL Server表值参数（TABLE_VALUED_PARAMETERS） #

SQL Server支持表值参数，可直接将集合作为参数传入，彻底规避IN子句的限制，性能优于临时表方案。

步骤：

1. 在SQL Server中创建自定义表类型：

CREATE TYPE IdList AS TABLE (Id BIGINT)

2. Java代码中使用表值参数执行查询：

List<Long> largeIds = ...;
Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> {
    SQLServerConnection sqlConn = connection.unwrap(SQLServerConnection.class);
    // 构建表值参数
    SQLServerDataTable tvp = new SQLServerDataTable();
    tvp.addColumnMetadata("Id", java.sql.Types.BIGINT);
    for (Long id : largeIds) {
        tvp.addRow(id);
    }
    
    // 用原生SQL执行分页查询
    String sql = "SELECT e.* FROM YourEntity e JOIN @Ids tvp ON e.id = tvp.Id ORDER BY e.id OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
    try (SQLServerPreparedStatement pstmt = sqlConn.prepareStatement(sql)) {
        pstmt.setStructured(1, "IdList", tvp);
        ResultSet rs = pstmt.executeQuery();
        // 映射ResultSet到目标实体类
        // ...
    }
});

注意：需使用Microsoft官方SQL Server JDBC驱动；该方法适合超大型集合的查询场景，性能最优。

方案4：调整SQL Server配置参数（不推荐） #

可尝试修改max_expression_tree_depth或添加查询优化器提示，但这类调整会影响整个数据库的查询优化逻辑，可能引发其他性能问题，仅作为最后应急手段。

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