上節探讨了批量新增資料,這節探讨批量更新資料兩種寫法的效率問題。
實作方式有兩種,
一種用for循環通過循環傳過來的參數集合,循環出N條sql,
另一種 用mysql的case when 條件判斷變相的進行批量更新
下面進行實作。
注意第一種方法要想成功,需要在db連結url後面帶一個參數 &allowMultiQueries=true
即: jdbc:mysql://localhost:3306/mysqlTest?characterEncoding=utf-8&allowMultiQueries=true
其實這種東西寫過來寫過去就是差不多一樣的代碼,不做重複的贅述,直接上代碼。
<!-- 這次用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;
}
測試前準備 首先用上節的 mybatis學習之路----批量更新資料 批量插入,插入10000條資料以備下面的批量更新用。
@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;
}
第一種拼完整sql的方式耗時:
第二種case when 耗時情況:
結果可以看出,其實case when 耗時比較多。
下面來加大資料量到100條;
結果可以看出,其實case when 耗時仍然比第一種多。
繼續加大資料量到1000條
繼續加大資料量到10000條
結果可以看出,兩種方式進行批量更新,效率已經不在一個數量級了。case when明顯的慢的多。
看網上有人說第一種的效率跟用代碼循環着一條一條的循環着插入的效率差不多,通過測試我就有疑問了,他是怎麼做到的。難道我的代碼有問題?明明第一種的效率很高嘛。
第一種效率其實相當高的,因為它僅僅有一個循環體,隻不過最後update語句比較多,量大了就有可能造成sql阻塞。
第二種雖然最後隻會有一條更新語句,但是xml中的循環體有點多,每一個case when 都要循環一遍list集合,是以大批量拼sql的時候會比較慢,是以效率問題嚴重。使用的時候建議分批插入。
根據效率,安全方面綜合考慮,選擇适合的很重要。