MyBatis初级实战之四：druid多数据源

「爱情、让人受尽委屈。」 2022-11-30 04:10 144阅读 0赞

### 欢迎访问我的GitHub ###

[https://github.com/zq2599/blog\_demos][https_github.com_zq2599_blog_demos]  
内容：所有原创文章分类汇总及配套源码，涉及Java、Docker、Kubernetes、DevOPS等；

### 关于druid多数据源 ###

本文是《MyBatis初级实战》系列的第四篇，一个springboot应用同时操作两个数据库的场景，在平时也会遇到，今天要实战的就是通过druid配置两个数据源，让一个springboot应用同时使用这两个数据源；

### 多数据源配置的基本思路 ###

1.  首先要明确的是：数据源是通过配置类实现的，因此要去掉springboot中和数据源相关的自动装配；
2.  最核心的问题有两个，第一个是确定表和数据源的关系，这个关系是在SqlSessionFactory实例中确立的，代码如下所示：

@Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/second/**/*Mapper.xml"));
            return bean.getObject();
        }

1.  第二个核心问题是包扫描，即指定的mapper接口要使用指定的sqlSessionTemplat，这个关系在SqlSessionTemplate配置类中(相当于旧版的xml配置bean)，如下图所示：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center]
2.  从上述代码可见，如果上层的业务代码想操作secondDataSource这个数据源的表，只要把对应的\*Mapper.xml文件和Mapper接口文件对应的目录下即可；
3.  整个配置的关键步骤如下图所示：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 1]

### 实战概览 ###

本次实战的内容如下：

1.  一共有两个数据库：mybatis和mybatis\_second；
2.  mybatis中有名为user的表，mybatis\_second中有名为address的表；
3.  新建名为druidtwosource的springboot应用，里面有两个controller，可以分别对user、address这两个表进行操作；
4.  编写单元测试用例，通过调用controller接口验证应用功能正常；
5.  启动springboot应用，通过swagger验证功能正常；
6.  进入druid监控页面；

### 源码下载 ###

1.  如果您不想编码，可以在GitHub下载所有源码，地址和链接信息如下表所示(https://github.com/zq2599/blog\_demos)：

<table> 
 <thead> 
  <tr> 
   <th align="left">名称</th> 
   <th align="left">链接</th> 
   <th align="left">备注</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">项目主页</td> 
   <td align="left">https://github.com/zq2599/blog_demos</td> 
   <td align="left">该项目在GitHub上的主页</td> 
  </tr> 
  <tr> 
   <td align="left">git仓库地址(https)</td> 
   <td align="left">https://github.com/zq2599/blog_demos.git</td> 
   <td align="left">该项目源码的仓库地址，https协议</td> 
  </tr> 
  <tr> 
   <td align="left">git仓库地址(ssh)</td> 
   <td align="left">git@github.com:zq2599/blog_demos.git</td> 
   <td align="left">该项目源码的仓库地址，ssh协议</td> 
  </tr> 
 </tbody> 
</table>

1.  这个git项目中有多个文件夹，本章的应用在mybatis文件夹下，如下图红框所示：  
    ![在这里插入图片描述][20200808201256277.png]

### 创建数据库和表 ###

1.  创建名为mybatis的数据库，建表语句如下：

DROP TABLE IF EXISTS `user`;
    
    CREATE TABLE `user` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL,
      `age` int(32) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

1.  创建名为mybatis\_second的数据库，建表语句如下：

DROP TABLE IF EXISTS `address`;
    
    CREATE TABLE `address` (
      `id` int(32) NOT NULL AUTO_INCREMENT,
      `city` varchar(32) NOT NULL,
      `street` varchar(32) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

### 编码 ###

1.  前文[《MyBatis初级实战之一：Spring Boot集成》][MyBatis_Spring Boot]创建了父工程mybatis，本文继续在此工程中新增子工程，名为druidtwosource，先提前看整个子工程文件结构，如下图，要注意的是红框1中的mapper接口，以及红框2中的mapper映射文件，这两处都按照数据库的不同放入各自文件夹：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 2]
2.  druidtwosource工程的pom.xml内容如下：

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>com.bolingcavalry</groupId>
            <artifactId>mybatis</artifactId>
            <version>1.0-SNAPSHOT</version>
            <relativePath>../pom.xml</relativePath>
        </parent>
    
        <groupId>com.bolingcavalry</groupId>
        <artifactId>druidtwosource</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>druidtwosource</name>
        <description>Demo project for Mybatis Druid (two datasource) in Spring Boot</description>
    
        <properties>
            <java.version>1.8</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
                <exclusions>
                    <exclusion>
                        <groupId>org.junit.vintage</groupId>
                        <artifactId>junit-vintage-engine</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
    
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger2</artifactId>
            </dependency>
            
            <dependency>
                <groupId>io.springfox</groupId>
                <artifactId>springfox-swagger-ui</artifactId>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
            </dependency>
    
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>com.google.code.gson</groupId>
                <artifactId>gson</artifactId>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

1.  配置文件application.yml，可见这里面有first和second两个数据源配置，而druid的web-stat-filter和stat-view-servlet这两个配置是公用的：

server:
      port: 8080
    
    spring:
      #1.JDBC数据源
      datasource:
        druid:
          first:
            username: root
            password: 123456
            url: jdbc:mysql://192.168.50.43:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
            driver-class-name: com.mysql.cj.jdbc.Driver
            #初始化连接池的连接数量 大小，最小，最大
            initial-size: 5
            min-idle: 5
            max-active: 20
            #配置获取连接等待超时的时间
            max-wait: 60000
            #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            time-between-eviction-runs-millis: 60000
            # 配置一个连接在池中最小生存的时间，单位是毫秒
            min-evictable-idle-time-millis: 30000
            # 配置一个连接在池中最大生存的时间，单位是毫秒
            max-evictable-idle-time-millis: 300000
            validation-query: SELECT 1 FROM user
            test-while-idle: true
            test-on-borrow: true
            test-on-return: false
            # 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
            filters: stat,wall,slf4j
            filter:
              stat:
                merge-sql: true
                slow-sql-millis: 5000
    
          second:
            username: root
            password: 123456
            url: jdbc:mysql://192.168.50.43:3306/mybatis_second?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
            driver-class-name: com.mysql.cj.jdbc.Driver
            #初始化连接池的连接数量 大小，最小，最大
            initial-size: 5
            min-idle: 5
            max-active: 20
            #配置获取连接等待超时的时间
            max-wait: 60000
            #配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
            time-between-eviction-runs-millis: 60000
            # 配置一个连接在池中最小生存的时间，单位是毫秒
            min-evictable-idle-time-millis: 30000
            # 配置一个连接在池中最大生存的时间，单位是毫秒
            max-evictable-idle-time-millis: 300000
            validation-query: SELECT 1 FROM user
            test-while-idle: true
            test-on-borrow: true
            test-on-return: false
            # 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
            filters: stat,wall,slf4j
            filter:
              stat:
                merge-sql: true###
                slow-sql-millis: 5000
    
          #3.基础监控配置
          web-stat-filter:
            enabled: true
            url-pattern: /*
            #设置不统计哪些URL
            exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
            session-stat-enable: true
            session-stat-max-count: 100
          stat-view-servlet:
            enabled: true
            url-pattern: /druid/*
            reset-enable: true
            #设置监控页面的登录名和密码
            login-username: admin
            login-password: admin
            allow: 127.0.0.1
            #deny: 192.168.1.100
    
    # 日志配置
    logging:
      level:
        root: INFO
        com:
          bolingcavalry:
            druidtwosource:
              mapper: debug

1.  user的映射配置，请注意文件位置：

<?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.bolingcavalry.druidtwosource.mapper.first.UserMapper">
    
        
        <insert id="insertWithFields" useGeneratedKeys="true" keyProperty="id">
            insert into user (id, name, age) values (#{id}, #{name}, #{age})
        </insert>
    
        
        <select id="findByName" parameterType="String" resultType="com.bolingcavalry.druidtwosource.entity.User">
            select id, name, age from user where name like concat('%', #{name}, '%')
        </select>
    
        
        <delete id="delete">
            delete from user where id= #{id}
        </delete>
    
    </mapper>

1.  address的映射配置：

<?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.bolingcavalry.druidtwosource.mapper.second.AddressMapper">
    
        
        <insert id="insertWithFields" useGeneratedKeys="true" keyProperty="id">
            insert into address (id, city, street) values (#{id}, #{city}, #{street})
        </insert>
    
        
        <select id="findByCityName" parameterType="String" resultType="com.bolingcavalry.druidtwosource.entity.Address">
            select id, city, street from address where city like concat('%', #{cityname}, '%')
        </select>
    
        
        <delete id="delete">
            delete from address where id= #{id}
        </delete>
    
    </mapper>

1.  user表的实体类，注意swagger用到的注解：

package com.bolingcavalry.druidtwosource.entity;
    
    import io.swagger.annotations.ApiModel;
    import io.swagger.annotations.ApiModelProperty;
    
    @ApiModel(description = "用户实体类")
    public class User { 
    
        @ApiModelProperty(value = "用户ID")
        private Integer id;
    
        @ApiModelProperty(value = "用户名", required = true)
        private String name;
    
        @ApiModelProperty(value = "用户地址", required = false)
        private Integer age;
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    '}';
        }
        ...省略get和set方法
    }

1.  address表的实体类：

package com.bolingcavalry.druidtwosource.entity;
    
    import io.swagger.annotations.ApiModel;
    import io.swagger.annotations.ApiModelProperty;
    
    @ApiModel(description = "地址实体类")
    public class Address { 
    
        @ApiModelProperty(value = "地址ID")
        private Integer id;
    
        @ApiModelProperty(value = "城市名", required = true)
        private String city;
    
        @ApiModelProperty(value = "街道名", required = true)
        private String street;
    
        @Override
        public String toString() { 
            return "Address{" +
                    "id=" + id +
                    ", city='" + city + '\'' +
                    ", street='" + street + '\'' +
                    '}';
        }
        ...省略get和set方法
    }

1.  启动类DuridTwoSourceApplication.java，要注意的是排除掉数据源和事务的自动装配，因为后面会手动编码执行这些配置：

package com.bolingcavalry.druidtwosource;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
    
    @SpringBootApplication(exclude={ 
            DataSourceAutoConfiguration.class,
            DataSourceTransactionManagerAutoConfiguration.class,
    })
    public class DuridTwoSourceApplication { 
    
        public static void main(String[] args) { 
            SpringApplication.run(DuridTwoSourceApplication.class, args);
        }
    
    }

1.  swagger配置：

package com.bolingcavalry.druidtwosource;
    
    import springfox.documentation.service.Contact;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import springfox.documentation.builders.ApiInfoBuilder;
    import springfox.documentation.builders.PathSelectors;
    import springfox.documentation.builders.RequestHandlerSelectors;
    import springfox.documentation.service.ApiInfo;
    import springfox.documentation.service.Tag;
    import springfox.documentation.spi.DocumentationType;
    import springfox.documentation.spring.web.plugins.Docket;
    import springfox.documentation.swagger2.annotations.EnableSwagger2;
    
    /** * @Description: swagger配置类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/11 7:54 */
    @Configuration
    @EnableSwagger2
    public class SwaggerConfig { 
    
        @Bean
        public Docket createRestApi() { 
            return new Docket(DocumentationType.SWAGGER_2)
                    .apiInfo(apiInfo())
                    .tags(new Tag("UserController", "用户服务"),
                            new Tag("AddressController", "地址服务"))
                    .select()
                    // 当前包路径
                    .apis(RequestHandlerSelectors.basePackage("com.bolingcavalry.druidtwosource.controller"))
                    .paths(PathSelectors.any())
                    .build();
        }
    
        //构建 api文档的详细信息函数,注意这里的注解引用的是哪个
        private ApiInfo apiInfo() { 
            return new ApiInfoBuilder()
                    //页面标题
                    .title("MyBatis CURD操作")
                    //创建人
                    .contact(new Contact("程序员欣宸", "https://github.com/zq2599/blog_demos", "zq2599@gmail.com"))
                    //版本号
                    .version("1.0")
                    //描述
                    .description("API 描述")
                    .build();
        }
    }

1.  数据源配置TwoDataSourceConfig.java，可见是通过ConfigurationProperties注解来确定配置信息，另外不要忘记在默认数据源上添加Primary注解：

package com.bolingcavalry.druidtwosource;
    
    import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    
    import javax.sql.DataSource;
    
    /** * @Description: druid配置类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/18 08:12 */
    @Configuration
    public class TwoDataSourceConfig { 
    
        @Primary
        @Bean(name = "firstDataSource")
        @ConfigurationProperties("spring.datasource.druid.first")
        public DataSource first() { 
            return DruidDataSourceBuilder.create().build();
        }
    
        @Bean(name = "secondDataSource")
        @ConfigurationProperties("spring.datasource.druid.second")
        public DataSource second() { 
            return DruidDataSourceBuilder.create().build();
        }
    }

1.  第一个数据源的mybatis配置类DruidConfigFirst.java，可以结合本篇的第一幅图来看，注意MapperScan注解的两个属性basePackages和sqlSessionTemplateRef是关键，它们最终决定了哪些mapper接口使用哪个数据源，另外注意要带上Primary注解：

package com.bolingcavalry.druidtwosource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    /** * @Description: druid配置类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/18 08:12 */
    @Configuration
    @MapperScan(basePackages = "com.bolingcavalry.druidtwosource.mapper.first", sqlSessionTemplateRef  = "firstSqlSessionTemplate")
    public class DruidConfigFirst { 
    
        @Bean(name = "firstSqlSessionFactory")
        @Primary
        public SqlSessionFactory sqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/first/**/*Mapper.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "firstTransactionManager")
        @Primary
        public DataSourceTransactionManager transactionManager(@Qualifier("firstDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "firstSqlSessionTemplate")
        @Primary
        public SqlSessionTemplate sqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    }

1.  第二个数据源的mybatis配置DruidConfigSecond.java，注意不要带Primary注解：

package com.bolingcavalry.druidtwosource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    
    import javax.sql.DataSource;
    
    /** * @Description: druid配置类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/18 08:12 */
    @Configuration
    @MapperScan(basePackages = "com.bolingcavalry.druidtwosource.mapper.second", sqlSessionTemplateRef  = "secondSqlSessionTemplate")
    public class DruidConfigSecond { 
    
        @Bean(name = "secondSqlSessionFactory")
        public SqlSessionFactory sqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception { 
            SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
            bean.setDataSource(dataSource);
            bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/second/**/*Mapper.xml"));
            return bean.getObject();
        }
    
        @Bean(name = "secondTransactionManager")
        public DataSourceTransactionManager transactionManager(@Qualifier("secondDataSource") DataSource dataSource) { 
            return new DataSourceTransactionManager(dataSource);
        }
    
        @Bean(name = "secondSqlSessionTemplate")
        public SqlSessionTemplate sqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { 
            return new SqlSessionTemplate(sqlSessionFactory);
        }
    
    }

1.  user表的mapper接口类很简单，只有三个接口，注意package位置：

package com.bolingcavalry.druidtwosource.mapper.first;
    
    import com.bolingcavalry.druidtwosource.entity.User;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    @Repository
    public interface UserMapper { 
    
        int insertWithFields(User user);
    
        List<User> findByName(String name);
    
        int delete(int id);
    }

1.  address表的Mapper接口类：

package com.bolingcavalry.druidtwosource.mapper.second;
    
    import com.bolingcavalry.druidtwosource.entity.Address;
    import org.springframework.stereotype.Repository;
    
    import java.util.List;
    
    /** * @Description: 地址实体的接口类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/4 8:32 */
    
    @Repository
    public interface AddressMapper { 
    
        int insertWithFields(Address address);
    
        List<Address> findByCityName(String cityName);
    
        int delete(int id);
    
    }

1.  user表的service类：

package com.bolingcavalry.druidtwosource.service;
    
    import com.bolingcavalry.druidtwosource.entity.User;
    import com.bolingcavalry.druidtwosource.mapper.first.UserMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    
    public class UserService { 
        @Autowired
        UserMapper userMapper;
    
        public User insertWithFields(User user) { 
            userMapper.insertWithFields(user);
            return user;
        }
    
        public List<User> findByName(String name) { 
            return userMapper.findByName(name);
        }
    
        public int delete(int id) { 
            return userMapper.delete(id);
        }
    
    }

1.  address表的service类：

package com.bolingcavalry.druidtwosource.service;
    
    import com.bolingcavalry.druidtwosource.entity.Address;
    import com.bolingcavalry.druidtwosource.entity.User;
    import com.bolingcavalry.druidtwosource.mapper.first.UserMapper;
    import com.bolingcavalry.druidtwosource.mapper.second.AddressMapper;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    
    @Service
    public class AddressService { 
    
        @Autowired
        AddressMapper addressMapper;
    
        public Address insertWithFields(Address address) { 
            addressMapper.insertWithFields(address);
            return address;
        }
    
        public List<Address> findByCityName(String cityName) { 
            return addressMapper.findByCityName(cityName);
        }
    
        public int delete(int id) { 
            return addressMapper.delete(id);
        }
    
    }

1.  user表的controller：

package com.bolingcavalry.druidtwosource.controller;
    
    import com.bolingcavalry.druidtwosource.entity.User;
    import com.bolingcavalry.druidtwosource.service.UserService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiImplicitParam;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    import java.util.List;
    
    @RestController
    @RequestMapping("/user")
    @Api(tags = { "UserController"})
    public class UserController { 
    
        @Autowired
        private UserService userService;
    
        @ApiOperation(value = "新增user记录", notes="新增user记录")
        @RequestMapping(value = "/insertwithfields",method = RequestMethod.PUT)
        public User create(@RequestBody User user) { 
            return userService.insertWithFields(user);
        }
    
        @ApiOperation(value = "删除指定ID的user记录", notes="删除指定ID的user记录")
        @ApiImplicitParam(name = "id", value = "用户ID", paramType = "path", required = true, dataType = "Integer")
        @RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
        public int delete(@PathVariable int id){ 
            return userService.delete(id);
        }
    
        @ApiOperation(value = "根据名称模糊查找所有user记录", notes="根据名称模糊查找所有user记录")
        @ApiImplicitParam(name = "name", value = "用户名", paramType = "path", required = true, dataType = "String")
        @RequestMapping(value = "/findbyname/{name}", method = RequestMethod.GET)
        public List<User> findByName(@PathVariable("name") String name){ 
            return userService.findByName(name);
        }
    }

1.  address表的controller：

package com.bolingcavalry.druidtwosource.controller;
    
    import com.bolingcavalry.druidtwosource.entity.Address;
    import com.bolingcavalry.druidtwosource.service.AddressService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiImplicitParam;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.*;
    
    import java.util.List;
    
    /** * @Description: user表操作的web接口 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/4 8:31 */
    @RestController
    @RequestMapping("/address")
    @Api(tags = { "AddressController"})
    public class AddressController { 
    
        @Autowired
        private AddressService addressService;
    
    
        @ApiOperation(value = "新增address记录", notes="新增address记录")
        @RequestMapping(value = "/insertwithfields",method = RequestMethod.PUT)
        public Address create(@RequestBody Address address) { 
            return addressService.insertWithFields(address);
        }
    
        @ApiOperation(value = "删除指定ID的address记录", notes="删除指定ID的address记录")
        @ApiImplicitParam(name = "id", value = "地址ID", paramType = "path", required = true, dataType = "Integer")
        @RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
        public int delete(@PathVariable int id){ 
            return addressService.delete(id);
        }
    
        @ApiOperation(value = "根据城市名模糊查找所address记录", notes="根据城市名模糊查找所address记录")
        @ApiImplicitParam(name = "name", value = "城市名", paramType = "path", required = true, dataType = "String")
        @RequestMapping(value = "/findbycityname/{cityname}", method = RequestMethod.GET)
        public List<Address> findByName(@PathVariable("cityname") String cityName){ 
            return addressService.findByCityName(cityName);
        }
    }

*  至此，编码完成，接下来编写单元测试代码；

### 单元测试 ###

1.  新增配置文件application-test.yml，其内容仅有下图红框位置与application.yml不同，其他的全部一致：  
    ![在这里插入图片描述][20200823120433500.png_pic_center]
2.  user表的测试用例如下：

package com.bolingcavalry.druidtwosource.controller;
    
    import com.bolingcavalry.druidtwosource.entity.User;
    import com.google.gson.Gson;
    import com.google.gson.JsonArray;
    import com.google.gson.JsonParser;
    import org.junit.jupiter.api.*;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.http.MediaType;
    import org.springframework.test.context.ActiveProfiles;
    import org.springframework.test.context.junit4.SpringRunner;
    import org.springframework.test.web.servlet.MockMvc;
    import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
    
    import java.util.UUID;
    
    import static org.hamcrest.Matchers.hasSize;
    import static org.hamcrest.Matchers.is;
    import static org.hamcrest.core.IsEqual.equalTo;
    import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;
    import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;
    
    
    /** * @Description: 单元测试类 * @author: willzhao E-mail: zq2599@gmail.com * @date: 2020/8/9 23:55 */
    @RunWith(SpringRunner.class)
    @SpringBootTest
    @AutoConfigureMockMvc
    @TestMethodOrder(MethodOrderer.OrderAnnotation.class)
    @ActiveProfiles("test")
    class UserControllerTest { 
    
        @Autowired
        private MockMvc mvc;
    
        // user表的name字段，这里为了保证测试时新增和删除的记录是同一条，用UUID作为用户名
        static String testName;
    
        @BeforeAll
        static void init() { 
            testName = UUID.randomUUID().toString().replaceAll("-","");
        }
    
        @Test
        @Order(1)
        void insertWithFields() throws Exception { 
            String jsonStr = "{\"name\": \"" + testName + "\", \"age\": 10}";
    
            mvc.perform(
                    MockMvcRequestBuilders.put("/user/insertwithfields")
                            .contentType(MediaType.APPLICATION_JSON)
                            .content(jsonStr)
                            .accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$.name", is(testName)))
                    .andDo(print())
                    .andReturn()
                    .getResponse()
                    .getContentAsString();
        }
    
        @Test
        @Order(2)
        void findByName() throws Exception { 
            mvc.perform(MockMvcRequestBuilders.get("/user/findbyname/"+ testName).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$", hasSize(1)))
                    .andDo(print());
        }
    
    
        @Test
        @Order(3)
        void delete() throws Exception { 
            // 先根据名称查出记录
            String responseString = mvc.perform(MockMvcRequestBuilders.get("/user/findbyname/"+ testName).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$", hasSize(1)))
                    .andDo(print())
                    .andReturn()
                    .getResponse()
                    .getContentAsString();
    
            // 反序列化得到数组
            JsonArray jsonArray = JsonParser.parseString(responseString).getAsJsonArray();
    
            // 反序列化得到user实例
            User user = new Gson().fromJson(jsonArray.get(0), User.class);
    
            // 执行删除
            mvc.perform(MockMvcRequestBuilders.delete("/user/"+ user.getId()).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(content().string(equalTo("1")))
                    .andDo(print());
        }
    }

1.  address表的单元测试如下：

package com.bolingcavalry.druidtwosource.controller;
    
    import com.bolingcavalry.druidtwosource.entity.Address;
    import com.google.gson.Gson;
    import com.google.gson.JsonArray;
    import com.google.gson.JsonParser;
    import org.junit.jupiter.api.*;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.http.MediaType;
    import org.springframework.test.context.ActiveProfiles;
    import org.springframework.test.context.junit4.SpringRunner;
    import org.springframework.test.web.servlet.MockMvc;
    import org.springframework.test.web.servlet.request.MockMvcRequestBuilders;
    
    import java.util.UUID;
    
    import static org.hamcrest.Matchers.hasSize;
    import static org.hamcrest.Matchers.is;
    import static org.hamcrest.core.IsEqual.equalTo;
    import static org.springframework.test.web.servlet.result.MockMvcResultHandlers.print;
    import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.*;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    @AutoConfigureMockMvc
    @TestMethodOrder(MethodOrderer.OrderAnnotation.class)
    @ActiveProfiles("test")
    class AddrestControllerTest { 
    
        @Autowired
        private MockMvc mvc;
    
        // address表的cityName字段，这里为了保证测试时新增和删除的记录是同一条，用UUID作为用户名
        static String testCityName;
    
        @BeforeAll
        static void init() { 
            testCityName = UUID.randomUUID().toString().replaceAll("-","");
        }
    
        @Test
        @Order(1)
        void insertWithFields() throws Exception { 
            String jsonStr = "{\"city\": \"" + testCityName + "\", \"street\": \"streetName\"}";
    
            mvc.perform(
                    MockMvcRequestBuilders.put("/address/insertwithfields")
                            .contentType(MediaType.APPLICATION_JSON)
                            .content(jsonStr)
                            .accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$.city", is(testCityName)))
                    .andDo(print())
                    .andReturn()
                    .getResponse()
                    .getContentAsString();
        }
    
        @Test
        @Order(2)
        void findByName() throws Exception { 
            mvc.perform(MockMvcRequestBuilders.get("/address/findbycityname/"+ testCityName).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$", hasSize(1)))
                    .andDo(print());
        }
    
    
        @Test
        @Order(3)
        void delete() throws Exception { 
            // 先根据名称查出记录
            String responseString = mvc.perform(MockMvcRequestBuilders.get("/address/findbycityname/"+ testCityName).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$", hasSize(1)))
                    .andDo(print())
                    .andReturn()
                    .getResponse()
                    .getContentAsString();
    
            // 反序列化得到数组
            JsonArray jsonArray = JsonParser.parseString(responseString).getAsJsonArray();
    
            // 反序列化得到user实例
            Address address = new Gson().fromJson(jsonArray.get(0), Address.class);
    
            // 执行删除
            mvc.perform(MockMvcRequestBuilders.delete("/address/"+ address.getId()).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(content().string(equalTo("1")))
                    .andDo(print());
        }
    }

*  至此，编码完成，而可以开始验证了；

### 验证，单元测试 ###

1.  user表对应的单元测试操作如下图，三个测试方法先后新增记录，查询记录，然后删除掉：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 3]
2.  AddrestControllerTest也按照上图做同样的操作；

### 验证，swagger ###

1.  浏览器访问：http://localhost:8080/swagger-ui.html ，会展示swagger页面如下：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 4]
2.  先来试试新增操作：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 5]
3.  返回数据如下图：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 6]
4.  以下是用MySQL数据库客户端工具查看到的mybatis.user表的数据，可见服务功能正常：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 7]
5.  其他接口请自行操作验证；

### 进入druid监控页面 ###

1.  druid监控页面地址是：http://localhost:8080/druid ， 账号密码都是admin：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 8]
2.  登录后可见数据库操作：  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 9]
3.  在数据源页面可以见到两个数据源，如下图：  
    ![在这里插入图片描述][20200823171253616.png_pic_center]  
    ![在这里插入图片描述][20200823171317669.png_pic_center]

*  以上就是完整的springboot+mybatis+druid多数据源开发和验证过程，希望能给您一些参考；

### 欢迎关注我的公众号：程序员欣宸 ###

![在这里插入图片描述][format_png]

[https_github.com_zq2599_blog_demos]: https://github.com/zq2599/blog_demos
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/0d89d6c3ad944f70aa6ff76b483d9097.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221124/797cf9d7f4804b929561527aa5915eb5.png
[20200808201256277.png]: /images/20221124/2db211c3950243e694e055a8db9ecdf0.png
[MyBatis_Spring Boot]: https://blog.csdn.net/boling_cavalry/article/details/107805840
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221124/667c19946c72435380a7f62757a9b70c.png
[20200823120433500.png_pic_center]: /images/20221124/1b928b48b9344b7598803b9edc468a1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221124/a8cb9bfb6c014d9ea97d347dc3f7c34b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221124/96bedf2d0f9f4defb87a609b6ff13e95.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221124/c0b0291472844bd5bfe7b26f02e98f9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20221124/99990a475c7d4e328fb48b11c7d12a13.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20221124/400ca80c69bb442fa21227ea6df45acf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20221124/93f114a6319f4180a8bdcfaf859d9d44.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvbGluZ19jYXZhbHJ5_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20221124/b5341219a81b4bebaf3bd0e7c113fc90.png
[20200823171253616.png_pic_center]: /images/20221124/f218cd5e367a460698dc781c64089c9b.png
[20200823171317669.png_pic_center]: /images/20221124/40a615727a2b43b89a1e0ec4fef74a5a.png
[format_png]: /images/20221124/275731d6749c4a0fab0a9affca3225af.png