druid removeAbandoned 生产环境配置 druid配置类

Spring Boot：Druid数据源配置类

• 引入MySQL连接器和Druid启动器
• properties配置文件
• 配置类编写
• Druid内置监控页面
• 遇见的问题

• 内置监控页面登录失败
• 项目启动失败：Unable to start embedded Tomcat

引入MySQL连接器和Druid启动器

<!--MySQL-连接器-->
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
        </dependency>
        <!--Druid-启动器-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

properties配置文件

    在SpringBoot项目的resources目录下提供Druid的配置druidDataSource.properties文件，提供配置参数信息。如果不知道该配置什么参数，那么开头贴出的Druid-WIKI官方地址会提供最优质的解析。

#数据源配置
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
# 使用阿里的Druid连接池
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
# 填写你数据库的url、登录名、密码和数据库名
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/testdb?useSSL=FALSE&useUnicode=TRUE&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.username = root
spring.datasource.password = root

# 连接池的配置信息
# 初始化大小，最小，最大
spring.datasource.druid.initial-size = 5
spring.datasource.druid.min-idle = 5
spring.datasource.druid.maxActive = 20
# 配置获取连接等待超时的时间-1min
spring.datasource.druid.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis = 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis = 300000
spring.datasource.druid.validationQuery = SELECT 1
spring.datasource.druid.testWhileIdle = true
spring.datasource.druid.testOnBorrow = false
spring.datasource.druid.testOnReturn = false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.poolPreparedStatements = true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters = stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 配置DruidStatFilter
spring.datasource.druid.web-stat-filter.enabled = true
spring.datasource.druid.web-stat-filter.url-pattern = /*
#过滤器-静态资源放行
spring.datasource.druid.web-stat-filter.exclusions = "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"

# 配置DruidStatViewServlet
spring.datasource.druid.stat-view-servlet.url-pattern = /druid/*
# IP白名单(没有配置或者为空，则允许所有访问)
spring.datasource.druid.stat-view-servlet.allow = 127.0.0.1,192.168.8.109
# IP黑名单 (存在共同时，deny优先于allow)
spring.datasource.druid.stat-view-servlet.deny = 192.168.1.188
#  禁用HTML页面上的“Reset All”功能
spring.datasource.druid.stat-view-servlet.reset-enable = false
# 登录名
spring.datasource.druid.stat-view-servlet.login-username = admin
# 登录密码
spring.datasource.druid.stat-view-servlet.login-password = amdin

配置类编写

    在SpringBoot项目新建config包，编写配置类。

package com.xwd.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.*;

import java.sql.SQLException;
import java.util.Properties;

/**
 * @ClassName DataSourceConfig
 * @Description: com.xwd.config
 * @Auther: xiwd
 * @version: 1.0
 */
@Configuration
@PropertySource({"classpath:druidDataSource.properties"})
public class DataSourceConfig {
    //methods
    @Value("${spring.datasource.type}")
    private String type;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    //连接池信息
    @Value("${spring.datasource.druid.initial-size}")
    private Integer initialSize;
    @Value("${spring.datasource.druid.min-idle}")
    private Integer minIdle;
    @Value("${spring.datasource.druid.maxActive}")
    private Integer maxActive;
    @Value("${spring.datasource.druid.maxWait}")
    private Integer maxWait;
    @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
    private Integer minEvictableIdleTimeMillis;
    @Value("${spring.datasource.druid.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.druid.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${spring.datasource.druid.testOnBorrow}")
    private Boolean testOnBorrow;
    @Value("${spring.datasource.druid.testOnReturn}")
    private Boolean testOnReturn;
    @Value("${spring.datasource.druid.poolPreparedStatements}")
    private Boolean poolPreparedStatements;
    @Value("${spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize}")
    private Integer maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.druid.filters}")
    private String filters;
    @Value("${spring.datasource.druid.connectionProperties}")
    private Properties connectionProperties;

    @Value("${spring.datasource.druid.web-stat-filter.enabled}")
    private Boolean webStatFilterEnabled;
    @Value("${spring.datasource.druid.web-stat-filter.url-pattern}")
    private String webStatFilterUrlPattern;
    @Value("${spring.datasource.druid.web-stat-filter.exclusions}")
    private String webStatFilterExclusions;

    @Value("${spring.datasource.druid.stat-view-servlet.url-pattern}")
    private String webStatViewServletUrlPattern;
    @Value("${spring.datasource.druid.stat-view-servlet.allow}")
    private String webStatViewServletAllowedIPs;
    @Value("${spring.datasource.druid.stat-view-servlet.deny}")
    private String webStatViewServletDenyIPs;
    @Value("${spring.datasource.druid.stat-view-servlet.reset-enable}")
    private String webStatViewServletResetEnable;
    @Value("${spring.datasource.druid.stat-view-servlet.login-username}")
    private String webStatViewServletLoginUsername;
    @Value("${spring.datasource.druid.stat-view-servlet.login-password}")
    private String webStatViewServletLoginPassword;
    /**
     * datasource:
     *             # 使用阿里的Druid连接池
     *       type: com.alibaba.druid.pool.DruidDataSource
     *       driver-class-name: com.mysql.cj.jdbc.Driver
     *       # 填写你数据库的url、登录名、密码和数据库名
     *       url: jdbc:mysql://127.0.0.1:3306/testdb?useSSL=FALSE&useUnicode=TRUE&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
     *       username: root
     *       password: root
     * @return
     */
    @Bean()
    @ConfigurationProperties(prefix = "spring.datasource")
    public DruidDataSource DruidDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        //数据源连接参数配置
        druidDataSource.setDriverClassName(driverClassName);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        //连接池参数设置
        druidDataSource.setInitialSize(this.initialSize);
        druidDataSource.setMinIdle(minIdle);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setMaxActive(maxActive);
        druidDataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
        druidDataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        druidDataSource.setValidationQuery(validationQuery);
        druidDataSource.setTestWhileIdle(testWhileIdle);
        druidDataSource.setTestOnBorrow(testOnBorrow);
        druidDataSource.setTestOnReturn(testOnReturn);
        druidDataSource.setPoolPreparedStatements(poolPreparedStatements);
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            druidDataSource.setFilters(filters);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        druidDataSource.setConnectProperties(connectionProperties);
        return druidDataSource;
    }


    /**
     * Druid监控servlet配置
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),webStatViewServletUrlPattern);
        //urlPattern-访问路径设置
        //IP配置
        servletRegistrationBean.addInitParameter("allow",webStatViewServletAllowedIPs);//IP白名单
        servletRegistrationBean.addInitParameter("deny",webStatViewServletDenyIPs);//IP黑名单
        //登陆账户配置
        servletRegistrationBean.addInitParameter("loginUsername",webStatViewServletLoginUsername);
        servletRegistrationBean.addInitParameter("loginPassword",webStatViewServletLoginPassword);
        //是否允许Reset All-计数器清零操作
        servletRegistrationBean.addInitParameter("resetEnable",webStatViewServletResetEnable);
        return servletRegistrationBean;
    }


    /**
     * Druid监控过滤器配置
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        //设置过滤器
        filterRegistrationBean.addUrlPatterns(webStatFilterUrlPattern);//url过滤规则
        filterRegistrationBean.addInitParameter("exclusions",webStatFilterExclusions);//忽略
        return filterRegistrationBean;
    }
}

Druid内置监控页面

    由于为内置监控页面配置了loginUsername、loginPassword两个参数，因此需要用户登录，登陆账户、密码信息由properties文件中提供。

    当SpringBoot的控制器处理一次SQL数据请求之后，就会刷新视图，记录SQL执行数、执行时间、读取行数等信息。

遇见的问题

内置监控页面登录失败

  &nsbp;  当执行完以上步骤启动项目后，如果发现直接通过参数设置内置监控登录页面账户和密码的方式，登录不成功，那么就直接在源代码中替换为String字符串。

项目启动失败：Unable to start embedded Tomcat

    首先查看properties配置文件中的配置信息是否正确，例如：像以下的路径信息，不应当添加双引号（“”），直接提供路径字面量值即可。
    自己手动编写properties出错率较低，但是如果直接从别处copy过来的，IDEA很可能会自动为路径值加上上引号。
    如果加了双引号，否则会报错：
Caused by: org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat
Caused by: java.lang.IllegalArgumentException: Invalid ["/*"] in filter mapping。

spring.datasource.druid.web-stat-filter.url-pattern = " /*"  #错误写法
spring.datasource.druid.web-stat-filter.url-pattern =  /*  #正确写法