Mybatis批量操作解析

た入场券 2023-10-08 16:01 28阅读 0赞

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

/**
         * 循环批量插入
         */
        @Test
        public void testInsertOneByOne() {
        
            long start = System.currentTimeMillis();
            int count = 12000;
            for (int i=2000; i< count; i++) {
        
                Blog blog = new Blog();
                blog.setBid(i);
                blog.setName("name"+i);
                blog.setAuthorId(i);
                mapper.insertBlog(blog);
            }
            session.commit();
            session.close();
            long end = System.currentTimeMillis();
            System.out.println("循环批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
        }

在MyBatis里面是支持批量的操作的，包括批量的插入、更新、删除。我们可以直接传入一个 List. Set、Map或者数组，配合动态SQL的标签，MyBatis 会自动帮我们生成语法正确的SQL语句。

## 批量插入 ##

批量插入的语法是这样的，只要在values后面增加插入的值就可以了。  
![在这里插入图片描述][541eb0743beb4d39ab12806c548cc6b8.png]  
在Mapper 文件里面，我们使用foreach标签拼接values 部分的语句:

<insert id="insertBlogList" parameterType="java.util.List">
            insert into blog (bid, name, author_id)
            values
            <foreach collection="list" item="blogs" index="index"  separator=",">
                ( #{
        blogs.bid},#{
        blogs.name},#{
        blogs.authorId} )
            </foreach>
        </insert>

Java代码里面，直接传入一个List类型的参数。

/**
         * MyBatis 动态SQL批量插入
         * @throws IOException
         */
        @Test
        public void testInsert() throws IOException {
        
            long start = System.currentTimeMillis();
            int count = 12000;
            List<Blog> list = new ArrayList<Blog>();
            for (int i=2000; i< count; i++) {
        
                Blog blog = new Blog();
                blog.setBid(i);
                blog.setName("name"+i);
                blog.setAuthorId(i);
                list.add(blog);
            }
            mapper.insertBlogList(list);
            session.commit();
            session.close();
            long end = System.currentTimeMillis();
            System.out.println("动态SQL批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
        }

插入一万条大约耗时1秒钟。  
可以看到，**动态SQL批量插入效率要比循环发送SQL执行要高得多**。  
最关键的地方就在于**减少了跟数据库交互的次数，并且避免了开启和结束事务的时间消耗**。

## 批量更新 ##

批量更新的语法是这样的，通过case when，来匹配 id相关的字段值。  
![在这里插入图片描述][0182d8aad4c245f7969d645d1e54b14f.png]  
![在这里插入图片描述][4111ae0b23dc449e9acc182df5bf4036.png]  
所以在Mapper文件里面最关键的就是case when和where 的配置。需要注意一下open属性和separator属性。

<update id="updateBlogList">
            update blog set
            name =
            <foreach collection="list" item="blogs" index="index" separator=" " open="case bid" close="end">
                when #{
        blogs.bid} then #{
        blogs.name}
            </foreach>
            ,author_id =
            <foreach collection="list" item="blogs" index="index" separator=" " open="case bid" close="end">
                when #{
        blogs.bid} then #{
        blogs.authorId}
            </foreach>
            where bid in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{
        item.bid,jdbcType=INTEGER}
            </foreach>
        </update>

/**
         * MyBatis 动态SQL批量更新
         * @throws IOException
         */
        @Test
        public void updateBlogList() throws IOException {
        
            long start = System.currentTimeMillis();
            int count = 12000;
            List<Blog> list = new ArrayList<Blog>();
            for (int i=2000; i< count; i++) {
        
                Blog blog = new Blog();
                blog.setBid(i);
                blog.setName("modified name"+i);
                blog.setAuthorId(i);
                list.add(blog);
            }
            mapper.updateBlogList(list);
            session.commit();
            session.close();
            long end = System.currentTimeMillis();
            System.out.println("批量更新"+count+"条，耗时：" + (end -start )+"毫秒");
        }

## 批量删除 ##

批量删除也是类似的。

<delete id="deleteByList" parameterType="java.util.List">
            delete from blog where bid in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{
        item.bid,jdbcType=INTEGER}
            </foreach>
        </delete>

/**
         * 动态SQL批量删除
         * @throws IOException
         */
        @Test
        public void testDelete() throws IOException {
        
            SqlSession session = sqlSessionFactory.openSession();
            try {
        
                BlogMapper mapper = session.getMapper(BlogMapper.class);
                List<Blog> list = new ArrayList<Blog>();
                Blog blog1 = new Blog();
                blog1.setBid(666);
                list.add(blog1);
                Blog blog2 = new Blog();
                blog2.setBid(777);
                list.add(blog2);
                mapper.deleteByList(list);
            } finally {
        
                session.close();
            }
        }

## 缺点 ##

当然 MyBatis 的动态标签的批量操作也是存在一定的缺点的，比如数据量特别大的时候，拼接出来的SQL语句过大。  
MySQL的服务端对于接收的数据包有大小限制，max\_allowed\_packet默认是4M，需要修改默认配置或者手动地控制条数，才可以解决这个问题。

> Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large(7188967>4194304).Youcan change this value on the server by setting the max\_allowed\_packet’ variable.

## 解决缺点 ##

在我们的全局配置文件中，可以配置默认的Executor的类型（默认是SIMPLE)。其中有一种 BatchExecutor。

![在这里插入图片描述][575b947a381c403bb0d03c993ab1eff4.png]  
![在这里插入图片描述][942395f25b7c420d82ba014a3cf87589.png]  
一共有3种  
![在这里插入图片描述][1f4c7d32c279400782387adfefab8b4c.png]

思考:三种类型的区别?(通过doUpdate()方法对比)  
1)SimpleExecutor:每执行一次update或select，就开启一个 Statement对象，用完立刻关闭Statement对象。

2)ReuseExecutor:执行update或select,以sql作为key查找 Statement对象，存在就使用，不存在就创建，用完后，不关闭Statement对象，而是放置于Map内，供下一次使用。简言之，就是重复使用Statement对象。

3)BatchExecutor:执行update （没有select，JDBC批处理不支持select)，将所有sql都添加到批处理中(addBatch())，等待统一执行（executeBatch())，它缓存了多个 Statement对象，每个Statement对象都是addBatch()完毕后，等待逐一执行executeBatch()批处理。与JDBC批处理相同。

executeUpdate()是一个语句访问一次数据库，executeBatch()是一批语句访问一次数据库(具体一批发送多少条SQL跟服务端的max\_allowed\_packet有关)。  
BatchExecutor底层是对JDBC ps.addBatch()和ps. executeBatch()的封装。

[541eb0743beb4d39ab12806c548cc6b8.png]: https://img-blog.csdnimg.cn/541eb0743beb4d39ab12806c548cc6b8.png
[0182d8aad4c245f7969d645d1e54b14f.png]: https://img-blog.csdnimg.cn/0182d8aad4c245f7969d645d1e54b14f.png
[4111ae0b23dc449e9acc182df5bf4036.png]: https://img-blog.csdnimg.cn/4111ae0b23dc449e9acc182df5bf4036.png
[575b947a381c403bb0d03c993ab1eff4.png]: https://img-blog.csdnimg.cn/575b947a381c403bb0d03c993ab1eff4.png
[942395f25b7c420d82ba014a3cf87589.png]: https://img-blog.csdnimg.cn/942395f25b7c420d82ba014a3cf87589.png
[1f4c7d32c279400782387adfefab8b4c.png]: https://img-blog.csdnimg.cn/1f4c7d32c279400782387adfefab8b4c.png