[Java] 详解mybatis 批量更新数据两种方法效率对比

<!-- 这次用resultmap接收输出结果 -->
 <select id="findbyname" parametertype="string" resultmap="customermap">
   select * from t_customer where c_name like concat('%', #{name},'%') order by c_cerono limit 0,100
 </select>
 
 
 <!-- 批量更新第一种方法，通过接收传进来的参数list进行循环着组装sql -->
 <update id="batchupdate" parametertype="java.util.map">
   <!-- 接收list参数，循环着组装sql语句，注意for循环的写法
    separator=";" 代表着每次循环完，在sql后面放一个分号
    item="cus" 循环list的每条的结果集
    collection="list" list 即为 map传过来的参数key -->
   <foreach collection="list" separator=";" item="cus">
   update t_customer set
   c_name = #{cus.name},
   c_age = #{cus.age},
   c_sex = #{cus.sex},
   c_cerono = #{cus.cerono},
   c_cerotype = #{cus.cerotype}
   where id = #{cus.id}
   </foreach>
 </update>
 
 <!-- 批量更新第二种方法，通过 case when语句变相的进行批量更新 -->
 <update id="batchupdatecasewhen" parametertype="java.util.map">
   update t_customer
   <trim prefix="set" suffixoverrides=",">
   <!-- 拼接case when 这是一种写法 -->
   <!--<foreach collection="list" separator="" item="cus" open="c_age = case id" close="end, ">-->
   <!--when #{cus.id} then #{cus.age}-->
   <!--</foreach>-->
 
   <!-- 拼接case when 这是另一种写法，这种写着更专业的感觉 -->
   <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 prefix="c_sex =case" suffix="end,">
     <foreach collection="list" item="cus">
     <if test="cus.sex!=null">
       when id=#{cus.id} then #{cus.sex}
     </if>
     </foreach>
   </trim>
   <trim prefix="c_cerono =case" suffix="end,">
     <foreach collection="list" item="cus">
     <if test="cus.cerono!=null">
       when id=#{cus.id} then #{cus.cerono}
     </if>
     </foreach>
   </trim>
   <trim prefix="c_cerotype =case" suffix="end,">
     <foreach collection="list" item="cus">
     <if test="cus.cerotype!=null">
       when id=#{cus.id} then #{cus.cerotype}
     </if>
     </foreach>
   </trim>
   </trim>
   <where>
   <foreach collection="list" separator="or" item="cus">
     id = #{cus.id}
   </foreach>
   </where>
 </update>

list<customer> findbyname(string name);
 
 int batchupdate(map<string,object> param);
 
 int batchupdatecasewhen(map<string,object> param);

/**
  * 用于更新时，获取更新数据
  * @param name
  * @return
  */
 public list<customer> findbyname(string name) {
   sqlsession sqlsession = null;
   try {
   sqlsession = sqlsessionutil.getsqlsession();
   return sqlsession.selectlist("customer.findbyname", name);
   } catch (exception e) {
   e.printstacktrace();
   } finally {
   sqlsessionutil.closesession(sqlsession);
   }
   return new arraylist<customer>();
 }
 
 
 /**
  * 批量更新第一种方式
  * @param param
  * @return
  */
 public int batchupdate(map<string,object> param) {
   return bathupdate("customer.batchupdate",param);
 }
 
 /**
  * 批量更新第二种方式
  * @param param
  * @return
  */
 public int batchupdatecasewhen(map<string,object> param) {
   return bathupdate("customer.batchupdatecasewhen",param);
 }
 
 /**
  * 公共部分提出
  * @param statementid
  * @param param
  * @return
  */
 private int bathupdate(string statementid,map param){
   sqlsession sqlsession = null;
   try {
   sqlsession = sqlsessionutil.getsqlsession();
   int key = sqlsession.update(statementid, param);
   // commit
   sqlsession.commit();
   return key;
   } catch (exception e) {
   sqlsession.rollback();
   e.printstacktrace();
   } finally {
   sqlsessionutil.closesession(sqlsession);
   }
   return 0;
 }

@test
  public void batchinsert() throws exception {
  map<string,object> param = new hashmap<string,object>();
  list<customer> list = new arraylist<customer>();
  for(int i=0;i<10000;i++){
    customer customer = new customer();
    customer.setname("准备数据" + i);
    customer.setage(15);
    customer.setcerono("111111111111"+i);
    customer.setcerotype(2);
    customer.setsex(1);
    list.add(customer);
  }
  param.put("list",list);
  long start = system.currenttimemillis();
  int result = customerdao.batchinsert(param);
  system.out.println("耗时 ： "+(system.currenttimemillis() - start));
  }

<!-- 这次用resultmap接收输出结果 -->
<select id="findbyname" parametertype="string" resultmap="customermap">
  select * from t_customer where c_name like concat('%', #{name},'%') order by c_cerono limit 0,10
</select>

@test
public void batchudpate() throws exception {
  map<string,object> param = new hashmap<string,object>();
 
  param.put("list",getfindbyname("准备数据","批量更新01"));
  long start = system.currenttimemillis();
  customerdao.batchupdate(param);
  system.out.println("耗时 ： "+(system.currenttimemillis() - start));
}
 
@test
public void batchudpatecasewhen() throws exception {
  map<string,object> param = new hashmap<string,object>();
  param.put("list",getfindbyname("批量更新01","准备数据"));
  long start = system.currenttimemillis();
  customerdao.batchupdatecasewhen(param);
  system.out.println("耗时 ： "+(system.currenttimemillis() - start));
}
 
private list<customer> getfindbyname(string name, string change){
  list<customer> list = customerdao.findbyname(name);
  system.out.println("查询出来的条数 ： " + list.size());
  if(null != change && !"".equals(change)){
  for(customer customer : list){
    customer.setname(change);
  }
  }
 
  return list;
}