mybatisplus 一次性执行多条SQL语句插入（Mysql篇）

野性酷女 2022-09-06 01:28 404阅读 0赞

### 文章目录 ###

*   *   *   *   *  一、数据库部分
                 *   *  1. 创建数据库
                     *  2. 初始化表结构
                 *  二、代码部分
                 *   *  2.1. controller
                     *  2.2. mapper接口
                     *  2.3. 映射文件
                     *  2.4. 参数封装
                 *  三、测试验证
                 *   *  3.1. 发起请求
                     *  3.2. 查看数据库
                     *  3.3. 配置文件部分

##### 一、数据库部分 #####

###### 1. 创建数据库 ######

创建more-insert

###### 2. 初始化表结构 ######

-- 一次性插入多张表测试
    --mysql 表结构
    CREATE TABLE `sys_user2`
    (
        `user_id` bigint NOT NULL COMMENT '主键id',
        `account` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '账号',
        `name`    varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '名字',
        PRIMARY KEY (`user_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='管理员表2';
    CREATE TABLE `sys_user5`
    (
        `user_id` bigint NOT NULL COMMENT '主键id',
        `account` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '账号',
        `name`    varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '名字',
        PRIMARY KEY (`user_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='管理员表5';

##### 二、代码部分 #####

> 声明：这里为了演示省略service层

###### 2.1. controller ######

package com.gblfy.modular.order.controller;
    
    import com.gblfy.modular.order.mapper.MysqlMoreInsertMapper;
    import com.gblfy.modular.order.model.request.MysqlMoreInsertParam;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    public class MoreController { 
    
        @Autowired
        private MysqlMoreInsertMapper mysqlmoreInsert;
        @GetMapping("/mysqlMoreInsert")
        public String mysqlMoreInsert() { 
            MysqlMoreInsertParam moreInsertParam = new MysqlMoreInsertParam();
            moreInsertParam.setUserId2(2L);
            moreInsertParam.setAccount2("Account2");
            moreInsertParam.setName2("name2");
            moreInsertParam.setUserId5(5L);
            moreInsertParam.setAccount5("Account5");
            moreInsertParam.setName5("name5");
            mysqlmoreInsert.mysqlmoreInsert(moreInsertParam);
            return "SUCCESS";
        }
    }

###### 2.2. mapper接口 ######

package com.gblfy.modular.order.mapper;
    
    import com.gblfy.modular.order.model.request.MysqlMoreInsertParam;
    import org.apache.ibatis.annotations.Param;
    
    /**
     * <p>
     * 英雄信息表通用 Mapper 接口
     * </p>
     *
     * @author gblfy
     * @since 2021-08-13
     */
    public interface MysqlMoreInsertMapper { 
        void mysqlmoreInsert(@Param("paramCondition") MysqlMoreInsertParam paramCondition);
    
    }

###### 2.3. 映射文件 ######

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.gblfy.modular.order.mapper.MysqlMoreInsertMapper">
    
        <insert id="mysqlmoreInsert" parameterType="com.gblfy.modular.order.model.request.MysqlMoreInsertParam">
            insert into sys_user2(user_id, account, name)
            values (#{paramCondition.userId2}, #{paramCondition.account2}, #{paramCondition.name2});
    
            insert into sys_user5(user_id, account, name)
            values (#{paramCondition.userId5}, #{paramCondition.account5}, #{paramCondition.name5});
        </insert>
    </mapper>

###### 2.4. 参数封装 ######

package com.gblfy.modular.order.model.request;
    
    import lombok.Data;
    
    import java.io.Serializable;
    
    /**
     * <p>
     * Mysql通用对象
     * </p>
     *
     * @author gblfy
     * @since 2021-08-13
     */
    @Data
    public class MysqlMoreInsertParam implements Serializable { 
    
        private static final long serialVersionUID = 1L;
        private Long userId2;
        private String account2;
        private String name2;
    
        private Long userId5;
        private String account5;
        private String name5;
    }

##### 三、测试验证 #####

###### 3.1. 发起请求 ######

[http://localhost/mysqlMoreInsert][http_localhost_mysqlMoreInsert]

###### 3.2. 查看数据库 ######

![在这里插入图片描述][d2883d3a357547c99666cf81c0947397.png]  
![在这里插入图片描述][bb8184214faf4ed698319eb985cfb81c.png]

###### 3.3. 配置文件部分 ######

########################################## 多数据源配置 ############################################
    spring:
      datasource:
        dynamic:
          primary: master #设置默认的数据源或者数据源组,默认值即为master
          strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
          datasource:
            master:
              url: jdbc:mysql://localhost:3306/more-insert?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT&nullCatalogMeansCurrent=true&allowMultiQueries=true
              username: root
              password: 123456
              driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
              filters: wall,mergeStat
            oracle_1:
              driver-class-name: oracle.jdbc.OracleDriver
              url: jdbc:oracle:thin:@192.168.xxx.xxx:1521:orcl
              username: orcl
              password: orcl
              filters: wall,mergeStat

[http_localhost_mysqlMoreInsert]: http://localhost/mysqlMoreInsert
[d2883d3a357547c99666cf81c0947397.png]: /images/20220829/e3eaa644d3294b3087f1bb0d8ae344ab.png
[bb8184214faf4ed698319eb985cfb81c.png]: /images/20220829/08ed975217ea47019c6302e467b215d6.png