<!-- 批量更新第一种方法,通过接收传进来的参数list进行循环着组装sql 后两种写法效率以此降低耗时长 --> <update id="updateBatchStock" > update oms_good_stock set bindcode= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.bindcode} </foreach>, innum= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.innum} </foreach>, sunit= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.sunit} </foreach>, pname= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.pname} </foreach>, pspec= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.pspec} </foreach>, pstyle= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.pstyle} </foreach>, proddate= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.proddate} </foreach>, ratio= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.ratio} </foreach>, goname= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.goname} </foreach>, whname= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.whname} </foreach>, expidate= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.expidate} </foreach>, create_by= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.createBy} </foreach>, create_time= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.createTime} </foreach>, update_by= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.updateBy} </foreach>, update_time= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.updateTime} </foreach>, is_push_wms= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.isPushWms} </foreach>, gocode= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.gocode} </foreach>, is_delete= <foreach collection="list" index="index" separator=" " open="case id" close="end" item="item"> when #{item.id} then #{item.isDelete} </foreach> where id in <foreach collection="list" index="index" separator="," open="(" close=")" item="item"> #{item.id} </foreach> </update> <!-- 批量更新第二种方法,通过接收传进来的参数list进行循环着组装sql 后两种写法效率以此降低耗时长 --> <update id="updateBatch" parameterType="java.util.List" > <foreach collection="list" index="index" separator=" " item="item"> update oms_good_stock <set > <if test="item.bindcode != null and item.bindcode != ''"> bindcode = #{item.bindcode},</if> <if test="item.innum != null ">innum = #{item.innum},</if> <if test="item.sunit != null and item.sunit != ''">item.sunit = #{item.sunit},</if> <if test="item.pname != null and item.pname != ''">item.pname = #{item.pname},</if> <if test="item.pspec != null and item.pspec != ''">item.pspec = #{item.pspec},</if> <if test="item.pstyle != null and item.pstyle != ''">item.pstyle = #{item.pstyle},</if> <if test="item.ratio != null and item.ratio != ''">item.ratio = #{item.ratio},</if> <if test="item.prodDate != null and item.prodDate != ''">item.prod_date = #{item.prodDate},</if> <if test="item.goname != null and item.goname != ''">item.goname = #{item.goname},</if> <if test="item.whname != null and item.whname != ''">item.whname = #{item.whname},</if> <if test="item.expiDate != null and item.expiDate != '' ">item.expi_date = #{item.expiDate},</if> <if test="item.createBy != null and item.createBy != ''">item.create_by = #{item.createBy},</if> <if test="item.createTime != null ">item.create_time = #{item.createTime},</if> <if test="item.updateBy != null and item.updateBy != ''">item.update_by = #{item.updateBy},</if> <if test="item.updateTime != null ">item.update_time = #{item.updateTime},</if> <if test="item.isPushWms != null ">item.isPushWms = #{item.isPushWms},</if> <if test="item.isDelete != null and item.isDelete != ''">item.is_delete = #{item.isDelete},</if> </set> where id = #{item.id} </foreach> </update> <!-- 批量更新第三种方法,通过 case when语句变相的进行批量更新 --> <update id="updateBatch1" parameterType="java.util.List" > update standard_relation <trim prefix="set" suffixOverrides=","> <trim prefix="standard_from_uuid =case" suffix="end,"> <foreach collection="list" item="i" index="index"> <if test="i.standardFromUuid!=null"> when id=#{i.id} then #{i.standardFromUuid} </if> </foreach> </trim> <trim prefix="standard_to_uuid =case" suffix="end,"> <foreach collection="list" item="i" index="index"> <if test="i.standardToUuid!=null"> when id=#{i.id} then #{i.standardToUuid} </if> </foreach> </trim> <trim prefix="gmt_modified =case" suffix="end,"> <foreach collection="list" item="i" index="index"> <if test="i.gmtModified!=null"> when id=#{i.id} then #{i.gmtModified} </if> </foreach> </trim> </trim> where <foreach collection="list" separator="or" item="i" index="index" > id=#{i.id} </foreach> </update> <!--批量更新第四种方法,用ON DUPLICATE KEY UPDATE--> <insert id="updateBatch2" parameterType="java.util.List"> insert into standard_relation(id,relation_type, standard_from_uuid, standard_to_uuid, relation_score, stat, last_process_id, is_deleted, gmt_created, gmt_modified,relation_desc)VALUES <foreach collection="list" item="item" index="index" separator=","> (#{item.id,jdbcType=BIGINT},#{item.relationType,jdbcType=VARCHAR}, #{item.standardFromUuid,jdbcType=VARCHAR}, #{item.standardToUuid,jdbcType=VARCHAR}, #{item.relationScore,jdbcType=DECIMAL}, #{item.stat,jdbcType=TINYINT}, #{item.lastProcessId,jdbcType=BIGINT}, #{item.isDeleted,jdbcType=TINYINT}, #{item.gmtCreated,jdbcType=TIMESTAMP}, #{item.gmtModified,jdbcType=TIMESTAMP},#{item.relationDesc,jdbcType=VARCHAR}) </foreach> ON DUPLICATE KEY UPDATE id=VALUES(id),relation_type = VALUES(relation_type),standard_from_uuid = VALUES(standard_from_uuid),standard_to_uuid = VALUES(standard_to_uuid), relation_score = VALUES(relation_score),stat = VALUES(stat),last_process_id = VALUES(last_process_id), is_deleted = VALUES(is_deleted),gmt_created = VALUES(gmt_created), gmt_modified = VALUES(gmt_modified),relation_desc = VALUES(relation_desc) </insert>