一、背景:业务需求,一次提交操作,需要批量按主键一条一条更新,100条数据优化前需要1.6s左右。
二、优化前的实现方式:通过for循环,依次更新每条数据
for(TitleDetail titleDetail: titleDetailListUpdate) {
TitleDetail titleDetailUpdate = new TitleDetail();
titleDetailUpdate.setAnswerResult(titleDetail.getAnswerResult());
titleDetailUpdate.setAnswerScore(titleDetail.getAnswerScore());
titleDetailUpdate.setAnswerBeginDatetime(titleDetail.getAnswerBeginDatetime());
titleDetailUpdate.setAnswerEndDatetime(titleDetail.getAnswerEndDatetime());
titleDetailUpdate.setTakeUpTime(titleDetail.getTakeUpTime());
Example titleDetailExample = new Example(TitleDetail.class);
Criteria titleDetailCriteria = titleDetailExample.createCriteria();
titleDetailCriteria.andEqualTo("titleNo", titleDetail.getTitleNo());
//单条更新操作
titleDetailDao.updateByExampleSelective(titleDetailUpdate, titleDetailExample);
}
三、解决办法:由于update语句不同于insert语句,insert语句可以一条sql插入多条数据,而update在根据主键更新时,一条sql只能操作一条数据。考虑通过 多条sql(以分号分隔)放到一次处理。我这里使用的是spring cloud + mybatis 注解的方式 主要代码如下:
@SuppressWarnings({ "unchecked", "rawtypes" })
public String updateBatch(Map map) {
//需要处理的数据集合
List<TitleDetail> titleDetailList = (List<TitleDetail>) map.get("titleDetailList");
//拼接update语句: update xxx set xx='1' where xx='a'; update xxx set xx='1' where xx='b'; ......
StringBuilder sb = new StringBuilder();
for (int i = 0; i < titleDetailList.size(); i++) {
sb.append("update title_detail set answer_result = #{titleDetailList[");
sb.append(i);
sb.append("].answerResult},");
sb.append(" answer_score = #{titleDetailList[");
sb.append(i);
sb.append("].answerScore},");
sb.append(" answer_begin_datetime = #{titleDetailList[");
sb.append(i);
sb.append("].answerBeginDatetime},");
sb.append(" answer_end_datetime = #{titleDetailList[");
sb.append(i);
sb.append("].answerEndDatetime},");
sb.append(" take_up_time = #{titleDetailList[");
sb.append(i);
sb.append("].takeUpTime} where title_no = #{titleDetailList[");
sb.append(i);
sb.append("].titleNo};");
}
return sb.toString();
}
四、另外:sql通过xml形式,代码如下:
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from music_favorite where id = #{id,jdbcType=INTEGER};
delete from music_favorite_song where f_id = #{id,jdbcType=INTEGER};
</delete>
五、注意:mysql默认是不支持一次执行多条SQL语句的,需要在数据库连接URL上加上 allowMultiQueries=true,