数据库使用的是MySQL，JDK版本1.8，运行在SpringBoot环境下
本文章源代码：https://github.com/runbeyondmove/mybatis-batch-demo
对比3种可用的方式
1、反复执行单条插入语句
2、xml拼接sql
3、批处理执行
先说结论：少量插入请使用反复插入单条数据，方便。数量较多请使用批处理方式。（可以考虑以有需求的插入数据量20条左右为界吧，在我的测试和数据库环境下耗时都是百毫秒级的，方便最重要）。无论何时都不用xml拼接sql的方式。
1. xml映射文件中的代码
<insert id="insert" parameterType="top.spanrun.bootssm.model.UserInf" useGeneratedKeys="true" keyProperty="id">
<!--
@mbggenerated generator自动生成，注意order的before和after
-->
<!--<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>-->
insert into user_inf (id, uname, passwd, gentle, email, city)
values (#{id,jdbcType=INTEGER}, #{uname,jdbcType=VARCHAR}, #{passwd,jdbcType=VARCHAR},
#{gentle,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}
)
</insert>
<insert id="insertWithXML" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
insert into user_inf (id, uname, passwd, gentle, email, city)
values
<foreach collection="list" item="user" index="index" separator=",">
(#{user.id,jdbcType=INTEGER}, #{user.uname,jdbcType=VARCHAR}, #{user.passwd,jdbcType=VARCHAR},
#{user.gentle,jdbcType=VARCHAR}, #{user.email,jdbcType=VARCHAR}, #{user.city,jdbcType=VARCHAR})
</foreach>
</insert>2. Mapper接口
@Mapper
public interface UserInfMapper {
int insert(UserInf record);
int insertWithXML(@Param("list") List<UserInf> list);
}3. Service实现，接口声明省略
tip:如果使用的是mybatis-plus时testInsertWithBatch()里面可以直接使用save Batch(List<> list),以及saveBatch(list, batchSize);
@Service
public class UserInfServiceImpl implements UserInfService{
private static final Logger LOGGER = LoggerFactory.getLogger(UserInfServiceImpl.class);
@Autowired
SqlSessionFactory sqlSessionFactory;
@Autowired
UserInfMapper userInfMapper;
@Transactional
@Override
public boolean testInsertWithBatch(List<UserInf> list) {
LOGGER.info(">>>>>>>>>>>testInsertWithBatch start<<<<<<<<<<<<<<");
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
UserInfMapper mapper = sqlSession.getMapper(UserInfMapper.class);
long startTime = System.nanoTime();
try {
List<UserInf> userInfs = Lists.newArrayList();
for (int i = 0; i < list.size(); i++) {
// 每1000条提交一次 if ((i+1)%1000 == 0){
sqlSession.commit();
sqlSession.clearCache();
}
mapper.insert(list.get(i));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
LOGGER.info("testInsertWithBatch spend time:{}",System.nanoTime()-startTime);
LOGGER.info(">>>>>>>>>>>testInsertWithBatch end<<<<<<<<<<<<<<");
return true;
}
@Transactional
@Override
public boolean testInsertWithXml(List<UserInf> list) {
LOGGER.info(">>>>>>>>>>>testInsertWithXml start<<<<<<<<<<<<<<");
long startTime = System.nanoTime();
userInfMapper.insertWithXML(list);
LOGGER.info("testInsertWithXml spend time:{}",System.nanoTime()-startTime);
LOGGER.info(">>>>>>>>>>>testInsertWithXml end<<<<<<<<<<<<<<");
return true;
}
@Transactional
@Override
public boolean testInsertWithForeach(List<UserInf> list) {
LOGGER.info(">>>>>>>>>>>testInsertWithForeach start<<<<<<<<<<<<<<");
long startTime = System.nanoTime();
for (int i = 0; i < list.size(); i++) {
userInfMapper.insert(list.get(i));
}
LOGGER.info("testInsertWithForeach spend time:{}",System.nanoTime()-startTime);
LOGGER.info(">>>>>>>>>>>testInsertWithForeach end<<<<<<<<<<<<<<");
return true;
}
@Transactional
@Override
public boolean testInsert(UserInf userInf) {
LOGGER.info(">>>>>>>>>>>testInsert start<<<<<<<<<<<<<<");
long startTime = System.nanoTime();
LOGGER.info("insert before,id=" + userInf.getId());
userInfMapper.insert(userInf);
LOGGER.info("insert after,id=" + userInf.getId());
LOGGER.info("testInsert spend time:{}",System.nanoTime()-startTime);
LOGGER.info(">>>>>>>>>>>testInsert end<<<<<<<<<<<<<<");
return true;
}
}4. Controller控制器
@RestController
public class UserInfController {
@Autowired
UserInfService userInfService;
@RequestMapping(value = "test/{size}/{type}")
public void testInsert(@PathVariable(value = "size") Integer size,@PathVariable(value = "type") Integer type){
System.out.println(">>>>>>>>>>>>type = " + type + "<<<<<<<<<<<<<");
switch (type){
case 1:
userInfService.testInsertWithForeach(generateList(size));
break;
case 2:
userInfService.testInsertWithXml(generateList(size));
break;
case 3:
userInfService.testInsertWithBatch(generateList(size));
break;
default:
UserInf userInf = new UserInf();
userInf.setUname("user_single");
userInf.setGentle("1");
userInf.setEmail("123@123.com");
userInf.setCity("广州市");
userInf.setPasswd("123456");
userInfService.testInsert(userInf);
}
}
private List<UserInf> generateList(int listSize){
List<UserInf> list = Lists.newArrayList();
UserInf userInf = null;
for (int i = 0; i < listSize; i++) {
userInf = new UserInf();
userInf.setUname("user_" + i);
userInf.setGentle("1");
userInf.setEmail("123@123.com");
userInf.setCity("广州市");
userInf.setPasswd("123456");
list.add(userInf);
}
return list;
}
}测试结果（单位是纳秒）：
1000
testInsertWithForeach spend time:431526521
testInsertWithXml spend time:118772867
testInsertWithBatch spend time:175602346
10000
testInsertWithForeach spend time:2072525050
testInsertWithXml spend time:685605121
testInsertWithBatch spend time:894647254
100000
testInsertWithForeach spend time:18950160161
testInsertWithBatch spend time:8469312537
testInsertWithXml报错
### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.] with root cause
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.