MyBatis批量更新插入数据

前言

今天中午突然想到MyBatis框架，然后想到我在更新、插入、修改操作的时候他返回的值到底是多少？

一时间想不起来，在网山找了一些文章，发现他们说的互相冲突。

于是晚上下班，开始验证。

搭建环境

新建一个springboot项目

引入相关依赖

  <!-- SpringBoot集成mybatis框架 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>

 <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

<!--阿里数据库连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.4</version>
        </dependency>

<dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

server:
  port: 8080
spring:
  datasource:
    #驱动名
    driverClassName: com.mysql.cj.jdbc.Driver
    #数据源类型此处是德鲁伊数据源，还有三种数据源
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      username: root
      password: xn123456
      url: jdbc:mysql://localhost:3309/db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#配置mybatis
mybatis:
#mybatis配置文件所在的位置 注意 ：classpath后面不要加 / 
  config-location: classpath:mybatis/mybatis-config.xml
  #别名，在这里配置后，就不需要在mapper。xml里面的参数类型就不用写全路径了
  typeAliasesPackage: com.example.demo.domain
  #mapper.xml文件所在的位置
  mapper-locations: classpath:mybatis/*Mapper.xml

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled"             value="true" />  <!-- 全局映射器启用缓存 -->
        <setting name="useGeneratedKeys"         value="true" />  <!-- 允许 JDBC 支持自动生成主键 -->
        <setting name="defaultExecutorType"      value="REUSE" /> <!-- 配置默认的执行器 -->
        <setting name="logImpl"                  value="SLF4J" /> <!-- 指定 MyBatis 所用日志的具体实现 -->
        <!-- <setting name="mapUnderscoreToCamelCase" value="true"/>  驼峰式命名 -->
    </settings>
</configuration>

在主启动类上加MapperScan

// 指定要扫描的Mapper类的包的路径(对于Mybatis两点重要：其一是MapperScan，其二是Mapper.xml位置的配置)
@MapperScan("com.dean.test_mybatis.**.mapper")

批量插入sql

insert into user(user_id,user_name)values
<trim suffixOverrides=",">
    <foreach collection="list" item="item" index="index" separator=",">
         <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="item.userId != null">#{item.userId}, </if>
            <if test="item.userName != null">#{item.userName}, </if>
         </trim>
    </foreach>
</trim>

当需要插入的数据较多时可以分批插入

测试代码

User user = new User();
        user.setUserId(1996);
        user.setUserName("111");
        users.add(user);
        User user1 = new User(1, "2222");
        users.add(user1);
        int i = testService.testSave(users);
        return i + "";

2022-04-28 00:28:30.868 ERROR 11808 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DuplicateKeyException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '1' for key 'PRIMARY'

结论

假如传入10条，有一条错误则抛出异常，且数据全部插入不成功

第二种方式

首先在jdbc链接里设置允许单次执行多条SQL

allowMultiQueries=true

<foreach collection="list" item="item" index="index" separator=";">
    insert into user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="item.userName != null">user_name,</if>
        <if test="item.userId != null">user_id,</if>
    </trim>
    <trim prefix="values(" suffix=")" suffixOverrides=",">
        <if test="item.userName != null">#{item.userName},</if>
        <if test="item.userId != null">#{item.userId},</if>
    </trim>
</foreach>

结论 这是多条sql insert 如果全部执行成功返回 1 如果有失败的抛出异常 并且前面插入成功的不会回滚掉 不建议使用 会被项目经理砍的

批量更新

<update id="testUpdate" parameterType="java.util.List">
       update user
           <trim prefix="set" suffixOverrides=",">
               <trim prefix=" user_name = case" suffix="end,">
                   <foreach collection="list" index="index" item="item" >
                       <if test="item.userName != null">
                           when user_id = #{item.userId} then #{item.userName}
                       </if>
                   </foreach>
               </trim>
           </trim>
        where user_id in
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <foreach collection="list" item="item" index="index">
                #{item.userId},
            </foreach>
        </trim>
    </update>

<update id="batchUpdateProductSyncStatus" parameterType="java.util.List">
        update hf_product
        <trim prefix="set" suffixOverrides=",">
            <trim prefix=" sync_state =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test='item.value !=null and item.value !=""' >
                        when serial_no = #{item.value} then #{item.syncState}
                    </if>
                </foreach>
            </trim>
            <trim prefix=" sync_time =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test='item.value !=null and item.value !=""' >
                        when serial_no = #{item.value} then #{item.syncTime}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="item" index="index" >
            serial_no=#{item.value}
        </foreach>
    </update>

结论

1. 当update成功时返回match的条数
2. 有一条失败时直接报错，且所有update不生效