# MyBatis批处理

批处理：就是批量处理的意思，假设有10万条数据，需要向数据库插入，如果使用insert语句一条一条的插入，那效率是很差的。可以利用集合保存批处理的数据，在利用批处理sql一次性完成，这样能很大程度上保证程序的执行效率。

# 导航

# 批处理添加

批量插入的局限性：无法获得插入数据的id
批量的sql语句太长，会被服务器拒绝

# mapper/xml文件如下：

<!--一条sql语句插入多条数据：insert into table values ("",""),("",""),(...)-->
    <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>

1
2
3
4
5
6
7

# java代码：

 @Test
    public void testBatchInsert(){
        SqlSession sqlSession=null;
        try {
            long start= new Date().getTime();
            sqlSession=MyBatisUtils.openSession();
            List<TGoodsEntity> tGoodsEntities=new ArrayList<TGoodsEntity>();
            for (int i = 0; i < 1000; i++) {
                TGoodsEntity goodsEntity=new TGoodsEntity();
                goodsEntity.setCategoryId(25);
                goodsEntity.setCurrentPrice(5f);
                goodsEntity.setDiscount(5f/10);
                goodsEntity.setOriginalCost(10f);
                goodsEntity.setIsFreeDelivery(1);
                goodsEntity.setTitle("测试数据"+i);
                goodsEntity.setSubTitle("测试数据");
                tGoodsEntities.add(goodsEntity);
            }
            int insert = sqlSession.insert("goods.batchInsert", tGoodsEntities);
            sqlSession.commit();
            System.out.println("添加成功了"+ insert+"条数据");
            long end = new Date().getTime();
            System.out.println("批量插入时间：" + (end - start) + "毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null!=sqlSession) {
                MyBatisUtils.closeSession(sqlSession);
            }
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

批量插入时间：1877毫秒

# 一条一条的插入

<insert id="testInsert" parameterType="com.torey.mybatis.entity.TGoodsEntity" flushCache="true">
        insert into t_goods (title,sub_title,original_cost,current_price,discount,is_free_delivery,category_id) values
            (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery},#{categoryId})
    </insert>

1
2
3
4

@Test
    public void testInsert(){
        SqlSession sqlSession=null;
        try {
            long start= new Date().getTime();
            sqlSession=MyBatisUtils.openSession();
            for (int i = 0; i < 1000; i++) {
                TGoodsEntity goodsEntity=new TGoodsEntity();
                goodsEntity.setCategoryId(25);
                goodsEntity.setCurrentPrice(5f);
                goodsEntity.setDiscount(5f/10);
                goodsEntity.setOriginalCost(10f);
                goodsEntity.setIsFreeDelivery(1);
                goodsEntity.setTitle("测试数据"+i);
                goodsEntity.setSubTitle("测试数据");
                sqlSession.insert("goods.testInsert", goodsEntity);
            }
            sqlSession.commit();
            long end = new Date().getTime();
            System.out.println("一条一条的插入：" + (end - start) + "毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null!=sqlSession) {
                MyBatisUtils.closeSession(sqlSession);
            }
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

一条一条的插入：3048毫秒

# 批量删除

<delete id="batchDelete" parameterType="java.util.List">
        delete from t_goods where goods_id in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

1
2
3
4
5
6

@Test
    public void testBatchDelete(){
        SqlSession sqlSession=null;
        try {
            long start= new Date().getTime();
            sqlSession=MyBatisUtils.openSession();
            List<Integer> aaList=new ArrayList<Integer>();
            for (int i = 0; i < 2000; i++) {
                aaList.add(i);
            }
            int delete = sqlSession.delete("goods.batchDelete", aaList);
            sqlSession.commit();
            System.out.println("删除成功了"+ delete+"条数据");
            long end = new Date().getTime();
            System.out.println("批量删除：" + (end - start) + "毫秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null!=sqlSession) {
                MyBatisUtils.closeSession(sqlSession);
            }
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

14MyBatis配置C3P0连接池
 16MyBatis注解开发

# 支持我-微信扫一扫-加入微信公众号

Aseven公众号