Mybatis优雅的批量update

先看hello world

1、本文使用springboot框架测试

2、开始编码

        2.1、创建一个数据库操作实体

import lombok.Data;

@Data
public class TestZiShu {
    private String id;
    private String name;
    private String age;
}

        2.2、测试controller

@RestController
@RequestMapping("testUpdate")
public class TestUpdate {

    @Resource
    TestUpdateService testUpdateService;

    @RequestMapping("update")
    public Integer testUpdate(@RequestBody List<TestZiShu> testZiShuList){
        return testUpdateService.testUpdate(testZiShuList);
    }

}

        2.3、service（结构还是要有的）

@Service
public class TestUpdateService {

    @Resource
    TestYuMapper testYuMapper;

    public Integer testUpdate(List<TestZiShu> testZiShuList){
        return testYuMapper.batchUpdate(testZiShuList);
    }
}

        2.4、mapper

public interface TestYuMapper {
    
    /**
     * 测试批量更新
     * @return int
     */
    int batchUpdate(List<TestZiShu> list);

}

        2.5、mapper.xml--注意入参实体和foreach标签的使用

    <update id="batchUpdate" >
        <foreach collection="list" item="item" separator=";">
            UPDATE testzishu
            SET
                `age` = #{item.age}
            WHERE
                `name` = #{item.name}
        </foreach>
    </update>

        2.6、数据库表

CREATE TABLE `testzishu` (
  `id` varchar(255) NOT NULL,
  `age` varchar(255) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

        2.7、如果你的项目可以启动话，就可以测试了

        入参：

[
    {
        "name": "小黑",
        "age": "22"
    },
    {
        "name": "小红",
        "age": "21"
    }
]

        2.8、执行结果sql

        本条SQL会一次性发送给数据库执行，只需要进行一次网络IO，可以提高效率。

==>  Preparing: UPDATE testzishu SET `age` = ? WHERE `name` = ? ; UPDATE testzishu SET `age` = ? WHERE `name` = ?
==> Parameters: 22(String), 小黑(String), 21(String), 小红(String)
<==    Updates: 1

3、常见问题

       问题1、 默认情况下，数据库是不支持执行这样由‘;’号拼接的长串的，执行的时候会报错，提示说执行的SQL有语法错误，操作结果如下:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE testzishu
            SET
                `age` = '21'
            WHERE
' at line 7
### The error may exist in file [C:\Users\server\target\classes\mapper\TestYuMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE testzishu             SET                 `age` = ?             WHERE                 `name` = ?          ;              UPDATE testzishu             SET                 `age` = ?             WHERE                 `name` = ?
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE testzishu
            SET
                `age` = '21'
            WHERE
' at line 7
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE testzishu
            SET
                `age` = '21'
            WHERE
' at line 7] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE testzishu
            SET
                `age` = '21'
            WHERE
' at line 7

        问题1解决方案：

                修改项目配置文件，数据库配置处url中指定allowMultiQueries参数值为true，目的是告诉数据库以支持‘;’号分隔的多条语句的执行！

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/water?allowMultiQueries=true