有一个工作需求,是对代码做优化,看了代码主要原因是在for循环里操作了数据库。
以前也写过类似的,就直接想到在mapper里用foreach。然后写起来一直报错:
最主要的是这个报错一直报语法错误,实际语法并没错,所以浪费了很多时间。这个问题是mysql在用foreach批量添加修改时,在连接数据库的url连接要拼上&allowMultiQueries=true(oracle不用),拼上后如果服务起不来的话,shutDown.bat次再重启。
批量添加、修改
这个需要循环出多条sql语句,如果集合过大的话,一次性将集合传到mapper,虽然减少了数据库连接次数,但sql过长对数据库不是很友好,所以应该将list拆解,提供个demo
if (insertList != null && insertList.size() > 0) {
if (insertList.size() <= 500) {
aMapper.batchInsertSelective(insertList);
} else {
int pages = (int) Math.ceil(insertList.size() / 500);
for (int p = 0; p <= pages; p++) {
if (CollectionUtils.isNotEmpty(insertList.subList(p * 500, Math.min((p + 1) * 500, insertList.size())))) {
assessmentScoreMapper.batchInsertSelective(insertList.subList(p * 500, Math.min((p + 1) * 500, insertList.size())));
}
}
}
}
每500个向后传一次,降低数据库的连接次数同时数据库压力不会太大。
mysql写修改:
<update id="batchUpdateByPrimaryKeySelective" >
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update stu
<set >
<if test="item.name!= null and item.name!=''" >
`name` = #{item.name,jdbcType=VARCHAR},
</if>
<if test="item.age != null" >
`age` = #{item.age,jdbcType=DECIMAL},
</if>
</set>
where `ID` = #{item.id,jdbcType=DECIMAL}
</foreach>
</update>
oracle写修改:
<update id="batchUpdateByPrimaryKeySelective" >
<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
update stu
<set >
<if test="item.name!= null and item.name!=''" >
`name` = #{item.name,jdbcType=VARCHAR},
</if>
<if test="item.age != null" >
`age` = #{item.age,jdbcType=DECIMAL},
</if>
</set>
where `ID` = #{item.id,jdbcType=DECIMAL}
</foreach>
</update>
mysql下添加:
<insert id="batchInsertSelective" useGeneratedKeys="true">
insert into stu(id,name,age) values
<foreach collection="list" item="item" index="index" open="" close="" separator=",">
(null,
#{item.name,jdbcType=VARCHAR},
#{item.age,jdbcType=DECIMAL})
</foreach>
</insert>
oracle下添加
<insert id="batchInsertSelective" >
insert into stu(id,name,age)
<foreach collection="list" item="item" index="index" open="" close="" separator="UNION ALL">
select
#{item.id,jdbcType=DECIMAL},
#{item.name,jdbcType=VARCHAR},
#{item.age,jdbcType=DECIMAL}
from dual
</foreach>
</insert>
批量查询、删除
这个只需写一条sql,不用考虑数据库压力,但是,in()里面参数超过1000个会有bug,这个解决方法有很多,提供一种方法
public List<Stu> batchSelect(Long[] ids) {
List<Stu> all = new ArrayList<>();
if(ids.length<=1000){
all = aMapper.batchSelect(ids);
}else if(ids.length>1000){
int count = ids.length;
int pageSize = 1000;
for(int i = 0;i<=(count/pageSize);i++){
int beg = i*pageSize;//开始
int end =(i+1)*pageSize; //结束
if(i==count/pageSize){
end= ids.length;
}
System.out.println("第"+(i+1)+"次获取,从"+beg+"获取到"+end);
Long [] newData = Arrays.copyOfRange(ids, beg , end );
List<Stu> some = aMapper.batchSelect(newData);
all.addAll(some);
}
}
return all;
}
xml写法:查找和删除拼接差不多
<select id="batchSelect" resultMap="BaseResultMap" >
select
<include refid="Base_Column_List" />
from stu
where ID in
<foreach collection="ids" index="index" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</select>
最后经过优化,接口从原来的6s变成0.5s。