Spring Boot + Spring Cloud 实现权限管理系统 后端篇（七）：集成 Druid 数据源

Spring Boot + Spring Cloud 实现权限管理系统 后端篇（七）：集成 Druid 数据源

一、Druid 数据源优点，实用功能

1）可以监控数据库访问性能

2）提供了一个高效、功能强大、可扩展性好的数据库连接池

3）数据库密码加密。直接把数据库密码写在配置文件中，容易导致安全问题。

4）SQL执行日志，监控你应用的数据库访问情况。

二、使用

1.添加依赖

<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
   <version>1.1.10</version>
</dependency>

Druid Spring Boot Starter 是阿里官方提供的 Spring Boot 插件，用于帮助在Spring Boot项目中轻松集成Druid数据库连接池和监控。

2.添加配置

把原有的数据源配置替换成 druid 数据源并配置数据源相关参数。

eshop-boot/application.yml

# Tomcat
server:
  tomcat:
    uri-encoding: UTF-8
    max-threads: 1000
    min-spare-threads: 30
  port: 8088
    #context-path: /kitty-admin

# DataSource
spring:
  datasource:
    name: druidDataSource
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://localhost:3306/kitty?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8
      username: root
      password: 123456
      filters: stat,wall,log4j4,config
      max-active: 100
      initial-size: 1
      max-wait: 60000
      min-idle: 1
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: select 'x'
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      max-open-prepared-statements: 50
      max-pool-prepared-statement-per-connection-size: 20

参数说明：

• spring.datasource.druid.max-active  最大连接数 
  - spring.datasource.druid.initial-size  初始化大小 
  - spring.datasource.druid.min-idle  最小连接数 
  - spring.datasource.druid.max-wait  获取连接等待超时时间 
  - spring.datasource.druid.time-between-eviction-runs-millis  间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 
  - spring.datasource.druid.min-evictable-idle-time-millis  一个连接在池中最小生存的时间，单位是毫秒 
  - spring.datasource.druid.filters=config,stat,wall,log4j  配置监控统计拦截的filters，去掉后监控界面SQL无法进行统计，’wall’用于防火墙

Druid提供以下几种Filter信息：

自定义配置属性

如果需要通过定制的配置文件对druid进行自定义属性配置，添加配置类如下：

package com.wylc.eshop.boot.config;

/**
 * @author SongBin on 2019/5/7.
 */

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DruidDataSourceProperties {

    // jdbc
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    // jdbc connection pool
    private int initialSize;
    private int minIdle;
    private int maxActive = 100;
    private long maxWait;
    private long timeBetweenEvictionRunsMillis;
    private long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    // filter
    private String filters;

    public int getInitialSize() {
        return initialSize;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public long getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(long maxWait) {
        this.maxWait = maxWait;
    }

    public long getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public long getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public boolean isPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public int getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public String getFilters() {
        return filters;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

Druid Spring Starter 简化了很多配置，如果默认配置满足不了你的需求，可以自定义配置。更多配置参考：

Druid Spring Starter: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

配置Servlet和Filter

package com.wylc.eshop.boot.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.ParameterBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.schema.ModelRef;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Parameter;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;

import java.util.ArrayList;
import java.util.List;

/**
 * @author SongBin on 2019/4/30.
 */
@Configuration
@EnableSwagger2
public class SwaggerConfig {

    @Bean
    public Docket createRestApi(){
        // 添加请求参数，我们这里把token作为请求头部参数传入后端
        /*ParameterBuilder parameterBuilder = new ParameterBuilder();
        List<Parameter> parameters = new ArrayList<>();
        parameterBuilder.name("token").description("令牌")
                .modelRef(new ModelRef("string")).parameterType("header").required(false).build();
        parameters.add(parameterBuilder.build());
        return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo()).select()
                .apis(RequestHandlerSelectors.any()).paths(PathSelectors.any())
                .build().globalOperationParameters(parameters);*/
        return new Docket(DocumentationType.SWAGGER_2).apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.any())
                .paths(PathSelectors.any()).build();
    }

    private ApiInfo apiInfo(){
        return new ApiInfoBuilder()
                .title("E-Shop API Doc")
                .description("This is a restful api document of E-Shop.")
                .version("1.0")
                .build();
    }

}

说明:

- @EnableConfigurationProperties({DruidDataSourceProperties.class})  用于导入上一步Druid的配置信息 
- public ServletRegistrationBean druidServlet()  相当于Web Servlet配置 
- public FilterRegistrationBean filterRegistrationBean()  相当于Web Filter配置

如果不使用上述的Servlet和Filter配置， 也可以通过下述监控器配置实现：

配置监控拦截器（相当于FilterRegistrationBean）

package com.louis.kitty.boot.config;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

import com.alibaba.druid.support.http.WebStatFilter;

/**   
 * 配置监控拦截器, druid监控拦截器   
 */    
@WebFilter(filterName="druidWebStatFilter",    
urlPatterns="/*",    
initParams={    
    @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"), // 忽略资源    
})   
public class DruidStatFilter extends WebStatFilter {  

}

配置Druid监控视图（相当于ServletRegistrationBean）

package com.louis.kitty.boot.config;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

import com.alibaba.druid.support.http.StatViewServlet;

/**   
 * druid监控视图配置   
 */    
@WebServlet(urlPatterns = "/druid/*", initParams={    
    @WebInitParam(name="allow",value="192.168.6.195"),    // IP白名单 (没有配置或者为空，则允许所有访问)    
    @WebInitParam(name="deny",value="192.168.6.73"),    // IP黑名单 (存在共同时，deny优先于allow)    
    @WebInitParam(name="loginUsername",value="admin"),    // 用户名    
    @WebInitParam(name="loginPassword",value="admin"),    // 密码    
    @WebInitParam(name="resetEnable",value="true")    // 禁用HTML页面上的“Reset All”功能    
})   
public class DruidStatViewServlet extends StatViewServlet {  
    private static final long serialVersionUID = 7359758657306626394L;  
}

注：

配合log4j2使用，log4j2配置可参考：

springboot添加日志管理功能

参考资料

https://github.com/alibaba/druid/wiki

https://blog.csdn.net/garyond/article/details/80189939

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

Spring Boot + Spring Cloud 实现权限管理系统 后端篇（七）：集成 Druid 数据源

注意：本文归作者所有，未经作者允许，不得转载