已有包含16500条数据的List,无法一次性提交到MySQL,因为已超过MySQL的max_allowed_packet
故考虑每1000条提交一次,最先想到的是16500 / 1000 = 16,然后定义int start、int end
根据start、end不断获取该List的subList,然后再逐个提交subList。
但是总觉得这种方式不够好,前辈们如果遇到这种情况会想要怎么解决呢?麻烦指点下
使用
mybaits
的foreach
标签
这是基于ibatis batch insert的代码,从项目中抠出来的,希望能对你有所帮助
protected <E> boolean executeBatch(List<E> elist, String sqlId) throws SQLException {
boolean isAutoCommit = true;
SqlMapClient sqlMapClient = this.getSqlMapClient();
try {
sqlMapClient.startTransaction();
sqlMapClient.startBatch();
isAutoCommit = sqlMapClient.getCurrentConnection().getAutoCommit();
sqlMapClient.getCurrentConnection().setAutoCommit(false);
int flag = 0;
for (int i = 0, size = elist.size(); i < size; ++i) {
sqlMapClient.insert(sqlId, elist.get(i));
if (++flag == 200) {
flag = 0;
sqlMapClient.executeBatch();
}
}
sqlMapClient.executeBatch();
sqlMapClient.commitTransaction();
} catch (SQLException e) {
sqlMapClient.getCurrentConnection().rollback();// 事务回滚
throw new SQLException(e.getCause());
} finally {
sqlMapClient.getCurrentConnection().setAutoCommit(isAutoCommit);// 恢复原来的状态
sqlMapClient.endTransaction();
}
return true;
}