新项目用到了Mybatis,好久没用了,有些比较生疏。今天需要批量更新部分数据
最开始用的是:
<update id="updateApprovalsStatus" parameterType="java.util.List">
update pay_bill
set bill_status=
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.billStatus}
</foreach>
set approval_date=
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then #{item.approvalDate}
</foreach>
where id in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.id,jdbcType=INTEGER}
</foreach>
</update>
但是发送的SQL报错
后来在网上找到了多次update的方式,但是要配置在数据库连接配置上allowMultiQueries=true,如:jdbc:mysql://10.10.20.36:3306/test?allowMultiQueries=true,但是这样的效率并不高。
于是换了种,并且效率也可以
代码:
<update id="updateApprovalsStatus" parameterType="java.util.List">
update pay_bill
<trim prefix="set" suffixOverrides=",">
<trim prefix="bill_status =case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.billStatus}
</foreach>
</trim>
<trim prefix="approval_date =case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id = #{item.id} then #{item.approvalDate}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.id,jdbcType=INTEGER}
</foreach>
</update>
问题解决!小计一下