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

港控/mmm° 2023-10-06 09:14 4阅读 0赞

## 前言 ##

在日常开发中，批量操作数据库数据是常见场景，比如批量插入、批量修改、批量删除数据。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 根据一个主键批量插入 ###

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class_id</th> 
   <th>student_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

数据结构如下（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 中略作修改即可：

## 2 批量update ##

student表是这样子的：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>zhangsan</td> 
   <td>17</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>lisi</td> 
   <td>18</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>wangwu</td> 
   <td>17</td> 
  </tr> 
 </tbody> 
</table>

待更新的数据：

{
        
    	"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

未完待续…