Spring NamedParameterJdbcTemplate删除with IN子句

Question

当我尝试执行删除时，将项目设置为列表

public class DBTest {

    public static void main(String[] args) {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setUrl("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("qwedsa");

        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(new JdbcTemplate(basicDataSource));

        ImmutableMap<String, ?> param = ImmutableMap.of("items", newArrayList("a", "b", "c"), "user_id", 1);
        Map<String, ?>[] params = new Map[]{param};
        template.batchUpdate("delete from sample where col1 in (:items) and user_id = :user_id", params);
    }
}

错误

bad SQL grammar [delete from sample where col1 in (?, ?, ?) and user_id = ?];
nested exception is java.sql.SQLException: No value specified for parameter 3

没有为delete语句展开列表吗？




Answer 1

您可以将代码简化为：

public static void main(String[] args) {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        basicDataSource.setUrl("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true");
        basicDataSource.setUsername("root");
        basicDataSource.setPassword("qwedsa");

        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(basicDataSource);

        ImmutableMap<String, ?> param = ImmutableMap.of("items", newArrayList("a", "b", "c"), "user_id", 1);
        template.update("delete from sample where col1 in (:items) and user_id = :user_id", param);
    }

效果很好