当前位置：首页 > news >正文

Mybatis批量操作

news 2025/3/17 1:43:20

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();}}