【MyBatis-4】MyBatis批量insert、update、delete数据

云深i不知处

发布于 2020-09-16 09:50:19

1.4K0

发布于 2020-09-16 09:50:19

文章被收录于专栏：测试基础测试基础

前言

在日常开发中，批量操作数据库数据是常见场景，比如批量插入、批量修改、批量删除数据。MyBatis为批量操作数据提供了非常便利的方案。

当然，也可以在程序中自己拼接sql语句，在dao层传入该sql语句作为参数。要注意的是，采用这种方式时，不能使用 # 而是要使用 $。

批量操作需要使用到比较复杂的标签，所以都在 xml 中实现。

需要注意的是，数据库配置中要将allowMultiQueries置为true，例如：

jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false

1 批量insert

首先，看一下批量插入的xml样板写法：

<insert id="addStudentBatch">
    INSERT INTO mutest.student(id,name)
    VALUES
    <foreach collection="studentList" item="student" separator=",">
        (#{student.id},#{student.name})
    </foreach>
</insert>

上面实现了向 student表中批量插入student信息。

这个很简单，只要调用时，传入学生实体的list就可以了。

dao层：

int addStudentBatch(JSONArray studentList);

studentList的数据样板：

[
	{
		"id": 1,
		"name": "爱因斯坦"
	},
	{
		"id": 2,
		"name": "法拉第"
	},
	{
		"id": 1,
		"name": "莱布尼兹"
	}
]

1.1 根据一个主键批量插入

再看一个稍微复杂一些的场景，向年级表插入班级学生关联信息。表结构如下：

id	class_id	student_id
1	1	1
1	1	2

数据结构如下（JOSNObject student）：

{
	"id": 1,
	"data": [
		{
			"classId": 1,
			"studentId": 1
		},
		{
			"classId": 1,
			"studentId": 2
		},{
			"classId": 1,
			"studentId": 3
		}
	]
}

与之对应的dao层（位于StudentDao.java中）：

int addGradeStudentBatch(Long gradeId, JSONArray studentList);

service层：

Long gradeId = student.getLong("id");
JSONArray sutdnetList = student.getJSONArray("data");
studentDao.addGradeStudentBatch(gradeId,sutdnetList);

xml：

<insert id="addGradeStudentBatch">
    INSERT INTO mutest.grade_student(gradeId,class_id,student_id)
    VALUES
    <foreach collection="studentList" item="student" separator=",">
        (#{gradeId},#{student.classId},#{student.studentId})
    </foreach>
</insert>

1.2 插入已存在数据自动更新

首先，已存在数据的判定由数据库通过唯一的主键或者索引实现的。

以上文向年级表插入班级学生关联信息为例，因为通过三个字段才能断定一条数据是否已存在，所以首先要将为这三个字段添加联合唯一索引：

alter table mutest.grade_student add unique index(id,class_id,student_id);

接下来只要在 xml 中略作修改即可：

<insert id="addGradeStudentBatch">
    INSERT INTO mutest.grade_student(gradeId,class_id,student_id)
    VALUES
    <foreach collection="studentList" item="student" separator=",">
        (#{gradeId},#{student.classId},#{student.studentId})
    </foreach>
    on duplicate key update
    id = values(id),
    class_id = values(class_id),
    student_id = values(student_id)
</insert>

2 批量update

student表是这样子的：

id	name	age
1	zhangsan	17
2	lisi	18
3	wangwu	17

待更新的数据：

{
	"data": [
		{
			"id": 1,
			"name": "zhangsan2",
			"age": 20
		},
		{
			"id": 2,
			"name": "lisi2",
			"age": 21
		}
	]
}

dao层：

void updateStudentBatch(JSONArray studentList);

2.1 MultiQueries

所谓的MultiQueries，就是一次提交多个查询语句。采用这种方式，要设置 allowMultiQueries=true。

首先，我们来看看这种方式下，sql是怎么写的：

UPDATE mutest.student set name='zhangsan2',age=20 WHERE id=1;
UPDATE mutest.student set name='lisi2',age=21 WHERE id=2;

其实很简单，就是逐条更新，但一次提交给MySQL服务器而已。

mybatis xml中的写法如下：

<update id="updateStudentBatch" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index">
        UPDATE mutest.student
        <set>
            <if test="item.name != null">
                name=#{item.name},
            </if>
            <if test="item.age != null">
                age=#{item.age}
            </if>
        </set>
        WHERE
        id=#{item.id};
    </foreach>
</update>

控制台sql语句：

Preparing: UPDATE mutest.student SET name=?, age=? WHERE id=?; UPDATE mutest.student SET name=?, age=? WHERE id=?; 
Parameters: zhangsan2(String), 20(Integer), 1(Integer), lisi2(String), 21(Integer), 2(Integer)
Updates: 1

2.2 case when

这个思路是借用 case when语法拼接语句来实现批量更新。要想实现将待更新的数据更新到库里，sql语句该怎么写呢，如下：

UPDATE mutest.student
    SET name = CASE id 
        WHEN 1 THEN 'zhangsan2'
        WHEN 2 THEN 'lisi2'
    END, 
    age = CASE id 
        WHEN 1 THEN  20
        WHEN 2 THEN  21
    END
WHERE id IN (1,2)

**mybatis xml中的写法如下：**du

<update id="updateStudentBatch" parameterType="java.util.List">
    UPDATE mutest.student
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="name =CASE" suffix="END,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name != null">
                    WHEN id=#{item.id} THEN #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="age =CASE" suffix="END,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age != null">
                    WHEN id=#{item.id} THEN #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.id,jdbcType=BIGINT}
    </foreach>
</update>

控制台sql语句：

Preparing: UPDATE mutest.student SET name =CASE WHEN id=? THEN ? WHEN id=? THEN ? END, age =CASE WHEN id=? THEN ? WHEN id=? THEN ? END WHERE id IN ( ? , ? ) 
Parameters: 1(Integer), zhangsan2(String), 2(Integer), lisi2(String), 1(Integer), 20(Integer), 2(Integer), 21(Integer), 1(Integer), 2(Integer)
Updates: 2

未完待续…

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-06-02 ，如有侵权请联系 cloudcommunity@tencent.com 删除

xml