Implementation of Mybatis Bulk Update in Three Ways
- 2021-06-28 12:42:25
- OfStack
Mybatis Implements Bulk Update Operations
Mode 1:
<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>
However, the sql statement in the Mybatis mapping file is not supported by default with';'At the end, execution of multiple sql statements is not supported.So you need to add an url connection to mysql
&
allowMultiQueries=true This is executable.
Mode 2:
<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>
This may seem inefficient, but it can be done without changing the mysql connection
Efficiency Reference Article: https://www.ofstack.com/article/155835.htm
Mode 3:
Temporarily alter the properties of sqlSessionFactory to implement the batch submitted java, but cannot return the affected quantity.
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;// Submitted Quantity , Submit once this number is reached
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 is the tool class that you define to get the bean object loaded by spring, where getBean () gets the sqlSessionFactory you want.Mapper is its own Mapper interface class with more business needs, and Object is the object.
summary
Attachment: 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;
}
}
}