Mybaties批量操作
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>
测试
@Test
public 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>
测试:
@Test
public 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();
}
}