mybatis三种批量插入方式对比

爱被打了一巴掌 2022-05-24 06:52 227阅读 0赞

准备：

1.表结构

<table> 
 <tbody> 
  <tr> 
   <td>CREATE TABLE `t_user` (<br>&nbsp; `id` varchar(32) CHARACTER SET utf8 NOT NULL COMMENT ‘主键’,<br>&nbsp; `name` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘用户名’,<br>&nbsp; `del_flag` char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘删除标示’,<br>&nbsp; PRIMARY KEY (`id`)<br>) ENGINE=InnoDB DEFAULT CHARSET=latin1;</td> 
  </tr> 
 </tbody> 
</table>

2.1 jdbc.properties配置

<table> 
 <tbody> 
  <tr> 
   <td> <pre>mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://127.0.0.1:3306/ssm
mysql.username=root
mysql.password=admin
#定义初始连接数
mysql.initialSize=1
#定义最大连接数
mysql.maxActive=20
#定义最大空闲
mysql.maxIdle=20
#定义最小空闲
mysql.minIdle=1
#定义最长等待时间
mysql.maxWait=60000</pre> </td> 
  </tr> 
 </tbody> 
</table>

2.2 spring-mybatis.xml配置

<table> 
 <tbody> 
  <tr> 
   <td> <pre>&lt;context:component-scan base-package="com.win.ssm"/&gt;
&lt;context:property-placeholder location="classpath:jdbc.properties"/&gt;
&lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"&gt;
    &lt;property name="driverClassName" value="${mysql.driver}"/&gt;
    &lt;property name="url" value="${mysql.url}"/&gt;
    &lt;property name="username" value="${mysql.username}"/&gt;
    &lt;property name="password" value="${mysql.password}"/&gt;
    <em>&lt;!-- </em><em>初始化链接大小</em><em>--&gt;
</em><em>    </em>&lt;property name="initialSize" value="${mysql.initialSize}"/&gt;
    <em>&lt;!-- </em><em>连接池最大数量</em><em>--&gt;
</em><em>    </em>&lt;property name="maxActive" value="${mysql.maxActive}"/&gt;
    <em>&lt;!-- </em><em>连接池最大空闲</em><em>--&gt;
</em><em>    </em>&lt;property name="maxIdle" value="${mysql.maxIdle}"/&gt;
    <em>&lt;!-- </em><em>连接池最小空闲 </em><em>--&gt;
</em><em>    </em>&lt;property name="minIdle" value="${mysql.minIdle}"&gt;&lt;/property&gt;
    <em>&lt;!-- </em><em>获取连接最大等待时间</em><em>--&gt;
</em><em>    </em>&lt;property name="maxWait" value="${mysql.maxWait}"/&gt;
&lt;/bean&gt;
<em>&lt;!-- spring</em><em>与</em><em>mybatis</em><em>整合类 </em><em>--&gt;
</em>&lt;bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"&gt;
    &lt;property name="dataSource" ref="dataSource"/&gt;
    <em>&lt;!-- </em><em>查找接口的别名 </em><em>--&gt;
</em><em>    </em>&lt;property name="typeAliasesPackage" value="com.win"/&gt;
    <em>&lt;!-- </em><em>自动扫描</em><em>mapping.xml</em><em>文件</em><em>--&gt;
</em><em>    </em>&lt;property name="mapperLocations" value="classpath:/mapping/*.xml"/&gt;
&lt;/bean&gt;

&lt;bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate"&gt;
    &lt;constructor-arg index="0" ref="sqlSessionFactory" /&gt;
    <em>&lt;!--&lt;constructor-arg index="1" value="BATCH" /&gt;--&gt;
</em>&lt;/bean&gt;

<em>&lt;!-- </em><em>扫描</em><em>DAO</em><em>接口 </em><em>--&gt;
</em>&lt;bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer"&gt;
    &lt;property name="basePackage" value="com.win.ssm.dao"/&gt;
    &lt;property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/&gt;
&lt;/bean&gt;
<em>&lt;!-- </em><em>事务管理 </em><em>--&gt;
</em>&lt;bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager"&gt;
    &lt;property name="dataSource" ref="dataSource"/&gt;
&lt;/bean&gt;</pre> </td> 
  </tr> 
 </tbody> 
</table>

第一种：普通for循环插入

①junit类

<table> 
 <tbody> 
  <tr> 
   <td> <pre>@Test
public void testInsertBatch2() throws Exception {
    long start = System.currentTimeMillis();
    User user;
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(false);
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    for (int i = 0; i &lt; 500; i++) {
        user = new User();
        user.setId("test" + i);
        user.setName("name" + i);
        user.setDelFlag("0");
        mapper.insert(user);
    }
    sqlSession.commit();
    long end = System.currentTimeMillis();
    System.<em>out</em>.println("---------------" + (start - end) + "---------------");
}</pre> </td> 
  </tr> 
 </tbody> 
</table>

②xml配置

<table> 
 <tbody> 
  <tr> 
   <td> <pre>&lt;insert id="insert"&gt;
    INSERT INTO t_user (id, name, del_flag)
          VALUES(#{id}, #{name}, #{delFlag})
&lt;/insert&gt;</pre> </td> 
  </tr> 
 </tbody> 
</table>

第二种：mybatis BATCH模式插入

①junit类

<table> 
 <tbody> 
  <tr> 
   <td> <pre>@Test
public void testInsertBatch2() throws Exception {
    long start = System.currentTimeMillis();
    User user;
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.<em>BATCH</em>, false);//跟上述sql区别
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    for (int i = 0; i &lt; 500; i++) {
        user = new User();
        user.setId("test" + i);
        user.setName("name" + i);
        user.setDelFlag("0");
        mapper.insert(user);
    }
    sqlSession.commit();
    long end = System.<em>currentTimeMillis</em>();
    System.<em>out</em>.println("---------------" + (start - end) + "---------------");
}</pre> </td> 
  </tr> 
 </tbody> 
</table>

②xml配置与第一种②中使用相同

第三种：foreach方式插入

①junit类

<table> 
 <tbody> 
  <tr> 
   <td> <pre>@Test
public void testInsertBatch() throws Exception {
    long start = System.currentTimeMillis();
    List&lt;User&gt; list = new ArrayList&lt;&gt;();
    User user;
    for (int i = 0; i &lt; 10000; i++) {
        user = new User();
        user.setId("test" + i);
        user.setName("name" + i);
        user.setDelFlag("0");
        list.add(user);
    }
    userService.insertBatch(list);
    long end = System.currentTimeMillis();
    System.<em>out</em>.println("---------------" + (start - end) + "---------------");
}</pre> </td> 
  </tr> 
 </tbody> 
</table>

②xml配置

<table> 
 <tbody> 
  <tr> 
   <td> <pre>&lt;insert id="insertBatch"&gt;
    INSERT INTO t_user
            (id, name, del_flag)
    VALUES
    &lt;foreach collection ="list" item="user" separator =","&gt;
         (#{user.id}, #{user.name}, #{user.delFlag})
    &lt;/foreach &gt;
&lt;/insert&gt;</pre> </td> 
  </tr> 
 </tbody> 
</table>

特别注意：mysql默认接受sql的大小是 1048576(1M)， 即第三种方式若数据量超过1M会报如下异常：（可通过调整MySQL安装目录下的my.ini文件中\[mysqld\]段的＂max\_allowed\_packet = 1M＂）

<table> 
 <tbody> 
  <tr> 
   <td> <p> <span><span>nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 &gt; 1048576).</span></span></p> <p> <span><span>You can change this value on the server by setting the max_allowed_packet’ variable.</span></span></p> </td> 
  </tr> 
 </tbody> 
</table>

结果对比：

时间有限测试数据较少，有兴趣可以自己测试以下。（不清楚为什么BATCH有时候比单条循环插入还耗时间，请知道的大神不吝赐教，感谢！）