如何传入列表作为Oracle查询参数并返回满足条件的列表项？

现有一张Oracle表backups，表中name列存储「备份名称+日期」格式的记录，示例数据如下：

ID   Name 
11   ABC_20220601 
22   ABC_20220531 
33   XYZ_20220531 
44   LMN_20220530

原有单参数查询通过字符串拼接生成SQL，匹配返回表中符合like条件的备份记录，代码实现如下：

String sql = String.format("select name from backups where name like '%s'", fileName);
return jdbcConnection.executeQuery(sql);

原逻辑示例：入参fileName = "ABC"时，返回结果为["ABC_20220601"]。

入参从单个字符串改为文件名列表List<String> fileNames，最终返回传入列表中存在匹配备份记录的文件名，不需要返回表中实际匹配到的备份记录。

改造后预期：入参为["ABC", "LMN", "PQR"]时，返回["ABC", "LMN"]，而非表中匹配到的["ABC_20220601", "ABC_20220531", "LMN_20220530"]。

核心原则：避免SQL注入、减少冗余数据返回、保证查询性能。

SQL写法（Oracle 12cR2及以上版本，性能最佳） #

通过表值函数把传入的文件名列表转为虚拟表，用EXISTS判断是否存在匹配的备份记录，直接返回匹配成功的入参文件名：

SELECT column_value AS matched_file
FROM TABLE(?) -- 这里绑定传入的文件名数组参数
WHERE EXISTS (
    SELECT 1
    FROM backups b
    -- 按示例的命名规则：备份名=文件名+_+日期，转义下划线避免SQL通配符误匹配
    WHERE b.name LIKE column_value || '\_%' ESCAPE '\'
)

如果你的业务是任意位置包含文件名的模糊匹配，把LIKE后的表达式改为b.name LIKE '%' || column_value || '%'即可。

Java侧JDBC实现 #

不要用字符串拼接SQL，通过参数绑定传入数组，从根源避免SQL注入风险：

// 将Java列表转为Oracle JDBC支持的数组类型
Array fileNameArr = connection.createArrayOf("VARCHAR2", fileNames.toArray());
String sql = """
SELECT column_value AS matched_file
FROM TABLE(?)
WHERE EXISTS (
    SELECT 1
    FROM backups b
    WHERE b.name LIKE column_value || '\_%' ESCAPE '\'
)
""";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setArray(1, fileNameArr);
ResultSet rs = pstmt.executeQuery();
// 遍历ResultSet取出matched_file，就是需要返回的文件名列表

低版本Oracle兼容方案 #

如果使用的Oracle版本不支持绑定数组参数，可以在Java侧动态生成参数占位符，构造虚拟表后做匹配，依然保持参数绑定，不要拼接参数值：

SELECT file_name
FROM (
    -- Java侧根据fileNames的长度动态生成对应数量的?占位符，用UNION ALL拼接
    SELECT ? AS file_name FROM dual
    UNION ALL SELECT ? FROM dual
    UNION ALL SELECT ? FROM dual
) t
WHERE EXISTS (
    SELECT 1 FROM backups b WHERE b.name LIKE t.file_name || '\_%' ESCAPE '\'
)

• 不要继续用String.format拼接SQL，会存在严重的SQL注入风险。
• 不要直接查询backups表再通过Java代码去重映射：同一个文件名可能对应多条历史备份记录，会返回大量冗余数据，浪费网络IO和内存，性能比上述方案差很多。
• 注意SQL中_是单字符通配符，如果文件名本身包含下划线，必须做转义，否则会出现误匹配。

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