MyBatis 批量插入几千条数据，请慎用foreach
点击上方“码农突围”，马上关注
这里是码农充电第一站，回复“666”，获取一份专属大礼包 真爱，请设置“星标”或点个“在看
<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
来源：blog.csdn.net/huanghanqian/article/details/83177178
-End-
最近有一些小伙伴，让我帮忙找一些 面试题 资料，于是我翻遍了收藏的 5T 资料后，汇总整理出来，可以说是程序员面试必备！所有资料都整理到网盘了，欢迎下载！
点击👆卡片，关注后回复【 面试题】即可获取
评论