第一种:普通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配置
<insert id="insert">
INSERT INTO t_user (id, name, del_flag)
VALUES(#{id}, #{name}, #{delFlag})
</insert>
第三种: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配置
MySQL
<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>
Oracle
<insert id="insertBatch">
INSERT ALL
<foreach collection ="list" item="user" index="index">
INTO t_user(id, name, del_flag) VALUES
(#{user.id}, #{user.name}, #{user.delFlag})
</foreach >
SELECT 1 FROM DUAL
</insert>
第四种: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">
begin
<foreach collection ="list" item="user" >
INSERT INTO t_user
(id, name, del_flag)
VALUES (#{user.id}, #{user.name}, #{user.delFlag});
</foreach >
end;
</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.
结果对比:
时间有限测试数据较少,有兴趣可以自己测试以下。(不清楚为什么BATCH有时候比单条循环插入还耗时间,请知道的大神不吝赐教,感谢!)