Mybatis批量操作

foreach标签

• foreach元素的属性主要有 item，index，collection，open，separator，close
• item表示集合中每一个元素进行迭代时的别名
• index指定一个名字，用于表示在迭代过程中，每次迭代到的位置
• open表示该语句以什么开始
• separator表示在每次进行迭代之间以什么符号作为分隔 符
• close表示以什么结束

在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：

• 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了,collection的属性值为map中对应的key值
• 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
• 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

批量插入

DAO层方法:

/**
 * 
 * @Title: batchSaveDemo
 * @Description: 批量保存
 * @param parameterList
 * @throws
void batchSaveDemoList(List<Map<String, Object>> parameterList) throws

Mapper.xml中的sql：

<!-- 批量保存 -->
<insert id="batchSaveDemoList" parameterType="list">
    INSERT INTO DEMO
    VALUES
    <foreach collection="list" item="demo" index="index" separator=",">
        (
        #{demo.ID},
        #{demo.USER_NAME},
        #{demo.AGE},
        #{demo.BIRTH_DAY},
        #{demo.SEX}
        )
    </foreach>
</insert>

执行的sql:

INSERT INTO DEMO VALUES

批量更新

第一种写法

DAO层方法:

/**
 * 
 * @Title: batchUpdateDemoList
 * @Description: 批量保存
 * @param parameterList
 * @throws
void batchUpdateDemoList(List<Map<String, Object>> parameterList) throws

Mapper.xml中的sql：

<!-- 批量更新 -->
<update id="batchUpdateDemoList" parameterType="list">
    UPDATE DEMO
    SET USER_NAME = 
    <foreach collection="list" item="demo" index="index" open="CASE ID" close="END">
        WHEN #{demo.ID} THEN #{demo.USER_NAME}
    </foreach>
    WHERE ID IN
    <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
        #{demo.ID}
    </foreach>
</update>

执行的sql:

UPDATE DEMO 
SET USER_NAME = 
    CASE ID 
        WHEN ? THEN ? 
        WHEN ? THEN ? 
        WHEN ? THEN ? 
        WHEN ? THEN ? 
    END 
WHERE 
    ID IN

第二种写法

trim标签

• prefix：前缀覆盖并增加其内容
• suffix：后缀覆盖并增加其内容
• prefixOverrides：前缀判断的条件
• suffixOverrides：后缀判断的条件
• prefix,suffix 表示在trim标签包裹的部分的前面或者后面添加内容
• 如果同时有prefixOverrides,suffixOverrides 表示会用prefix,suffix覆盖Overrides中的内容
• 如果只有prefixOverrides,suffixOverrides 表示删除开头的或结尾的xxxOverides指定的内容

xml中的sql

<!-- 批量更新 -->
<update id="batchUpdateDemoList" parameterType="list">
    UPDATE DEMO
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="USER_NAME = CASE" suffix="END,">
            <foreach collection="list" item="demo" index="index">
                WHEN ID = #{demo.ID} THEN #{demo.USER_NAME}
            </foreach>
        </trim>
    </trim>
    WHERE ID IN
    <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
        #{demo.ID}
    </foreach>
</update>

生成的sql:

UPDATE DEMO 
SET USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
    END 
WHERE 
    ID IN

同时修改多个字段

<!-- 批量更新 -->
<update id="batchUpdateDemoList" parameterType="list">
    UPDATE DEMO
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="USER_NAME = CASE" suffix="END,">
            <foreach collection="list" item="demo" index="index">
                WHEN ID = #{demo.ID} THEN #{demo.USER_NAME}
            </foreach>
        </trim>
        <trim prefix="AGE = CASE" suffix="END,">
            <foreach collection="list" item="demo" index="index">
                WHEN ID = #{demo.ID} THEN #{demo.AGE}
            </foreach>
        </trim>
    </trim>
    WHERE ID IN
    <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
        #{demo.ID}
    </foreach>
</update>

生成的sql：

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        END 
WHERE 
    ID IN

提供默认值

<!-- 批量更新 -->
    <update id="batchUpdateDemoList" parameterType="list">
        UPDATE DEMO
        <trim prefix="SET" suffixOverrides=",">
            <trim prefix="USER_NAME = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    WHEN ID = #{demo.ID} THEN #{demo.USER_NAME}
                </foreach>
                ELSE '默认值'
            </trim>
            <trim prefix="AGE = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    WHEN ID = #{demo.ID} THEN #{demo.AGE}
                </foreach>
                ELSE 0
            </trim>
        </trim>
        WHERE ID IN
        <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
            #{demo.ID}
        </foreach>
    </update>

生成sql:

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE '默认值' 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE 0 
    END 
WHERE 
    ID IN

对更新的数据进行判断

这样的话只有要更新的list中USER_NAME != null and USER_NAME != ”的数据才能进行USER_NAME 更新.其他的将使用默认值更新,而不会保持原数据不变.

<!-- 批量更新 -->
    <update id="batchUpdateDemoList" parameterType="list">
        UPDATE DEMO
        <trim prefix="SET" suffixOverrides=",">
            <trim prefix="USER_NAME = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    <if test="demo.USER_NAME != null and demo.USER_NAME != ''">
                        WHEN ID = #{demo.ID} THEN #{demo.USER_NAME}
                    </if>
                </foreach>
                ELSE '默认值'
            </trim>
            <trim prefix="AGE = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    WHEN ID = #{demo.ID} THEN #{demo.AGE}
                </foreach>
                ELSE 0
            </trim>
        </trim>
        WHERE ID IN
        <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
            #{demo.ID}
        </foreach>
    </update>

USER_NAME满足条件时生成的sql

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE '默认值' 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE 0 
    END 
WHERE 
    ID IN

USER_NAME不满足条件时生成的sql

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE '默认值' 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE 0 
    END 
WHERE 
    ID IN

只对满足条件的数据进行更新

<!-- 批量更新 -->
    <update id="batchUpdateDemoList" parameterType="list">
        UPDATE DEMO
        <trim prefix="SET" suffixOverrides=",">
            <trim prefix="USER_NAME = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    <if test="demo.USER_NAME != null and demo.USER_NAME != ''">
                        WHEN ID = #{demo.ID} THEN #{demo.USER_NAME}
                    </if>
                    <if test="demo.USER_NAME == null or demo.USER_NAME == ''">
                        WHEN ID = #{demo.ID} THEN DEMO.USER_NAME<!-- 原数据 -->
                    </if>
                </foreach>
                ELSE '默认值'
            </trim>
            <trim prefix="AGE = CASE" suffix="END,">
                <foreach collection="list" item="demo" index="index">
                    WHEN ID = #{demo.ID} THEN #{demo.AGE}
                </foreach>
                ELSE 0
            </trim>
        </trim>
        WHERE ID IN
        <foreach collection="list" item="demo" index="index" separator="," open="(" close=")">
            #{demo.ID}
        </foreach>
    </update>

USER_NAME满足条件时生成的sql

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE '默认值' 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE 0 
    END 
WHERE 
    ID IN

USER_NAME不满足条件时生成的sql

UPDATE DEMO 
SET 
USER_NAME = 
    CASE 
        WHEN ID = ? THEN DEMO.USER_NAME 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE '默认值' 
    END, 
AGE = 
    CASE 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        WHEN ID = ? THEN ? 
        ELSE 0 
    END 
WHERE 
    ID IN

批量删除

第一种写法：数组

/**
     * 
     * @Title: batchDeleteDemoList
     * @Description: 批量删除
     * @param idsArray
     * @throws
    void batchDeleteDemoList(String[] idsArray) throws

parameterType为数组的类型，而不是array

<!-- 批量删除 -->
    <delete id="batchDeleteDemoList" parameterType="string">
        DELETE FROM DEMO
        WHERE ID IN
        <foreach collection="array" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </delete>

生成的sql

DELETE FROM DEMO WHERE ID IN

第二种写法：List

/**
     * 
     * @Title: batchDeleteDemoList
     * @Description: 批量删除
     * @param idsList
     * @throws
    void batchDeleteDemoList(List<String> idsList) throws

<!-- 批量删除 -->
    <delete id="batchDeleteDemoList" parameterType="list">
        DELETE FROM DEMO
        WHERE ID IN
        <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </delete>

生成sql

DELETE FROM DEMO WHERE ID IN

第三种写法：Map

/**
     * 
     * @Title: batchDeleteDemoList
     * @Description: 批量删除
     * @param map
     * @throws
    void batchDeleteDemoList(Map<String, Object> map) throws

map.put(“IDS”, idsArray);
idsArray是一个字符串数组

<!-- 批量删除 -->
    <delete id="batchDeleteDemoList" parameterType="map">
        DELETE FROM DEMO
        WHERE ID IN
        <foreach collection="IDS" item="item" index="index" separator="," open="(" close=")">
            #{item}
        </foreach>
    </delete>

生成sql

DELETE FROM DEMO WHERE ID IN