欢迎访问我的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());
}
}
- 至此,编码完成,而可以开始验证了;
验证,单元测试
- user表对应的单元测试操作如下图,三个测试方法先后新增记录,查询记录,然后删除掉:
2. AddrestControllerTest也按照上图做同样的操作;
验证,swagger
- 浏览器访问:http://localhost:8080/swagger-ui.html ,会展示swagger页面如下:
2. 先来试试新增操作:
3. 返回数据如下图:
4. 以下是用MySQL数据库客户端工具查看到的mybatis.user表的数据,可见服务功能正常:
5. 其他接口请自行操作验证;
进入druid监控页面
- druid监控页面地址是:http://localhost:8080/druid , 账号密码都是admin:
2. 登录后可见数据库操作:
3. 在数据源页面可以见到两个数据源,如下图:
- 以上就是完整的springboot+mybatis+druid多数据源开发和验证过程,希望能给您一些参考;
你不孤单,欣宸原创一路相伴
- Java系列
- Spring系列
- Docker系列
- kubernetes系列
- 数据库+中间件系列
- DevOps系列
欢迎关注公众号:程序员欣宸
微信搜索「程序员欣宸」,我是欣宸,期待与您一同畅游Java世界…
https://github.com/zq2599/blog_demos