MyBatis最佳实践之——批量操作

最新推荐文章于 2024-04-30 17:28:01 发布

翠花也老了

最新推荐文章于 2024-04-30 17:28:01 发布

阅读量2.5k

点赞数 1

分类专栏： Mybatis 实践与应用文章标签： mybatis java mysql

本文链接：https://blog.csdn.net/yuiezt/article/details/128859148

版权

Mybatis 实践与应用专栏收录该内容

8 篇文章 1 订阅

订阅专栏

我们在项目中会有一些批量操作的场景，比如导入文件批量处理数据的情况（批量新增商户、批量修
改商户信息），当数据量非常大，比如超过几万条的时候，在Java代码中循环发送SQL到数据库执行肯
定是不现实的，因为这个意味着要跟数据库创建几万次会话。
即使在同一个连接中，也有重复编译和执行SQL的开销。

批量插入

一般思路：使用for循环，循环调用insert语句进行插入操作。

@Test
    void testBatchInsert() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        for (int i=1;i<=30000;i++){
            //创建Session
            deviceMapper.insert(Device.builder().id(i).build());
            //每次操作提交
            sqlSession.commit();
        }
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");

    }

执行结果：

start:1675532044001
end:1675532082837
总耗时：38836ms
总耗时：38s

这是的xml配置是这样的：

    <insert id="insert">
        insert into t_device (id, sn) values (
        #{id}, #{sn}
        )
    </insert>

它相当于是循环调用insert语句，每次插入一条，每次提交。
而批量插入的SQL语法，是在values后面增加插入的值，如下：

    <insert id="batchInsert" parameterType="java.util.List">
        insert into t_device (id,sn) values
        <foreach collection="list" item="device" separator=",">
            (#{device.id},#{device.sn})
        </foreach>
    </insert>

测试代码如下：

@Test
    void testBatchInsertBatch() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        List<Device> list = new ArrayList<Device>();
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        for (int i=1;i<=30000;i++){
            list.add(Device.builder().id(i).build());
        }
        //执行一次，提交
        deviceMapper.batchInsert(list);
        sqlSession.commit();
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");
    }

执行结果如下：

start:1675532213592
end:1675532214973
总耗时：1381ms
总耗时：1s

可以看到，动态SQL批量插入效率比循环发送SQL执行要高很多。

批量更新

批量更新也可以像上述插入一样通过java代码的for实现，每次更新提交：

    <update id="update" parameterType="com.lk.tool.bean.Device">
        update t_device set sn = #{sn} where id = #{id}
    </update>

测试代码如下：

    @Test
    void testUpdateBatch() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        List<Device> list = new ArrayList<Device>();
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        for (int i=1;i<=30000;i++){
            deviceMapper.update(Device.builder().id(i).sn("CHYLL-****"+i).build());
            //每次更新提交
            sqlSession.commit();
        }
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");
    }

执行结果：

start:1675532637217
end:1675532681889
总耗时：44672ms
总耗时：44s

第二种通过case when，来匹配id相关的字段值

    <update id="updateBatchGood" parameterType="java.util.List">
        update t_device set sn =
        <foreach collection="list" item="device" separator=" " open="case id" close="end">
            when #{device.id} then #{device.sn}
        </foreach>
        where id in
        <foreach collection="list" item="device" separator="," open="(" close=")">
            #{device.id}
        </foreach>
    </update>

测试类代码如下：

    @Test
    void testUpdateBatchGood() throws IOException {
       InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        List<Device> list = new ArrayList<Device>();
        for (int i=1;i<=30000;i++){
            list.add(Device.builder().id(i).sn("CHYLL-0000"+i).build());
        }
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        deviceMapper.updateBatchGood(list);
        sqlSession.commit();
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");
    }

执行结果：

start:1675532725000
end:1675532750446
总耗时：25446ms
总耗时：25s

批量删除

像上述插入、更新一样通过java代码的for实现，每次更新提交：

    <delete id="delete" parameterType="com.lk.tool.bean.Device">
        delete from t_device where id = #{id}
    </delete>

测试代码：

    @Test
    void testDeleteBatch() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        List<Device> list = new ArrayList<Device>();
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        for (int i=1;i<=30000;i++){
            deviceMapper.delete(Device.builder().id(i).sn("CHYLL-0000"+i).build());
            sqlSession.commit();
        }
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");
    }

执行结果：

start:1675533069870
end:1675533118258
总耗时：48388ms
总耗时：48s

再来看批处理操作：

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

    @Test
    void testDeleteGood() throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        List<Device> list = new ArrayList<Device>();
        for (int i=1;i<=30000;i++){
            list.add(Device.builder().id(i).sn("CHYLL-0000"+i).build());
        }
        long startCurrentTime = System.currentTimeMillis();
        System.out.println("start:"+startCurrentTime);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        DeviceMapper deviceMapper = sqlSession.getMapper(DeviceMapper.class);
        deviceMapper.deleteBatch(list);
        sqlSession.commit();
        sqlSession.close();
        long endCurrentTime = System.currentTimeMillis();
        System.out.println("end:"+endCurrentTime);
        System.out.println("总耗时："+ (endCurrentTime-startCurrentTime) +"ms");
        System.out.println("总耗时："+(endCurrentTime-startCurrentTime)/1000+"s");
    }

执行结果：

start:1675533352386
end:1675533353915
总耗时：1529ms
总耗时：1s

注意：
然MyBatis的动态标签的批量操作在数据量特别大的时候，拼接出来的SQL语句过大。
MySQL的服务端对于接收的数据包有大小限制，max_allowed_packet 默认是 4M，需要修改默认配
置或者手动地控制条数，才可以解决这个问题。

最后附上数据库表结构：

CREATE TABLE `t_device` (
  `id` int NOT NULL,
  `sn` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci