SQL
<update id="updateDeptList">
<foreach collection="list" item="item" separator=";">
update dept
<set>
<if test="item.name !=null and item.name != '' ">
name = #{item.name},
</if>
<if test="item.parentId !=null ">
parent_id = #{item.parentId},
</if>
<if test="item.abbr !=null and item.abbr != '' ">
abbr = #{item.abbr},
</if>
<if test="item.branchId !=null ">
branch_id = #{item.branchId} ,
</if>
<if test="item.deptLevel !=null ">
dept_level = #{item.deptLevel},
</if>
<if test="item.deleteDate !=null ">
delete_date = #{item.deleteDate},
</if>
<if test="item.isused !=null ">
isused = #{item.isused},
</if>
<if test="item.paraentCode !=null and item.paraentCode !='' ">
paraent_code= #{item.paraentCode},
</if>
<if test="item.companyCode !=null and item.companyCode !='' ">
company_code = #{item.companyCode},
</if>
<if test="item.companyName !=null and item.companyName !='' ">
company_name = #{item.companyName},
</if>
<if test="item.level !=null and item.level !='' ">
level= #{item.level},
</if>
<if test="item.ihrId !=null ">
ihr_id = #{item.ihrId},
</if>
<if test="item.groupName !=null and item.groupName !='' ">
group_name = #{item.groupName},
</if>
<if test="item.abilityName !=null and item.abilityName !='' ">
ability_name = #{item.abilityName},
</if>
<if test="item.branchNo !=null and item.branchNo !='' ">
branch_no = #{item.branchNo},
</if>
update_date = now()
</set>
where id=#{item.id}
</foreach>
</update>
原因:mybatis连接mysql执行批量修改时,由于mybatis不支持执行多条SQL,故报错sql syntax error, no terminated. SEMI (SQL未正确结束)。需在SQL连接加入**&allowMultiQueries=true**方可正确执行。
mybatis 批量修改的三种方式。
- 方式一
<update id="updateBatch" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update tableName
<set>
name=${item.name},
name2=${item.name2}
</set>
where id = ${item.id}
</foreach>
</update>
但Mybatis映射文件中的sql语句默认是不支持以" ; " 结尾的,也就是不支持多条sql语句的执行。所以需要在连接mysql的url上加 &allowMultiQueries=true 这个才可以执行。
- 方式二
<update id="updateBatch" parameterType="java.util.List">
update tableName
<trim prefix="set" suffixOverrides=",">
<trim prefix="c_name =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.name!=null">
when id=#{cus.id} then #{cus.name}
</if>
</foreach>
</trim>
<trim prefix="c_age =case" suffix="end,">
<foreach collection="list" item="cus">
<if test="cus.age!=null">
when id=#{cus.id} then #{cus.age}
</if>
</foreach>
</trim>
</trim>
<where>
<foreach collection="list" separator="or" item="cus">
id = #{cus.id}
</foreach>
</where>
</update>
这种方式似乎效率不高,但是可以实现,而且不用改动mysql连接
效率参考文章:https://blog.csdn.net/xu19166…
- 方式三
public int updateBatch(List<Object> list){
if(list ==null || list.size() <= 0){
return -1;
}
SqlSessionFactory sqlSessionFactory = SpringContextUtil.getBean("sqlSessionFactory");
SqlSession sqlSession = null;
try {
sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
Mapper mapper = sqlSession.getMapper(Mapper.class);
int batchCount = 1000;//提交数量,到达这个数量就提交
for (int index = 0; index < list.size(); index++) {
Object obj = list.get(index);
mapper.updateInfo(obj);
if(index != 0 && index%batchCount == 0){
sqlSession.commit();
}
}
sqlSession.commit();
return 0;
}catch (Exception e){
sqlSession.rollback();
return -2;
}finally {
if(sqlSession != null){
sqlSession.close();
}
}
}
其中 SpringContextUtil 是自己定义的工具类 用来获取spring加载的bean对象,getBean() 获得的是想要得到的sqlSessionFactory。Mapper 是自己的更具业务需求的Mapper接口类,Object是对象。
总结
方式一 需要修改mysql的连接url,让全局支持多sql执行,不太安全
方式二 当数据量大的时候 ,效率明显降低
方式三 需要自己控制,自己处理,一些隐藏的问题无法发现。
SpringContextUtil.java
@Component
public class SpringContextUtil implements ApplicationContextAware{
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringContextUtil.applicationContext = applicationContext;
}
public static ApplicationContext getApplicationContext(){
return applicationContext;
}
public static Object getBean(Class T){
try {
return applicationContext.getBean(T);
}catch (BeansException e){
return null;
}
}
public static Object getBean(String name){
try {
return applicationContext.getBean(name);
}catch (BeansException e){
return null;
}
}
}