mybatis 批插入比较

mybatis 批插入比较

方式

批量插入是一种高效的数据库操作方式，可以显著提高数据插入的性能。在MyBatis中，有多种方法可以实现批量插入，下面是其中的几种：

1.使用foreach标签

使用foreach标签可以将多条SQL语句合并成一条，从而实现批量插入。具体步骤如下： - 在Mapper.xml文件中编写SQL语句，并使用foreach标签包裹插入语句。 - 在Java代码中调用Mapper接口中的批量插入方法，传入一个List集合作为参数，该集合中存放待插入的数据。 以下是示例代码： Mapper.xml文件：

<insert id="batchInsert" parameterType="com.cy.dao.model.TestUser">
    insert into test_user (id, login_name, user_name
      )
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id,jdbcType=INTEGER}, #{item.loginName,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR})
    </foreach>
  </insert>

Java代码：

List<MyObject> list = new ArrayList<>();
// 添加待插入的数据
int result = mapper.batchInsert(list);

2.使用SqlSession的批量操作方法

SqlSession提供了多种批量操作方法，例如 insert() 、 update() 和 delete() 等。具体步骤如下： - 调用SqlSession的批量操作方法，传入一个Mapper接口和方法名，以及一个Collection集合作为参数，该集合中存放待插入的数据。 以下是示例代码：

List<MyObject> list = new ArrayList<>();
// 添加待插入的数据
int result = sqlSession.insert("MyMapper.batchInsert", list);
sqlSession.commit();

需要注意的是，以上两种方法都可以实现批量插入，但其性能和效率可能会有所不同。具体使用哪种方法，需要根据具体情况进行选择。

实践

部分代码

• 通用批处理接口

import java.util.Collection;

public interface IBatchMapper {
    <T> int batchInsert(Collection<T> list , String statement);
    <T> int batchUpdate(Collection<T> list , String statement);
}

• 通用批处理接口实现类

注意该种方式需要加入事务，本人撤销事务注解，执行时间超长，应该是没有进行批量执行。

import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.Collection;

@Slf4j
@Component
public class BatchMapper implements IBatchMapper{
    @Resource
    private SqlSessionFactory sqlSessionFactory;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public <T> int batchInsert(Collection<T> list , String statement){
        int result = 0;
        SqlSession sqlSession = null;
        try{
            sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
            for (T obj : list) {
                result += sqlSession.insert(statement, obj);
            }
            sqlSession.commit();
        } catch (Exception e) {
            log.error("批插入失败：{}" , JSONUtil.toJsonStr(list) , e);
            throw new RuntimeException(e);
        } finally {
            if (sqlSession != null) {
                sqlSession.close(); // 关闭SqlSession，释放资源
            }
        }
        return result;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public <T> int batchUpdate(Collection<T> list, String statement) {
        int result = 0;
        SqlSession sqlSession = null;
        try{
            sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
            for (T obj : list) {
                result += sqlSession.update(statement, obj);
            }
            sqlSession.commit();
        } catch (Exception e) {
            log.error("批更新失败：{}" , JSONUtil.toJsonStr(list) , e);
            throw new RuntimeException(e);
        } finally {
            if (sqlSession != null) {
                sqlSession.close(); // 关闭SqlSession，释放资源
            }
        }
        return result;
    }
}

• 基础biz类，给业务调用，数据分批

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;

import javax.annotation.Resource;
import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.List;

public class BaseEventBiz<M , T> {
    @Autowired
    private ApplicationContext applicationContext;
    @Resource
    private BatchMapper batchMapper;

    protected int batchCount = 2000;
    @SuppressWarnings("unchecked")
    Class<M> getMapperClass() {
        ParameterizedType parameterizedType = (ParameterizedType) getClass().getGenericSuperclass();
        return (Class<M>) parameterizedType.getActualTypeArguments()[0];
    }

    public M getMapper (){
        return applicationContext.getBean(getMapperClass());
    }

    public int batchInsert(List<T> list) {
        String statement =  getMapperClass().getName() + ".insert";
        int result = 0;
        List<T> l = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            l.add(list.get(i));
            if (i % batchCount == 0) {
                result += batchMapper.batchInsert(l, statement);
                l.clear();
            }
        }
        if (l.size()>0) {
            result += batchMapper.batchInsert(l , statement);
        }
        return result;
    }
    public int batchUpdate(List<T> list) {
        String statement =  getMapperClass().getName() + ".update";
        int result = 0;
        List<T> l = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            l.add(list.get(i));
            if (i % batchCount == 0) {
                result += batchMapper.batchUpdate(l, statement);
                l.clear();
            }
        }
        if (l.size()>0) {
            result += batchMapper.batchUpdate(l , statement);
        }
        return result;
    }
}

• 用户表dao层，mapper及model

package com.cy.dao.model;

public class TestUser {
    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column test_user.id
     *
     * @mbg.generated
     */
    private Integer id;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column test_user.login_name
     *
     * @mbg.generated
     */
    private String loginName;

    /**
     *
     * This field was generated by MyBatis Generator.
     * This field corresponds to the database column test_user.user_name
     *
     * @mbg.generated
     */
    private String userName;

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column test_user.id
     *
     * @return the value of test_user.id
     *
     * @mbg.generated
     */
    public Integer getId() {
        return id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column test_user.id
     *
     * @param id the value for test_user.id
     *
     * @mbg.generated
     */
    public void setId(Integer id) {
        this.id = id;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column test_user.login_name
     *
     * @return the value of test_user.login_name
     *
     * @mbg.generated
     */
    public String getLoginName() {
        return loginName;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column test_user.login_name
     *
     * @param loginName the value for test_user.login_name
     *
     * @mbg.generated
     */
    public void setLoginName(String loginName) {
        this.loginName = loginName == null ? null : loginName.trim();
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method returns the value of the database column test_user.user_name
     *
     * @return the value of test_user.user_name
     *
     * @mbg.generated
     */
    public String getUserName() {
        return userName;
    }

    /**
     * This method was generated by MyBatis Generator.
     * This method sets the value of the database column test_user.user_name
     *
     * @param userName the value for test_user.user_name
     *
     * @mbg.generated
     */
    public void setUserName(String userName) {
        this.userName = userName == null ? null : userName.trim();
    }
}

import com.cy.dao.model.TestUser;
import com.cy.dao.model.TestUserExample;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TestUserMapper {
    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    long countByExample(TestUserExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int deleteByExample(TestUserExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int deleteByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int insert(TestUser record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int insertSelective(TestUser record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    List<TestUser> selectByExample(TestUserExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    TestUser selectByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int updateByExampleSelective(@Param("record") TestUser record, @Param("example") TestUserExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int updateByExample(@Param("record") TestUser record, @Param("example") TestUserExample example);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int updateByPrimaryKeySelective(TestUser record);

    /**
     * This method was generated by MyBatis Generator.
     * This method corresponds to the database table test_user
     *
     * @mbg.generated
     */
    int updateByPrimaryKey(TestUser record);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.dao.mapper.TestUserMapper">
  <resultMap id="BaseResultMap" type="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="login_name" jdbcType="VARCHAR" property="loginName" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
  </resultMap>
  <sql id="Example_Where_Clause">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <where>
      <foreach collection="oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Update_By_Example_Where_Clause">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <where>
      <foreach collection="example.oredCriteria" item="criteria" separator="or">
        <if test="criteria.valid">
          <trim prefix="(" prefixOverrides="and" suffix=")">
            <foreach collection="criteria.criteria" item="criterion">
              <choose>
                <when test="criterion.noValue">
                  and ${criterion.condition}
                </when>
                <when test="criterion.singleValue">
                  and ${criterion.condition} #{criterion.value}
                </when>
                <when test="criterion.betweenValue">
                  and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                </when>
                <when test="criterion.listValue">
                  and ${criterion.condition}
                  <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                    #{listItem}
                  </foreach>
                </when>
              </choose>
            </foreach>
          </trim>
        </if>
      </foreach>
    </where>
  </sql>
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    id, login_name, user_name
  </sql>
  <select id="selectByExample" parameterType="com.cy.dao.model.TestUserExample" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select
    <if test="distinct">
      distinct
    </if>
    <include refid="Base_Column_List" />
    from test_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select 
    <include refid="Base_Column_List" />
    from test_user
    where id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    delete from test_user
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <delete id="deleteByExample" parameterType="com.cy.dao.model.TestUserExample">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    delete from test_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </delete>
  <insert id="insert" parameterType="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    insert into test_user (id, login_name, user_name
      )
    values (#{id,jdbcType=INTEGER}, #{loginName,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    insert into test_user
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        id,
      </if>
      <if test="loginName != null">
        login_name,
      </if>
      <if test="userName != null">
        user_name,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="loginName != null">
        #{loginName,jdbcType=VARCHAR},
      </if>
      <if test="userName != null">
        #{userName,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>
  <select id="countByExample" parameterType="com.cy.dao.model.TestUserExample" resultType="java.lang.Long">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    select count(*) from test_user
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
  </select>
  <update id="updateByExampleSelective" parameterType="map">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update test_user
    <set>
      <if test="record.id != null">
        id = #{record.id,jdbcType=INTEGER},
      </if>
      <if test="record.loginName != null">
        login_name = #{record.loginName,jdbcType=VARCHAR},
      </if>
      <if test="record.userName != null">
        user_name = #{record.userName,jdbcType=VARCHAR},
      </if>
    </set>
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByExample" parameterType="map">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update test_user
    set id = #{record.id,jdbcType=INTEGER},
      login_name = #{record.loginName,jdbcType=VARCHAR},
      user_name = #{record.userName,jdbcType=VARCHAR}
    <if test="_parameter != null">
      <include refid="Update_By_Example_Where_Clause" />
    </if>
  </update>
  <update id="updateByPrimaryKeySelective" parameterType="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update test_user
    <set>
      <if test="loginName != null">
        login_name = #{loginName,jdbcType=VARCHAR},
      </if>
      <if test="userName != null">
        user_name = #{userName,jdbcType=VARCHAR},
      </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    update test_user
    set login_name = #{loginName,jdbcType=VARCHAR},
      user_name = #{userName,jdbcType=VARCHAR}
    where id = #{id,jdbcType=INTEGER}
  </update>
</mapper>

import com.cy.dao.model.TestUser;

import java.util.List;

public interface TestUserCustomMapper {
    int batchInsert(List<TestUser> list);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.dao.mapper.custom.TestUserCustomMapper">
  <resultMap id="BaseResultMap" type="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="login_name" jdbcType="VARCHAR" property="loginName" />
    <result column="user_name" jdbcType="VARCHAR" property="userName" />
  </resultMap>
  <sql id="Base_Column_List">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    id, login_name, user_name
  </sql>
  <insert id="batchInsert" parameterType="com.cy.dao.model.TestUser">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
    -->
    insert into test_user (id, login_name, user_name
      )
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id,jdbcType=INTEGER}, #{item.loginName,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR})
    </foreach>
  </insert>
</mapper>

• 用户表插入处理类

import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;

@Service
public class TestUserEventBiz extends BaseEventBiz<TestUserMapper, TestUser> {
    @Resource
    private TestUserCustomMapper testUserCustomMapper;

    public int customBatchInsert(List<TestUser> list){
        int result = 0;
        List<TestUser> l = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            l.add(list.get(i));
            if (i % batchCount == 0) {
                testUserCustomMapper.batchInsert(l);
                l.clear();
            }
        }
        if (l.size()>0) {
            testUserCustomMapper.batchInsert(l);
        }
        return result;
    }

    public int insert(TestUser testUser) {
        return getMapper().insert(testUser);
    }
}

测试

• 测试类

  import com.cy.biz.TestUserEventBiz;
  import com.cy.dao.model.TestUser;
  import lombok.extern.slf4j.Slf4j;
  import org.junit.Before;
  import org.junit.FixMethodOrder;
  import org.junit.Test;
  import org.junit.runner.RunWith;
  import org.junit.runners.MethodSorters;
  import org.springframework.boot.test.context.SpringBootTest;
  import org.springframework.test.context.junit4.SpringRunner;
  import org.springframework.util.StopWatch;
  
  import javax.annotation.Resource;
  import java.util.ArrayList;
  import java.util.Collection;
  import java.util.List;
  
  @Slf4j
  @RunWith(SpringRunner.class)
  @SpringBootTest(classes = RedisLockApplication.class)
  @FixMethodOrder(MethodSorters.NAME_ASCENDING)
  public class UserTest {
      @Resource
      private TestUserEventBiz testUserEventBiz;
  
      private List<TestUser> list1W;
      private List<TestUser> list10W;
      private List<TestUser> list100W;
      @Before
      public void init(){
          list1W = new ArrayList<>();
          list10W = new ArrayList<>();
          list100W = new ArrayList<>();
          int count = 1000000;
          while(count > 0){
              String s = String.valueOf(count);
              TestUser testUser = new TestUser();
              testUser.setUserName(s);
              testUser.setLoginName(s);
              if (count > 990000) {
                  list1W.add(testUser);
              }
              if (count > 900000) {
                  list10W.add(testUser);
              }
              list100W.add(testUser);
              count -- ;
          }
      }
  
      @Test
      public void aBatchInsert() {
          StopWatch stopWatch = new StopWatch("sqlSession批插入");
          stopWatch.start("1W");
          testUserEventBiz.batchInsert(list1W);
          stopWatch.stop();
          stopWatch.start("10W");
          testUserEventBiz.batchInsert(list10W);
          stopWatch.stop();
          stopWatch.start("100W");
          testUserEventBiz.batchInsert(list100W);
          stopWatch.stop();
          log.info(stopWatch.prettyPrint());
      }
      @Test
      public void bCustomBatchInsert() {
          StopWatch stopWatch = new StopWatch("foreach批插入");
          stopWatch.start("1W");
          testUserEventBiz.customBatchInsert(list1W);
          stopWatch.stop();
          stopWatch.start("10W");
          testUserEventBiz.customBatchInsert(list10W);
          stopWatch.stop();
          stopWatch.start("100W");
          testUserEventBiz.customBatchInsert(list100W);
          stopWatch.stop();
          log.info(stopWatch.prettyPrint());
      }
  }
  

测试结果

2023-06-11 17:50:32.351 INFO  31172 --- [main] com.cy.UserTest - StopWatch 'sqlSession批插入': running time = 41413780000 ns
---------------------------------------------
ns         %     Task name
---------------------------------------------
923910700  002%  1W
3991819400  010%  10W
36498049900  088%  100W

2023-06-11 17:50:47.562 INFO  31172 --- [main] com.cy.UserTest - StopWatch 'foreach批插入': running time = 15136264900 ns
---------------------------------------------
ns         %     Task name
---------------------------------------------
271883200  002%  1W
1653087200  011%  10W
13211294500  087%  100W

比较

以下是使用不同方法进行批量插入的性能效率比较表格，具体数据可能因环境和数据量不同而有所不同，仅供参考：

方法	数据量	耗时
foreach标签	1W	271ms
foreach标签	10W	1653ms
foreach标签	100W	13211ms
SqlSession批量操作方法	1W	924ms
SqlSession批量操作方法	10W	3992ms
SqlSession批量操作方法	100W	36498ms

从上表可以看出，使用foreach标签的性能效率较高。以上数据仅供参考，实际效率可能会受到多种因素的影响。在实际使用中，需要根据具体情况进行选择。