MyBatis批量插入几千条数据，请慎用foreach
Java之间
共 4798字，需浏览 10分钟
·
2021-11-28 14:45
往期热门文章：
1、有了 for (;;) ，为什么还需要while (true) ？到底哪个更快？
2、名企公开挂“加班真好”标语，员工称一年被免费“白嫖”600多小时！网友看不下去了，稽查部门展开调查...
<insert id="batchInsert" parameterType="java.util.List">
insert into USER (id, name) values
<foreach collection="list" item="model" index="index" separator=",">
(#{model.id}, #{model.name})
foreach>
insert>
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`)
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");
Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. You shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.
some database such as Oracle here does not support. in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();
Internally, it still generates the same single insert statement with many placeholders as the JDBC code above. MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.And these steps are relatively costly process when the statement string is big and contains many placeholders. [1] simply put, it is a mapping between placeholders and the parameters.
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
Listrecords = getRecordsToInsert(); // not shown
BatchInsertbatchInsert = 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();
}
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();
https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353 https://blog.csdn.net/wlwlwlwl015/article/details/50246717 http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/ https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html
最近热文阅读：
1、有了 for (;;) ，为什么还需要while (true) ？到底哪个更快？ 2、名企公开挂“加班真好”标语，员工称一年被免费“白嫖”600多小时！网友看不下去了，稽查部门展开调查... 3、面试官：为什么 Java 不把基本类型放在堆中？我竟然答不上来。。 4、IDEA 注释模板这样搞！ 5、后端开挂：3行代码写出8个接口！ 6、推荐一款可视化配置 Nginx 的神器 7、一款性能调优利器 — 火焰图 8、Redis 实现限流的三种方式 9、推荐 15 款常用开发工具 10、一次 QPS 翻倍的 Java 服务性能优化 关注公众号，你想要的Java都在这里
评论