MyBatis初级实战之四：druid多数据源_小言_互联网的博客

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

2020-09-11 11:49 337人阅读评论(0)

欢迎访问我的GitHub

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

关于druid多数据源

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

多数据源配置的基本思路

首先要明确的是：数据源是通过配置类实现的，因此要去掉springboot中和数据源相关的自动装配；
最核心的问题有两个，第一个是确定表和数据源的关系，这个关系是在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();
    }

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

实战概览

本次实战的内容如下：

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

源码下载

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

名称	链接	备注
项目主页	https://github.com/zq2599/blog_demos	该项目在GitHub上的主页
git仓库地址(https)	https://github.com/zq2599/blog_demos.git	该项目源码的仓库地址，https协议
git仓库地址(ssh)	git@github.com:zq2599/blog_demos.git	该项目源码的仓库地址，ssh协议

这个git项目中有多个文件夹，本章的应用在mybatis文件夹下，如下图红框所示：

创建数据库和表

创建名为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;

创建名为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;

编码

前文《MyBatis初级实战之一：Spring Boot集成》创建了父工程mybatis，本文继续在此工程中新增子工程，名为druidtwosource，先提前看整个子工程文件结构，如下图，要注意的是红框1中的mapper接口，以及红框2中的mapper映射文件，这两处都按照数据库的不同放入各自文件夹：
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>
        <!-- swagger-ui -->
        <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>

配置文件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

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>

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>

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方法
}

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方法
}

启动类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);
    }

}

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();
    }
}

数据源配置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();
    }
}

第一个数据源的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);
    }
}

第二个数据源的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);
    }

}

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);
}

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);

}

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);
    }

}

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);
    }

}

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);
    }
}

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);
    }
}

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

单元测试

新增配置文件application-test.yml，其内容仅有下图红框位置与application.yml不同，其他的全部一致：
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());
    }
}

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());
    }
}