接口:
//批量新增
int batchInsertGoods(List<Goods> list);
//批量修改
int batchUpdateCarsByIds(List<Map<String, Object>> list);
//根据ids批量获取Goods列表
List<Map<String, Object>> getGoodsList(List<String> ids);
//同一对象根据多个字段批量删除
void batchDelete(List<Goods> goods);
Mybatis:
<!-- 批量新增-->
<insert id="batchInsertGoods" parameterType="java.util.List">
INSERT INTO t_goods(i_id, goods_name)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.id,jdbcType=VARCHAR},#{item.goodsName,jdbcType=VARCHAR})
</foreach>
</insert>
<!-- 批量修改方式-->
<update id="batchupdateCarsByIds" parameterType="java.util.List">
update t_car
<trim prefix="set" suffixOverrides=",">
<trim prefix="car_number = case" suffix="else car_number end,">
<foreach collection="list" item="car" index="index">
<if test="car.carNumber != null and car.carNumber != ''">
when i_id = #{car.id} then #{car.carNumber}
</if>
</foreach>
</trim>
<trim prefix="car_name = case" suffix="else car_name end,">
<foreach collection="list" item="car" index="index">
<if test="car.carName != null">
when i_id = #{car.id} then #{car.carName}
</if>
</foreach>
</trim>
</trim>
where i_id in
<foreach collection="list" item="car" index="index" separator="," open="(" close=")">
#{car.id}
</foreach>
</update>
<!-- 根据ids批量获取Goods列表-->
<select id="getGoodsList" parameterType="java.util.List" resultType="java.util.Map" >
SELECT i_id, goods_name FROM t_goods WHERE i_id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</select>
<!-- 根据多个字段批量删除-->
<delete id="batchDelete" parameterType="java.util.List">
DELETE
FROM t_goods
WHERE (good_code, good_name) in
<foreach collection="list" open="(" close=")" item="item" separator=",">
(#{item.goodCode}, #{item.goodName})
</foreach>
</delete>
mysql输出:
-- 批量新增
INSERT INTO goods(id, goods_name)
VALUES
(?, ?),
(?, ?);
-- 批量修改,不为空则修改,为空则不修改(goods.goods_name,goods.iorder代表不修改)
UPDATE goods set goods_name = case when id = ? then ? when id = ? then goods.goods_name end,
iorder = case when id = ? then goods.iorder when id = ? then ? end
WHERE id in ( ? , ? );