数据库连接串增加参数 &allowMultiQueries=true 支持多条SQL执行
MySQL批量插入格式
insert into table (字段一,字段二,字段三) values(xx,xx,xx),(oo,oo,oo)
<!-- 批量插入数据 -->
<insert id="insertBatch" parameterType="java.util.List">
insert into table_A
(id, name,create_time,update_time)
values
<foreach collection="list" item="bean" index="index"
separator=",">
(
#{bean.id},#{bean.name},#{bean.createTime},#{bean.updateTime}
)
</foreach>
</insert>
<!--批量更新 -->
<update id="updateListByHouseId" parameterType="java.util.ArrayList">
<foreach close=";" collection="list" index="index" item="item" open="" separator=";">
update rba_house_status
<trim prefix="set" suffixOverrides=",">
last_push_time = now(),
<if test="item.pushStatus != null">push_status = #{item.pushStatus},</if>
<if test="item.createStatus != null">create_status = #{item.createStatus},</if>
<if test="item.houseCreateTime != null">house_create_time = #{item.houseCreateTime},</if>
<if test="item.updateStatus != null">update_status = #{item.updateStatus},</if>
update_time = now(),
<if test="item.auditStatus != null">audit_status = #{item.auditStatus},</if>
<if test="item.auditDesc != null">audit_desc = #{item.auditDesc},</if>
<if test="item.hotelId != null">hotel_id = #{item.hotelId},</if>
</trim>
<where>house_id = #{item.houseId}</where>
</foreach>
</update>
使用case when 方式实现批量更新
<!--批量多字段更新-->
<update id="updateListByHouseId" parameterType="java.util.ArrayList">
update rba_house_status
<trim prefix="set" suffixOverrides=",">
<trim prefix="push_status =(case" suffix="end),">
<foreach collection="list" item="item">
<if test="item.pushStatus != null">
when house_id = #{item.houseId} then #{item.pushStatus}
</if>
</foreach>
</trim>
<trim prefix="create_status =(case" suffix="end),">
<foreach collection="list" item="item">
<if test="item.createStatus != null">
when house_id = #{item.houseId} then #{item.createStatus}
</if>
</foreach>
</trim>
<trim prefix="house_create_time =(case" suffix="end),">
<foreach collection="list" item="item">
<if test="item.houseCreateTime != null">
when house_id = #{item.houseId} then #{item.houseCreateTime}
</if>
</foreach>
</trim>
update_time = now(),
</trim>
<where>
house_id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item.houseId}
</foreach>
</where>
</update>