<update id="batchUpdate" parameterType="java.util.List"> <foreach collection="list" item="item" separator=";" > update tableA set name=#{item.name}, type=#{item.type} where id =#{item.id} </foreach> </update>
缺点:性能差,在八十多万数据的表中,一个update一次处理200条记录需要五分钟左右,可以优化成以下SQL
<update id="batchUpdate" parameterType="java.util.List"> update tableA <trim prefix="set" suffixOverrides=","> <trim prefix="name =case" suffix="end,"> <foreach collection="list" item="item"> when id = #{item.id} then #{item.name} </foreach> </trim> <trim prefix="type =case" suffix="end,"> <foreach collection="list" item="item"> when id = #{item.id} then #{item.type} </foreach> </trim> </trim> where <foreach collection="list" item="item" separator="," open="id in (" close=")"> #{item.id} </foreach> </update>