Spring Boot Data中如何给WITH子句传递多行值（DB2场景）

问题背景 #

仓库查询需接收两个IN参数：一个是最多上万条的ID列表，另一个是最多10个标签值。DB2中当IN子句的ID数量超过8000时会触发101错误，因此改用WITH子句关联多行值表，但无法直接通过Spring Data JPA的参数绑定传递多行值列表，尝试String、List、List均无效。

解决方案 #

方案一：使用DB2的UNNEST函数（推荐） #

DB2支持UNNEST函数将传入的列表直接展开为多行数据，无需手动拼接VALUES子句，Spring Data JPA可直接绑定List参数。

修改后的仓库方法代码：

@Query("""
        WITH ID_TABLE (ID) AS (SELECT UNNEST(:values))
        SELECT 
            N.TERMINAL_ID   AS terminalId,                              
            N.TAG_NO        AS tagNo, 
            N.VALUE         AS value 
        FROM NOTIFY N 
            INNER JOIN ID_TABLE
                ON N.TERMINAL_ID = ID_TABLE.ID
        WHERE N.TAG_NO IN (:tags) 
        """, 
        nativeQuery = true)
List<TerminalTag> find(@Param("values") List<Long> values, @Param("tags") List<String> tags);

说明：UNNEST(:values)会将传入的List直接转换为ID_TABLE的多行数据，完美匹配DB2语法要求，同时避免SQL注入风险。

方案二：自定义Repository实现手动拼接VALUES子句 #

若使用的DB2版本不支持UNNEST，可通过自定义Repository实现类，手动生成符合要求的VALUES子句字符串。

1. 定义主Repository接口，继承自定义接口：

public interface TerminalTagRepository extends JpaRepository<TerminalTag, Long>, TerminalTagRepositoryCustom {
}

2. 定义自定义方法接口：

public interface TerminalTagRepositoryCustom {
    List<TerminalTag> findByTerminalIdsAndTags(List<Long> values, List<String> tags);
}

3. 实现自定义接口：

@Repository
public class TerminalTagRepositoryImpl implements TerminalTagRepositoryCustom {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<TerminalTag> findByTerminalIdsAndTags(List<Long> values, List<String> tags) {
        // 生成(1),(2),...形式的VALUES子句
        StringBuilder valuesClause = new StringBuilder();
        for (int i = 0; i < values.size(); i++) {
            if (i > 0) {
                valuesClause.append(",");
            }
            valuesClause.append("(").append(values.get(i)).append(")");
        }

        // 拼接完整SQL
        String sql = """
                WITH ID_TABLE (ID) AS (VALUES %s)
                SELECT 
                    N.TERMINAL_ID   AS terminalId,                              
                    N.TAG_NO        AS tagNo, 
                    N.VALUE         AS value 
                FROM NOTIFY N 
                    INNER JOIN ID_TABLE
                        ON N.TERMINAL_ID = ID_TABLE.ID
                WHERE N.TAG_NO IN (:tags) 
                """.formatted(valuesClause.toString());

        // 执行查询并绑定参数
        Query query = entityManager.createNativeQuery(sql, TerminalTag.class);
        query.setParameter("tags", tags);

        return query.getResultList();
    }
}

说明：此方法通过手动拼接VALUES子句满足DB2语法要求，由于参数是Long类型，不存在SQL注入风险。

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