[精] MySQL和Oracle下Mybatis批量操作示例和获取影响行数介绍

default-executor-type:
simple默认: 
    SimpleExecutor, 单个 sqlsession 内, 每次操作都开启一个 Statement 对象用完立刻关闭 Statement 对象
batch: 
    BatchExecutor, 单个 sqlsession 内,每次操作复用已有 Statement 对象, addBatch()汇总然后统一执行executeBatch()
    因此 Mybatis 官方写明它无法返回行数(BATCH executor is in use, the update counts are being lost.)
reuse: 
    ReuseExecutor, 应用实例内, 全局共享 Statement对象(Map<String, Statement>), 存在则复用

表结构定义

CREATE TABLE `student`
(
    `id`   int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `age`  int(11)      DEFAULT NULL,
    PRIMARY KEY (`id`)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

Mybatis批量新增

一般批量insert有4种写法
1.循环插入每次插入一条用的不多这里不介绍

注意如果是一次提交多个insert/update语句MySQL的连接串需要加上
allowMultiQueries=true表示开启批处理可以执行批量SQL。就是多个分号;的SQL

2.BEGIN-END写法这个是单次多条insert语句用的不多这里不介绍
3.foreach写法这个是单次单条insert语句常用
4.JDBC原生写法这个效率最高不属于mybatis写法这里不介绍

Mapper接口定义

int insertListBatch(@Param("studentList") List<Student> studentList);

批量新增——Mysql写法

单次单条insert语句,foreach-batch写法

    <insert id="insertListBatch">
        insert into student(
        id,
        name,
        age
        ) values
        <foreach collection="studentList" item="item" index="index" separator=",">
            (
            #{item.id,jdbcType=INTEGER},
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=INTEGER}
            )
        </foreach>
    </insert>

获取返回影响行数只需要用下面这种执行器模式直接获取返回值该值就是影响行数。Oracle也是一样。

default-executor-type: simple

        int i = studentService.insertListBatch(studentList);
        logger.info("Mybatis SQL return :" + i);

批量新增——Oracle写法

单次单条insert语句,foreach-batch写法

    <insert id="insertListBatch">
        insert into STUDENT(
        ID,
        NAME,
        AGE
        )
        <foreach collection="studentList" item="item" index="index" separator="union all">
            (
            select
            #{item.id},
            #{item.name,jdbcType=VARCHAR},
            #{item.age,jdbcType=DECIMAL}
            from dual
            )
        </foreach>
    </insert>

Oracle与MySQL批量插入的区别就是Oracle需要用到 union all 和 dual 关键字看下Oracle批量插入的SQL示例就知道了

INSERT INTO TEST.STUDENT (ID,NAME,AGE)
(SELECT 7,'change',18 FROM dual)
UNION ALL
(SELECT 5,'change',18 FROM dual)

上面说到Mybatis批量插入有几种写法那么这些写法有什么区别效率怎么样可以参考这篇文章4亿数据批量操作插入为什么不用Mybatis而是选择原生JDBC文中有各类批量操作效率对比和总结https://zhangxiaofan.blog.csdn.net/article/details/121351546

Mybatis批量删除

Mapper接口定义

int deleteByIdList(@Param("list") List<String> list);

批量删除——MySQL写法、Oracle写法一样

    <delete id="deleteByIdList">
        delete from student
        where id IN
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

返回影响行数跟批量插入一样用simple默认执行器即可直接获取返回值。

Mybatis批量更新

Mapper接口定义

int updateListByIdBatch(@Param("studentList") List<Student> studentList);

批量更新——MySQL写法

    <update id="updateListByIdBatch">
        update student
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="age =case" suffix="end,">
                <foreach collection="studentList" item="item" index="index">
                    <if test="item.age != null">
                        when id=#{item.id} then #{item.age,jdbcType=INTEGER}
                    </if>
                </foreach>
            </trim>
            <trim prefix="name =case" suffix="end">
                <foreach collection="studentList" item="item" index="index">
                    <if test="item.name != null">
                        when id=#{item.id,jdbcType=INTEGER} then #{item.name,jdbcType=VARCHAR}
                    </if>
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="studentList" item="item" index="index" separator="," open="(" close=")">
            #{item.id,jdbcType=INTEGER}
        </foreach>
    </update>

返回影响行数跟批量插入一样用simple默认执行器即可直接获取返回值。

批量更新——Oracle写法


    <update id="updateListByIdBatch">
        update STUDENT
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="AGE =case" suffix="end,">
                <foreach collection="studentList" item="item" index="index">
                    <choose>
                        <when test="item.age != null">
                            when ID=#{item.id} then #{item.age,jdbcType=DECIMAL}
                        </when>
                        <otherwise>
                            <!-- 字段为null, 用原值更新(保留原值), 否则会被重置为null -->
                            when ID=#{item.id,jdbcType=DECIMAL} then age
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
            <trim prefix="NAME =case" suffix="end">
                <foreach collection="studentList" item="item" index="index">
                    <choose>
                        <when test="item.name != null">
                            when ID=#{item.id,jdbcType=DECIMAL} then #{item.name,jdbcType=VARCHAR}
                        </when>
                        <otherwise>
                            <!-- 字段为null, 用原值更新(保留原值), 否则会被重置为null -->
                            when ID=#{item.id,jdbcType=DECIMAL} then name
                        </otherwise>
                    </choose>
                </foreach>
            </trim>
        </trim>
        where ID in
        <foreach collection="studentList" item="item" index="index" separator="," open="(" close=")">
            #{item.id,jdbcType=DECIMAL}
        </foreach>
    </update>

Oracle与MySQL批量更新的写法主要区别在Oracle需要用 choose-when-otherwise 来解决部分输入字段为null的问题。感兴趣的朋友可以参考这篇文章

Oralce Mybaits批量更新的正确写法——解决传入字段为nullhttps://zhangxiaofan.blog.csdn.net/article/details/120179181?spm=1001.2014.3001.5502返回影响行数跟批量插入一样用simple默认执行器即可直接获取返回值。

where如果有多个条件可以写成类似这样的

        where (ID,ID2) in
        <foreach collection="studentList" item="item" index="index" separator="," open="(" close=")">
            (#{item.id,jdbcType=DECIMAL},#{item.id2,jdbcType=DECIMAL})
        </foreach>