由于MyBatis的本质是对JDBC的封装，所以从JDBC的原生来看此问题~
一、原生JDBC插入两种方式
conn.setAutoCommit(false); //设置手动提交 //预编译sql对象,只编译一回 PreparedStatement ps = conn.prepareStatement("insert into tb_user (name) values(?)"); for (int i = 0; i < stuNum; i++) { ps.setString(1,name); ps.executeUpdate(); } conn.commit();//执行 conn.close();
conn.setAutoCommit(false); //设置手动提交 //预编译sql对象,只编译一回 PreparedStatement ps = conn.prepareStatement("insert into tb_user (name) values(?)"); for (int i = 0; i < stuNum; i++) { ps.setString(1,name); ps.addBatch();//添加到批次 } ps.executeBatch();//提交批处理 conn.commit();//执行 conn.close();
Ps：明显前者比后者效率低很多。
二、MyBatis底层批量插入SQL原理
方式一：
INSERT INTO person(username, email, gender) VALUES("wangwu1", "wangwu1@qq.com", "F"), VALUES("wangwu2", "wangwu2@qq.com", "F"), ……
方式二：
INSERT INTO person(username, email, gender) VALUES("wangwu1", "wangwu1@qq.com", "F"); INSERT INTO person(username, email, gender) VALUES("wangwu2", "wangwu2@qq.com", "F"); ……
Ps：推荐方式一。
三、纯 MyBatis 批量插入
1）对应【二、方式一】（推荐）
<insert id="addEmps"> INSERT INTO tb1_emplyee(last_name,email,gender,d_id) VALUES <foreach collection="emps" item="emp" separator=","> (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}) </foreach> </insert>
（2）对应【二、方式二】（注意：需要添加连接属性，否则ERROR）（不推荐）
<insert id="addEmps"> <foreach collection="emps" item="emp" separator=";"> INSERT INTO tb1_emplyee(last_name,email,gender,d_id) VALUES (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}) </foreach> </insert>
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true
（3）通用代码
public void addEmps(@Param("emps") List<Employee> emps);
@Test public void testBatchSave() throws IOException{ SqlSessionFactory sqlSessionFactory = getSqlSessionFactory(); // 获取到的SqlSession不会自动提交数据（处理过） SqlSession openSession = sqlSessionFactory.openSession(); try { EmployeeMapperDymanicSQL mapper=openSession.getMapper(EmployeeMapperDymanicSQL.class); List<Employee> emps=new ArrayList<Employee>(); emps.add(new Employee(null,"Eminem","Eminem@126.com","1",new Department(1))); emps.add(new Employee(null,"2Pac","2Pac@126.com","1",new Department(1))); mapper.addEmps(emps); openSession.commit(); } finally { openSession.close(); } }
四、MyBatis 基于 SqlSession 的 ExecutorType 批量插入
1、Mybatis内置的ExecutorType有3种，默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优。
2、但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的。
3、在测试中使用simple模式提交10000条数据，时间为18248 毫秒，batch模式为5023 ，性能提高70%。
@Test //单条操作耗时 耗时:8584 public void mybatisBatch() { SqlSession session = getSqlSessionFactory().openSession(); try { DeptMapper deptMapper = (DeptMapper) session.getMapper(DeptMapper.class); long start =System.currentTimeMillis(); for (int i = 0; i <10000 ; i++) { SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1); deptMapper.saveSysDept(dept); } long end =System.currentTimeMillis(); System.out.println("耗时:"+(end-start)); } catch (Exception e) { e.printStackTrace(); } finally { session.commit(); session.close(); } } @Test //傻瓜式批量但非BATCH批量耗时 耗时:938 public void saveDeptBatchOne() { SqlSession session = getSqlSessionFactory().openSession(); try { DeptMapper deptMapper = (DeptMapper) session.getMapper(DeptMapper.class); long start =System.currentTimeMillis(); List<SysDept> deptList=new ArrayList<SysDept>(); for (int i = 0; i <100000 ; i++) { SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1); deptList.add(dept); if(i%500==0){ deptMapper.saveDeptBatch(deptList); deptList.clear(); } } deptMapper.saveDeptBatch(deptList); long end =System.currentTimeMillis(); System.out.println("耗时:"+(end-start)); } catch (Exception e) { e.printStackTrace(); } finally { session.commit(); session.close(); } } @Test //傻瓜式批量+BATCH批量耗时 耗时:822 public void saveDeptBatchTwo() { //设置ExecutorType.BATCH原理：把SQL语句发个数据库，数据库预编译好，数据库等待需要运行的参数，接收到参数后一次运行，ExecutorType.BATCH只打印一次SQL语句，多次设置参数步骤， SqlSession session = getSqlSessionFactory().openSession(ExecutorType.BATCH); try { DeptMapper deptMapper = (DeptMapper) session.getMapper(DeptMapper.class); long start =System.currentTimeMillis(); List<SysDept> deptList=new ArrayList<SysDept>(); for (int i = 0; i <100000; i++) { SysDept dept=new SysDept(UUID.randomUUID().toString().substring(1,6), 1, new Date(), new Date(), 1); deptList.add(dept); if(i%500==0){ deptMapper.saveDeptBatch(deptList); deptList.clear(); } } deptMapper.saveDeptBatch(deptList); long end =System.currentTimeMillis(); System.out.println("耗时:"+(end-start)); } catch (Exception e) { e.printStackTrace(); } finally { session.commit(); session.close(); } }
五、Spring + MyBatis 批量插入
待更新...
