MyBatis批量操作整合

最新推荐文章于 2024-06-27 18:05:01 发布

夕阳无限好_

最新推荐文章于 2024-06-27 18:05:01 发布

阅读量369

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/caixianji/article/details/51480057

版权

mybatis 专栏收录该内容

0 篇文章 0 订阅

订阅专栏

MyBatis批量操作包括：批量新增、批量更新以及批量删除。

1.批量新增：一条insert语句插入多条记录

INSERT INTO t_mobile_ownership (id, province, city, ownership, segment, area_no, card_type, postcode, operator)
VALUES (1,...),(2,...)

xml代码示例：

<!-- 批量新增手机号归属地信息 -->
    <insert id="insertBatch" parameterType="java.util.List">
        INSERT INTO
        	t_mobile_ownership
        (id, province, city, ownership, segment, area_no, card_type, postcode, operator)
        VALUES
        <foreach collection="mobileList" item="item" separator=",">
            (#{item.id},#{item.province},#{item.city},#{item.ownership},#{item.segment},
            #{item.areaNo},#{item.cardType},#{item.postcode},#{item.operator}
            )
        </foreach>
    </insert>

2.批量更新：一条update语句更新多条记录

Mysql并未提供方法实现批量更新，此处可以使用case when语句来实现批量更新操作。

xml代码示例：

<!-- 批量更新手机号归属地信息 -->
    <update id="updateBatch" parameterType="java.util.List">
    	UPDATE t_mobile_ownership
        <trim prefix="set" suffixOverrides=",">
        	<!-- 所属省 -->
            <trim prefix="province =case" suffix="end,">
                <foreach collection="mobileList" item="item">
                    <if test="item.province != null">
                        when id = #{item.id} then #{item.province}
                    </if>
                </foreach>
            </trim>
            <!-- 区号 -->
            <trim prefix="area_no =case" suffix="end,">
                <foreach collection="mobileList" item="item">
                    <if test="item.areaNo != null">
                        when id = #{item.id} then #{item.areaNo}
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE
        	id IN
        <foreach collection="mobileList" item="item" separator="," open="(" close=")">
           #{item.id}
        </foreach>
    </update>

以上代码实际执行sql如下:

UPDATE
    t_mobile_ownership
SET province = CASE id
    WHEN 1 THEN '江苏'
    WHEN 2 THEN '浙江'
END,
area_no = CASE id 
    WHEN 1 THEN '025'
    WHEN 2 THEN '0570'
END
WHERE
    id IN (1,2)

3.批量删除：一条delete语句删除多条记录

DELETE FROM t_mobile_ownership WHERE id in (1,2)

xml代码示例：

<!-- 批量删除手机号归属地信息 -->
    <delete id="deleteBatch" parameterType="java.util.List">
    	DELETE FROM
    		t_mobile_ownership
    	WHERE
    		id IN
   		<foreach collection="mobileList" item="item" separator="," open="(" close=")">
   			#{item.id}
   		</foreach>
    </delete>