Mybatis学习第11节 -- 动态sql foreach 批量删除

需求

假设我们现在想要将用户勾选中的商铺批量删除或者进行批量其他操作

接口

int deleteShopInList(List<Integer> ids);

映射

在这里注意foreach的collection属性只有list和array两个值可用

<delete id="deleteShopInList">
    DELETE
    FROM
      tb_shop
    WHERE `shop_id` IN
    <foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
      #{item}
    </foreach>
</delete>

测试

@Test
public void testDeleteShopInList() {
    String template = "批量删除结果: %s\n";
    SqlSession session = MyBatisUtil.getSqlSession();
    ShopMapper mapper = session.getMapper(ShopMapper.class);

    List<Integer> ids = Arrays.asList(40, 41);

    System.out.printf(template, mapper.deleteShopInList(ids));
    session.commit();
    session.close();
}

结果

Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@46c670a6]
==> Preparing: DELETE FROM tb_shop WHERE `shop_id` IN ( ? , ? ) 
==> Parameters: 40(Integer), 41(Integer)
<== Updates: 2
批量删除结果: 2

 

总结

一定要细心检查sql语句是不是没有问题, 如果在上述案例中的tb_shop后面多一个反引号`就会报参数设置错误, 还是不容易发现真正错误在哪里的