Mybatis3 一条sql批量操作多条记录
- 案例1
查询多个id的记录的所有信息
概述
查询id为10001,10002,10003,10004,10005的记录的所有信息
前端传参
Json:{“IDlist”:”[10001,10002,10003,10004,10005]”}
后端处理
将json中IDlist对象转成jsonArray
List list = jsonObject.getJSONArray("IDlist");
遍历list结果
10001
10002
10003
10004
10005
编写mapper.xml (查询equi表)
<select id="selectPostIn" parameterType="java.util.List" resultType="com.alibaba.fastjson.JSONObject">
SELECT *
FROM equi w
WHERE w.id in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
- 案例2
修改多个id记录的某个字段的状态(每条记录修改的状态都相同)
概述:
修改id为10001,10002,10003,10004,10005的记录的status状态值为2
前端传参
Json:{“IDlist”:”[10001,10002,10003,10004,10005]”}
后端处理
List list = jsonObject.getJSONArray("IDlist");
遍历list结果
10001
10002
10003
10004
10005
编写mapper.xml (查询equi表)
<update id="batchUpdate" parameterType="java.util.List">
update equi w
SET
w.equi_status = '2'
where w.id in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</update>
- 案例3
修改多个id记录的某个字段的值(每条记录修改的值都不同)
概述
修改id为10001,10002,10003,10004,10005的记录的num值(原num减去本次修改的值)
前端传参
{"list":"[{id:10002,num:3},{id:10003,num:4},{id:10005,num:1}]"}
后端处理
List list = jsonObject.getJSONArray("IDlist");
遍历list结果
{"num":3,"id”:10002}
{"num":4,"id”:10003}
{"num":1,"id”:10005}
编写mapper.xml (查询equi表)
<update id="batchUpdate2" parameterType="java.util.List">
update equi w
set w.equi_num =
<foreach collection="list" item="item" index="index"
separator=" " open="case ID" close="end">
when #{item.id} then w.equi_num - #{item.num}
</foreach>
where w.id in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.id}
</foreach>
</update>