记一次mybatis大数据量插入

MyBatis文档中写批量插入的时候，是推荐使用

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);  
try {  
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);  
    List<SimpleTableRecord> records = getRecordsToInsert(); // not shown  
   
    BatchInsert<SimpleTableRecord> batchInsert = insert(records)  
            .into(simpleTable)  
            .map(id).toProperty("id")  
            .map(firstName).toProperty("firstName")  
            .map(lastName).toProperty("lastName")  
            .map(birthDate).toProperty("birthDate")  
            .map(employed).toProperty("employed")  
            .map(occupation).toProperty("occupation")  
            .build()  
            .render(RenderingStrategy.MYBATIS3);  
   
    batchInsert.insertStatements().stream().forEach(mapper::insert);  
   
    session.commit();  
} finally {  
    session.close();  
}  

即基本思想是将 MyBatis session 的 executor type 设为 Batch ，然后多次执行插入语句。就类似于JDBC的下面语句一样。

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");  
connection.setAutoCommit(false);  
PreparedStatement ps = connection.prepareStatement(  
        "insert into tb_user (name) values(?)");  
for (int i = 0; i < stuNum; i++) {  
    ps.setString(1,name);  
    ps.addBatch();  
}  
ps.executeBatch();  
connection.commit();  
connection.close();  

总结

如果MyBatis需要进行批量插入，推荐使用 ExecutorType.BATCH 的插入方式，如果非要使用 的插入的话，需要将每次插入的记录控制在 20~50 左右