5

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

 3 years ago
source link: https://segmentfault.com/a/1190000039032150
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

欢迎访问我的GitHub

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)，如下图所示：

RnMB3mv.png!mobile

  1. 从上述代码可见，如果上层的业务代码想操作</font>secondDataSource</font>这个数据源的表，只要把对应的*Mapper.xml文件和Mapper接口文件对应的目录下即可；
  2. 整个配置的关键步骤如下图所示：

Yjia6rR.png!mobile

实战概览

本次实战的内容如下：

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

源码下载

  1. 如果您不想编码，可以在GitHub下载所有源码，地址和链接信息如下表所示( https://github.com/zq2599/blo... ：
名称 链接 备注 项目主页 https://github.com/zq2599/blo... 该项目在GitHub上的主页 git仓库地址(https) https://github.com/zq2599/blo... 该项目源码的仓库地址，https协议 git仓库地址(ssh) [email protected]:zq2599/blog_demos.git 该项目源码的仓库地址，ssh协议
  1. 这个git项目中有多个文件夹，本章的应用在<font color="blue">mybatis</font>文件夹下，如下图红框所示：

7ryYBvi.png!mobile

创建数据库和表

  1. 创建名为<font color="blue">mybatis</font>的数据库，建表语句如下：
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. 创建名为<font color="blue">mybatis_second</font>的数据库，建表语句如下：
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，本文继续在此工程中新增子工程，名为<font color="blue">druidtwosource</font>，先提前看整个子工程文件结构，如下图，要注意的是红框1中的mapper接口，以及红框2中的mapper映射文件，这两处都按照数据库的不同放入各自文件夹：

FrEFBjB.png!mobile

  1. 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>
  1. 配置文件application.yml，可见这里面有<font color="red">first</font>和<font color="red">second</font>两个数据源配置，而druid的<font color="blue">web-stat-filter</font>和<font color="blue">stat-view-servlet</font>这两个配置是公用的：
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，要注意的是<font color="blue">排除掉数据源和事务的自动装配</font>，因为后面会手动编码执行这些配置：
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: [email protected]
 * @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", "[email protected]"))
                //版本号
                .version("1.0")
                //描述
                .description("API 描述")
                .build();
    }
}
  1. 数据源配置TwoDataSourceConfig.java，可见是通过<font color="blue">ConfigurationProperties</font>注解来确定配置信息，另外不要忘记在默认数据源上添加<font color="blue">Primary</font>注解：
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: [email protected]
 * @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注解的两个属性<font color="blue">basePackages</font>和<font color="blue">sqlSessionTemplateRef</font>是关键，<font color="red">它们最终决定了哪些mapper接口使用哪个数据源</font>，另外注意<font color="red">要带上</font>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: [email protected]
 * @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，注意<font color="red">不要带</font>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: [email protected]
 * @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: [email protected]
 * @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: [email protected]
 * @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不同，其他的全部一致：

qEfM3mM.png!mobile

  1. 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: [email protected]
 * @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表对应的单元测试操作如下图，三个测试方法先后新增记录，查询记录，然后删除掉：

UBFfamU.png!mobile

  1. AddrestControllerTest也按照上图做同样的操作；

验证，swagger

  1. 浏览器访问： http://localhost :8080/swagger-ui.html ，会展示swagger页面如下：

fy2Qva3.png!mobile

  1. 先来试试新增操作：

rUfqQvV.png!mobile

  1. 返回数据如下图：

aQVJNje.png!mobile

  1. 以下是用MySQL数据库客户端工具查看到的mybatis.user表的数据，可见服务功能正常：

3YvMZf6.png!mobile

  1. 其他接口请自行操作验证；

进入druid监控页面

  1. druid监控页面地址是： http://localhost :8080/druid ， 账号密码都是admin：

IvuEVn6.png!mobile

  1. 登录后可见数据库操作：

vYJb2mv.png!mobile

  1. 在数据源页面可以见到两个数据源，如下图：

iMnu6bi.png!mobile

Qrumian.png!mobile

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

你不孤单，欣宸原创一路相伴


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK