最近在用java写后端接口时,遇到数据的批量更新问题,显而易见,写个for循环进行更新是最方便的,但是影响效率,显然需要更好的解决办法,想起了经常写的批量插入操作,试了一下,发现不能用同样的办法,查了会资料,终于写完了批量更新的sql语句。
先附上较容易的批量插入的代码:
<insert id="addAllLiquidityKey" parameterType="java.util.List"> insert into liquidity_key(responsible_department,item_name,responsible_person,collaborative_department,measure,date) values <foreach collection="list" item="liquidityKey" separator=","> (#{liquidityKey.responsibleDepartment},#{liquidityKey.itemName},#{liquidityKey.responsiblePerson},#{liquidityKey.collaborativeDepartment},#{liquidityKey.measure},#{liquidityKey.date}) </foreach> </insert>
然后是批量更新的sql:
(先附上网上找的一个)
<update id="updateBatch" parameterType="java.util.List">
update mydata_table
<trim prefix="set" suffixOverrides=",">
<trim prefix="status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.status !=null and item.status != -1">
when id=#{item.id} then #{item.status}
</if>
<if test="item.status == null or item.status == -1">
when id=#{item.id} then mydata_table.status//原数据
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
刚看到的时候有点懵,查了会sql语句,发现首先批量更新,需要用到
case when数据库操作语句(忘了的可以百度下)
然后了解一下trim标签的属性,
trim标签使用
1、trim 有四个属性
2、prefix,suffix 表示在trim标签包裹的部分的前面或者后面添加内容(注意:是没有prefixOverrides,suffixOverrides的情况下)
3、如果有prefixOverrides,suffixOverrides 表示**覆盖**Overrides中的内容。
4、如果只有prefixOverrides,suffixOverrides 表示删除。
之后呢,就可以开始写批量更新的语句了,
附上我的代码,由于我的表字段有点多,需要些较多相同的代码,为了简便,写了个方法来引用:
sql id="batch_update"> <trim prefix="${table_name} = case" suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.${listName} != null"> when id = #{item.id} then #{item.${listName}} </if> <if test="item.${listName} == null"> when id = #{item.id} then liquidity_key.${table_name} </if> </foreach> </trim> </sql> <update id="updateAll" parameterType="java.util.List"> update liquidity_key <trim prefix="set" suffixOverrides=","> <include refid="batch_update"> <property name="table_name" value="responsible_department"/> <property name="listName" value="responsibleDepartment"/> </include> <include refid="batch_update"> <property name="table_name" value="item_name"/> <property name="listName" value="itemName"/> </include> <include refid="batch_update"> <property name="table_name" value="responsible_person"/> <property name="listName" value="responsiblePerson"/> </include> <include refid="batch_update"> <property name="table_name" value="collaborative_department"/> <property name="listName" value="collaborativeDepartment"/> </include> <include refid="batch_update"> <property name="table_name" value="measure"/> <property name="listName" value="measure"/> </include> <include refid="batch_update"> <property name="table_name" value="date"/> <property name="listName" value="date"/> </include> </trim> where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id} </foreach> </update>