MyBatis批量插入幾千條數(shù)據(jù)，請慎用foreach
往期熱門文章：
1、有了 for (;;) ，為什么還需要while (true) ？到底哪個更快？
2、名企公開掛“加班真好”標語，員工稱一年被免費“白嫖”600多小時！網(wǎng)友看不下去了，稽查部門展開調(diào)查...
<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);
????List?records?=?getRecordsToInsert();?//?not?shown
?
????BatchInsert?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();
}
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?????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多小時！網(wǎng)友看不下去了，稽查部門展開調(diào)查... 3、面試官：為什么 Java 不把基本類型放在堆中？我竟然答不上來。。 4、IDEA 注釋模板這樣搞！ 5、后端開掛：3行代碼寫出8個接口！ 6、推薦一款可視化配置 Nginx 的神器 7、一款性能調(diào)優(yōu)利器 — 火焰圖 8、Redis 實現(xiàn)限流的三種方式 9、推薦 15 款常用開發(fā)工具 10、一次 QPS 翻倍的 Java 服務(wù)性能優(yōu)化 關(guān)注公眾號，你想要的Java都在這里
評論
圖片
表情