2021-07-30

第一种:普通for循环插入

 ①junit类

@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 < 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.out.println("---------------" + (start - end) + "---------------");
}

  ②xml配置

<insert id="insert">
    INSERT INTO t_user (id, name, del_flag)
          VALUES(#{id}, #{name}, #{delFlag})
</insert>


第二种:mybatis BATCH模式插入

 ①junit类


@Test
public void testInsertBatch2() throws Exception {
    long start = System.currentTimeMillis();
    User user;
    SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);//跟上述sql区别
    UserDao mapper = sqlSession.getMapper(UserDao.class);
    for (int i = 0; i < 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.out.println("---------------" + (start - end) + "---------------");
}


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

第三种:foreach方式插入

 ①junit类

@Test
public void testInsertBatch() throws Exception {
    long start = System.currentTimeMillis();
    List<User> list = new ArrayList<>();
    User user;
    for (int i = 0; i < 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.out.println("---------------" + (start - end) + "---------------");
}


 ②xml配置

<insert id="insertBatch">
    INSERT INTO t_user
            (id, name, del_flag)
    VALUES
    <foreach collection ="list" item="user" separator =",">
         (#{user.id}, #{user.name}, #{user.delFlag})
    </foreach >
</insert>

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

nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (5677854 > 1048576).

You can change this value on the server by setting the max_allowed_packet' variable.

结果对比:

第一种 第二种 第三种
500条 7742 7388 622
1000条 15290 15078 746
5000条 78011 177350 1172
10000条 397472 201180 1205
上一篇:System的一些方法


下一篇:零拷贝速度测试