MyBatis初級實戰之四：druid多資料來源

程式設計師欣宸發表於2021-01-20

MyBatisUI

歡迎訪問我的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)，如下圖所示：

在這裡插入圖片描述
4. 從上述程式碼可見，如果上層的業務程式碼想操作secondDataSource這個資料來源的表，只要把對應的*Mapper.xml檔案和Mapper介面檔案對應的目錄下即可；
5. 整個配置的關鍵步驟如下圖所示：

在這裡插入圖片描述

實戰概覽

本次實戰的內容如下：

一共有兩個資料庫：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不同，其他的全部一致：

在這裡插入圖片描述
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());
    }
}

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