如何使用PreparedStatement为SQL查询的IN参数传递空值

你当前使用Statement字符串拼接的方式存在SQL注入风险，改用PreparedStatement实现空值传递需要分场景处理IN条件，具体实现如下：

1. 核心逻辑说明 #

SQL中IN (NULL)永远不会返回匹配结果，因为数据库中NULL与任何值的等值判断结果都是UNKNOWN，所以必须分两种场景处理：

• 传入的RTCA_CD_QMNUM集合非空：动态生成对应数量的?占位符拼接在IN条件后，逐个设置参数
• 传入的集合为空（需要传递空值匹配）：直接将条件改写为E.RTCA_CD_QMNUM IS NULL

2. 完整实现代码 #

// 1. 基础SQL片段，替换硬编码参数为占位符，去掉原IN条件
String baseSql = "SELECT E.RTCA_CD_QMNUM"
        + ", E.RTIT_NR_ITEM"
        + ", D.RTSI_NR_SUBITEM"
        + ", B.PLOP_DT_INICIO"
        + ", B.PLOP_DT_FECHAMENTO"
        + ", D.RTSI_DT_MAIS_CEDO"
        + ", A.SSRT_CD_STATUS_SUBITEM_RT"
        + ", D.RTSI_DT_MAIS_TARDE"
        + ", A.PORT_DT_INCLUSAO"
        + ", C.PLCO_DS_PLANEJ_CONFIG"
        + ", F.ROTA_NM_ROTA"
        + ", H.CLLO_NM_CLUSTER_LOG"
        + ", G.ATEM_DT_INICIO"
        + ", I.TIAM_DS_TIPO_ATEND_MAR "
        + "FROM SIGIOP.PLANEJAMENTO_OPERACIONAL_RT A "
        + "INNER JOIN SIGIOP.PLANEJAMENTO_OPERACIONAL B ON B.PLOP_SQ_PLANEJ_OPER = A.PLOP_SQ_PLANEJ_OPER "
        + "INNER JOIN SIGIOP.PLANEJAMENTO_CONFIG C ON C.PLCO_SQ_PLANEJ_CONFIG = B.PLCO_SQ_PLANEJ_CONFIG "
        + "INNER JOIN SIGIOP.RT_SUBITEM D ON D.RTSI_CD_RTSUBITEM = A.RTSI_CD_RTSUBITEM "
        + "INNER JOIN SIGIOP.RT_ITEM E ON E.RTIT_CD_RTITEM = D.RTIT_CD_RTITEM "
        + "INNER JOIN SIGIOP.ROTA F ON F.ROTA_SQ_ROTA = A.ROTA_SQ_ROTA "
        + "INNER JOIN SIGIOP.ATENDIMENTO_MAR G ON G.ATEM_SQ_ATEND_MAR = A.ATEM_SQ_ATEND_MAR "
        + "INNER JOIN SIGIOP.CLUSTER_LOG H ON H.CLLO_SQ_CLUSTER_LOG = G.CLLO_SQ_CLUSTER_LOG "
        + "INNER JOIN SIGIOP.TIPO_ATENDIMENTO_MAR I ON I.TIAM_SQ_TIPO_ATEND_MAR = G.TIAM_SQ_TIPO_ATEND_MAR "
        + "WHERE (B.PLOP_DT_FECHAMENTO >= TO_DATE(?, 'DDMMYYYY') OR B.PLOP_DT_FECHAMENTO IS NULL) "
        + "AND (B.PLOP_DT_FECHAMENTO < TO_DATE(?, 'DDMMYYYY') OR B.PLOP_DT_FECHAMENTO IS NULL) "
        + "AND A.MORE_SQ_MOTIVO_REPLANEJ IS NULL "
        + "AND B.PLOP_IN_STATUS = 2 ";

// 2. 动态拼接IN条件，统一管理参数
List<Object> params = new ArrayList<>();
params.add(dataInicio);
params.add(dataFim);
StringBuilder fullSql = new StringBuilder(baseSql);

if (rts == null || rts.isEmpty()) {
    // 空参数场景：匹配字段为空的记录
    fullSql.append("AND E.RTCA_CD_QMNUM IS NULL ");
} else {
    // 非空场景：生成对应数量的占位符
    fullSql.append("AND E.RTCA_CD_QMNUM IN (");
    for (int i = 0; i < rts.size(); i++) {
        if (i > 0) fullSql.append(",");
        fullSql.append("?");
        params.add(rts.get(i));
    }
    fullSql.append(") ");
}
fullSql.append("ORDER BY E.RTCA_CD_QMNUM, E.RTIT_NR_ITEM, D.RTSI_NR_SUBITEM, B.PLOP_DT_INICIO");

// 3. 使用PreparedStatement执行查询
try (PreparedStatement pst = cnnSigiop.prepareStatement(fullSql.toString())) {
    // 批量设置所有参数
    for (int i = 0; i < params.size(); i++) {
        pst.setObject(i + 1, params.get(i));
    }
    try (ResultSet resultSigiop = pst.executeQuery()) {
        while (resultSigiop.next()) {
            // 原有业务逻辑
        }
    }
}

注意事项 #

• 所有动态参数都通过PreparedStatement的set方法传递，彻底避免SQL注入风险
• 使用try-with-resources语法自动释放连接、语句、结果集资源，避免资源泄漏

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