最近遇上需要批量修改有联合主键的表数据,网上找了很多文章,最终都没找到比较合适的方法,有些只能支持少量数据批量修改,超过十几条就不行了。
最终自己摸索总结了两种方式可以批量修改数据。
第一种(EMPID与ORGID联合主键):
<update id="updateMoreEmpOrg" parameterType="java.util.List">
update hr_emp_org
<trim prefix="set" suffixOverrides=",">
<trim prefix="ISMAN = CASE EMPID" suffix="end,">
<foreach collection="empOrgList" item="item" index="index">
<if test="item.isman != null">
when EMPID = #{item.empid} then #{item.isman}
</if>
</foreach>
</trim>
<trim prefix="UPDATETIME = CASE EMPID" suffix="end,">
<foreach collection="empOrgList" item="item" index="index">
<if test="item.updatetime != null">
when EMPID = #{item.empid} then #{item.updatetime}
</if>
</foreach>
</trim>
<trim prefix="hr_status =case EMPID" suffix="end,">
<foreach collection="empOrgList" item="item" index="index">
<if test="item.hrStatus != null">
when #{item.EMPID} then #{item.hrStatus}
</if>
</foreach>
</trim>
</trim>
where
EMPID in
<foreach collection="empOrgList" item="item" open="(" separator="," close=")">
#{item.empid}
</foreach>
and ORGID in
<foreach collection="empOrgList" item="item" open="(" separator="," close=")">
#{item.orgid}
</foreach>
</update>
直接结果集来两个in查询,最终可以满足。
第二种(EMPID与POSITIONID?联合主键):
<update id="updateMoreEmpPosition" parameterType="java.util.List">
update hr_emp_position
<trim prefix="set" suffixOverrides=",">
<trim prefix="ISMAN =case" suffix="end,">
<foreach collection="empPositionList" item="item" index="index">
<if test="item.isman != null">
when EMPID = #{item.empid} and POSITIONID = #{item.positionid} then #{item.isman}
</if>
</foreach>
</trim>
<trim prefix="CREATETIME =case" suffix="end,">
<foreach collection="empPositionList" item="item" index="index">
<if test="item.createtime != null">
when EMPID = #{item.empid} and POSITIONID = #{item.positionid} then #{item.createtime}
</if>
</foreach>
</trim>
<trim prefix="UPDATETIME =case" suffix="end,">
<foreach collection="empPositionList" item="item" index="index">
<if test="item.updatetime != null">
when EMPID = #{item.empid} and POSITIONID = #{item.positionid} then #{item.updatetime}
</if>
</foreach>
</trim>
<trim prefix="hr_status =case" suffix="end,">
<foreach collection="empPositionList" item="item" index="index">
<if test="item.hrStatus != null">
when EMPID = #{item.empid} and POSITIONID = #{item.positionid} then #{item.hrStatus}
</if>
</foreach>
</trim>
</trim>
where
EMPID in
<foreach collection="empPositionList" item="item" open="(" separator="," close=")">
#{item.empid}
</foreach>
</update>
修改条件中trim里面 case后面不填对比字段,在if里面进行对比判断。
|