批量 insert 示例

1
2
3
4
5
6
7
8
9

<insert id="batchSave" keyColumn="id" keyProperty="id" parameterType="com.aidijing.User" useGeneratedKeys="true">
    INSERT INTO `user`
        (`username`,`password`)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.username},#{item.password})
    </foreach>
</insert>

批量 update 示例

  • 使用for循环,一条记录update一次,这样虽然可以一次性到数据库中执行,但是性能还不是最好的.在数据库中执行,容易造成阻塞.
1
2
3
4
5
6
7
8
9
<update id="updateBatch"  parameterType="java.util.List">  
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>      
</update>
  • 这样批量更新,性能更佳
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    <update id="updateBatchById">
        update useracct
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="phone = case" suffix="end,">
                <foreach collection="list" item="model" index="index">
                    <if test="model.phone!=null">
                        when id = #{model.id} then #{model.phone}
                    </if>
                </foreach>
            </trim>
            <trim prefix="openId = case" suffix="end,">
                <foreach collection="list" item="model" index="index">
                    <if test="model.openId != null">
                        when id = #{model.id} then #{model.openId}
                    </if>
                </foreach>
            </trim>
            <trim prefix="createTime = case" suffix="end,">
                <foreach collection="list" item="model" index="index">
                    <if test="model.createTime != null">
                        when id = #{model.id} then #{model.createTime}
                    </if>
                </foreach>
            </trim>
            <trim prefix="updateTime = case" suffix="end,">
                <foreach collection="list" item="model" index="index">
                    <if test="model.updateTime != null">
                        when id = #{model.id} then #{model.updateTime}
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE
        <foreach collection="list" item="model" separator=" OR ">
            id = #{model.id}
        </foreach>
    </update>