mybatis的批量更新操作

我們知道mybatis的插入和刪除是可以支持批量操作的，但是update也是支持的，代碼如下：

<update id="updateAll" parameterType="java.util.List">
    <foreach collection="list" item="it" index="index" open="" close="" separator=";">
      update SYSTEM_EXPERT_LIBRARY
      SET USER_CODE = #{it.userCode,jdbcType=VARCHAR},
      EXPERT_NAME = #{it.expertName,jdbcType=VARCHAR},
      EXPERT_SEX = #{it.expertSex,jdbcType=CHAR},
      EXPERT_MAIL = #{it.expertMail,jdbcType=VARCHAR},
      EXPERT_ADDRESS = #{it.expertAddress,jdbcType=VARCHAR},
      EXPERT_CARD = #{it.expertCard,jdbcType=VARCHAR},
      EXPERT_PHONE = #{it.expertPhone,jdbcType=VARCHAR},
      EXPERT_XILIE = #{it.expertXilie,jdbcType=VARCHAR},
      SPECIALTY = #{it.specialty,jdbcType=VARCHAR},
      STATUS = #{it.status,jdbcType=VARCHAR},
      UPDATER = #{it.updater,jdbcType=VARCHAR}
      where ID = #{it.id,jdbcType=INTEGER}
    </foreach>
  </update>

注意批量更新的時候，如果使用了druid數據源，則配置的時候要注意：不能配置wall攔截器，否則批量更新不成功。

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" 
init-method="init" destroy-method="close">
<  property name="url" value="jdbc:mysql://10.3.3.133:6789/test?allowMultiQueries=true&
useUnicode=true&characterEncoding=utf-8"/>
<property name="username" value="test"/>
  <property name="password" value="test"/>        
<!-- 配置監控統計攔截的filters -->
<!-- <property name="filters" value="stat,log4j,wall"/> 
這種配置不支持批量更新語句-->
</bean>

還有重要的一點是要默認開啟支持批量修改操作

Url拼接?allowMultiQueries=true

jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://:3306/xx?allowMultiQueries=true
jdbc.username=
jdbc.password=

mybatis的批量新增刪除

<delete id="deleteBatch">
  delete from t_acl where id in
  <foreach collection="list" index="index" item="item" 
		separator="," open="(" close=")">
    #{item.id}
  </foreach</delete>

<insert id="insertAll" parameterType="java.util.List" 
	useGeneratedKeys="false">  
  insert into SYSTEM_EXPERT_LIBRARY  
  <foreach collection="list" item="it" index="index" separator=",">
  (  )  
	</foreach>
</insert>