1、批量插入
<!--批量操作-插入--><!-- 相當于INSERT INTO t_goods (c1,c2,c3) VALUES (a1,a2,a3),(b1,b2,b3),(d1,d2,d3),...--><insert id="batchInsert" parameterType="java.util.List">INSERT INTO t_goods (title,sub_title,original_cost,current_price,discount,is_free_delivery,category_id) VALUES<foreach collection="list" item="item" index="index" separator=",">(#{item.title},#{item.subTitle},#{item.originalCost},#{item.currentPrice},#{item.discount},#{item.isFreeDelivery},#{item.categoryId})</foreach></insert>
測試
@Testpublic void batchInsertTest(){SqlSession sqlSession = null;try {sqlSession = MyBatisUtil.getSqlSession();PageHelper.startPage(1, 6);GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);List<Goods> goodsList = new ArrayList<Goods>();for (int i = 0; i < 10; i++) {Goods goods = new Goods();goods.setTitle("測試產品"+i);goods.setSubTitle("測試產品副標題"+i);goods.setDiscount(0.88f);goods.setIsFreeDelivery(1);goods.setOriginalCost(155f);goods.setCategoryId(40);goods.setCurrentPrice(888f);goodsList.add(goods);}int insert = mapper.batchInsert(goodsList);if (insert == goodsList.size()) {sqlSession.commit();System.out.println("插入數據成功");}else{System.out.println("插入數據失敗");sqlSession.rollback();}} catch (Exception e) {System.out.println("插入數據失敗");sqlSession.rollback();throw new RuntimeException(e);} finally {sqlSession.close();}}
1、批量插入數據無法獲得插入數據的id,若后續需要使用該id繼續操作，需要想別的辦法
2、若批量插入的數據太多，生成的批量插入SQL過長，可能會被服務器拒絕，可以改為分成幾次來完成
2、批量刪除
<!--批量操作-刪除-->
<!--相當于DELETE FROM t_goods WHERE goods_id IN (a1,a2,a3,a4,a5...)
-->
<delete id="batchDelete" parameterType="java.util.List">DELETE FROM t_goods WHERE goods_id IN<foreach collection="list" item="item" index="index" separator="," open="(" close=")">#{item}</foreach>
</delete>
測試：
@Testpublic void batchDeleteTest(){SqlSession sqlSession = null;try {sqlSession=MyBatisUtil.getSqlSession();GoodsMapper mapper = sqlSession.getMapper(GoodsMapper.class);List<Integer> parameterList = Arrays.asList(2600, 2601,2602);int delete = mapper.batchDelete(parameterList);if (delete == parameterList.size()) {System.out.println("刪除數據成功");sqlSession.commit();}else{System.out.println("刪除數據失敗");sqlSession.rollback();}} catch (Exception e) {System.out.println("刪除數據失敗");sqlSession.rollback();throw new RuntimeException(e);} finally {sqlSession.close();}}
)
)
)
