MyBatis批量插入
普通for循环
@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) + "---------------"); }
myBatis的BATCH模式插入
@Test public void testInsertBatch2() throws Exception { long start = System.currentTimeMillis(); User user; SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, 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>
foreach方式插入
@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>
batch相对来说比simple快,第三种批量写入是最快的,但第三种会产生问题
mysql默认接受sql的大小是1048576(1M),即第三种方式若数据量超过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.
可通过调整MySQL安装目录下的my.ini文件中[mysqld]段的"max_allowed_packet = 1M"
executorType
- simple,不做特殊事情
- reuse,缓存起来复用
- batch,批量方式
batch方式最快
mapper不过是通过sqlfactory自动实现了原始dao接口和dao实现类
写好配置,连接池,预处理语句,然后调用执行即可
论读书
睁开眼,书在面前 闭上眼,书在心里
睁开眼,书在面前 闭上眼,书在心里