1.for循环insert
long start=System.currentTimeMillis();for(int i=0;i<100000; i++){User user=newUser();
user.setId("id"+ i);
user.setName("name"+ i);
user.setPassword("password"+ i);
userMapper.insert(user);}long end=System.currentTimeMillis();System.out.println("---------------"+(start- end)+"---------------");
<insertid="insert">
INSERT INTO t_user (id, name, password)
VALUES(#{id}, #{name}, #{password})</insert>
时间为380826ms
2.Mybatis batch模式
SqlSession sqlSession= sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH,false);//跟上述sql区别UserMapper userMapper= sqlSession.getMapper(UserMapper.class);long start=System.currentTimeMillis();for(int i=0; i<100000; i++){User user=newUser();
user.setId("id"+ i);
user.setName("name"+ i);
user.setPassword("password"+ i);
userMapper.insert(user);}
sqlSession.commit();long end=System.currentTimeMillis();System.out.println("---------------"+(start- end)+"---------------");
<insertid="insert">
INSERT INTO t_user (id, name, password)
VALUES(#{id}, #{name}, #{password})</insert>
时间为203660ms
3.批量foreach插入
long start=System.currentTimeMillis();List<User> userList=newArrayList<>();for(int i=0; i<100000; i++){User user=newUser();
user.setId("id"+ i);
user.setName("name"+ i);
user.setPassword("password"+ i);
userList.add(user);}
userMapper.insertBatch(userList);long end=System.currentTimeMillis();System.out.println("---------------"+(start- end)+"---------------");
<insertid="insertBatch"parameterType="java.util.List">
INSERT INTO t_user
(id, name, password)
VALUES<foreachcollection="userList"item="user"separator=",">
(#{user.id}, #{user.name}, #{user.password})</foreach></insert>
时间为8706ms
插入三种方式,文章参考:https://segmentfault.com/a/1190000021290975?utm_source=tag-newest
如果批量增加数据量较多时 建议采用batch模式,foreach一次性插入数据量建议10-100条,for循环是最不建议的方式,需要频繁的建立关闭数据库连接,比较耗时。