mybatis batchinsert方法学习

1.表结构
CREATE TABLE t_user ( id varchar(32) CHARACTER SET utf8 NOT NULL COMMENT ‘主键’, name varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘用户名’, del_flag char(1) CHARACTER SET utf8 DEFAULT NULL COMMENT ‘删除标示’, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2.1 jdbc.properties配置mysql.driver=com.mysql.jdbc.Drivermysql.url=jdbc:mysql://127.0.0.1:3306/ssmmysql.username=rootmysql.password=admin#定义初始连接数mysql.initialSize=1#定义最大连接数mysql.maxActive=20#定义最大空闲mysql.maxIdle=20#定义最小空闲mysql.minIdle=1#定义最长等待时间mysql.maxWait=60000
2.2 spring-mybatis.xml
<context:component-scan base-package=“com.win.ssm”/><context:property-placeholder location=“classpath:jdbc.properties”/>
第一种：普通for循环插入
①junit类
@Testpublic 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 INTO t_user (id, name, del_flag) VALUES(#{id}, #{name}, #{delFlag})
第二种：mybatis BATCH模式插入
①junit类
@Testpublic 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类
@Testpublic void testInsertBatch() throws Exception { long start = System.currentTimeMillis(); List 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 INTO t_user (id, name, del_flag) VALUES (#{user.id}, #{user.name}, #{user.delFlag})
特别注意：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