MyBatis——批量插入
今天来简单讲下批量插入。嗯,简言之,从两个批量插入讲起。
1.常规批量插入(通过foreach)
这个插入速度最快,不过会生成很长的SQL,列如:
insert into teacher (tname, age) values (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) , (?,?) …
来用代码说明:
既然是使用foreach,那么就要在映射xml文件配置
<!-- 常规方式批量插入 -->
<insert id="conventionalBulkInsert" parameterType="Teacher">
insert into teacher (tname, age) values
<foreach collection="list" item="teacher" separator="," close=";">
(#{teacher.tname},#{teacher.age})
</foreach>
</insert>
然后,定义一个接口类方法int conventionalBulkInsert(List teacherList)
int conventionalBulkInsert(List<Teacher> teacherList);
在这还需要一些插入的数据(下面也要)
List<Teacher> teachers=new ArrayList<>();
String[] TeacherArr= new String[]{"张三", "李四", "王五", "赵六", "好人"};
@Before
public void setUp() {
teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
Random random = new Random();
// 构造测试数据
for(int i = 0; i < 10000; i++) {
Teacher teacher = new Teacher();
int idx = random.nextInt(TeacherArr.length);
teacher.setTname(TeacherArr[idx] +"_"+ (i + 1));
teacher.setAge(i+1);
teachers.add(teacher);
}
}
常规批量插入测试
/**
* 常规批量插入。(通过foreach,生成很长的SQL)
*/
@Test
public void testBatchInsertByNormal(){
long start=System.currentTimeMillis();
int rows=teacherDao.conventionalBulkInsert(teachers);
log.info("插入数据行数: " + rows+", 耗时: " + (System.currentTimeMillis() - start));
}
2.使用ExecutorType.BATCH方式执行批量操作
Mybatis内置的ExecutorType有3种,默认的是simple,该模式下它为每个语句的执行创建一个新的预处理语句,单条提交sql;而batch模式重复使用已经预处理的语句,
并且批量执行所有更新语句,显然batch性能将更优;
但batch模式也有自己的问题,比如在Insert操作时,在事务没有提交之前,是没有办法获取到自增的id,这在某型情形下是不符合业务要求的。
代码演示:
/**
* 使用ExecutorType.BATCH方式执行批量操作
*/
@Test
public void testBatchInsertByExecutorType() {
SqlSessionFactory factory = MyBatisTools.getInstance().getSessionFactory();
SqlSession sqlSession = factory.openSession(ExecutorType.BATCH, false);
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
long start = System.currentTimeMillis();
int rows = 0;
int batchSize = 100;
int count = 0;
for(Teacher teacher : teachers) {
rows += teacherDao.insertTeacher(teacher);
count ++;
if(count % batchSize == 0) {
sqlSession.flushStatements();//每100条刷新一次(Preparing: insert into teacher(tname) values (?);)
}
}
sqlSession.flushStatements();
sqlSession.commit();
sqlSession.close();
log.info("插入数据行数: " + rows+", 耗时: " + (System.currentTimeMillis() - start));
}
相对来说插入执行时间长些。