我们知道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">
还有重要的一点是要默认开启支持批量修改操作
Url拼接#allowMultiQueries=true
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://:3306/xx#allowMultiQueries=true
jdbc.username=
jdbc.password=
<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>