Spring Boot-Druid数据源配置类

2022-07-22 10:36:39

Spring Boot：Druid数据源配置类


    参考文档：Druid-wiki常见问题。

引入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包，编写配置类。

packagecom.xwd.config;importcom.alibaba.druid.pool.DruidDataSource;importcom.alibaba.druid.support.http.StatViewServlet;importcom.alibaba.druid.support.http.WebStatFilter;importorg.springframework.beans.factory.annotation.Value;importorg.springframework.boot.context.properties.ConfigurationProperties;importorg.springframework.boot.web.servlet.FilterRegistrationBean;importorg.springframework.boot.web.servlet.ServletRegistrationBean;importorg.springframework.context.annotation.*;importjava.sql.SQLException;importjava.util.Properties;/**
 * @ClassName DataSourceConfig
 * @Description: com.xwd.config
 * @Auther: xiwd
 * @version: 1.0
 */@Configuration@PropertySource({"classpath:druidDataSource.properties"})publicclassDataSourceConfig{//methods@Value("${spring.datasource.type}")privateString type;@Value("${spring.datasource.driver-class-name}")privateString driverClassName;@Value("${spring.datasource.url}")privateString url;@Value("${spring.datasource.username}")privateString username;@Value("${spring.datasource.password}")privateString password;//连接池信息@Value("${spring.datasource.druid.initial-size}")privateInteger initialSize;@Value("${spring.datasource.druid.min-idle}")privateInteger minIdle;@Value("${spring.datasource.druid.maxActive}")privateInteger maxActive;@Value("${spring.datasource.druid.maxWait}")privateInteger maxWait;@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")privateInteger timeBetweenEvictionRunsMillis;@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")privateInteger minEvictableIdleTimeMillis;@Value("${spring.datasource.druid.validationQuery}")privateString validationQuery;@Value("${spring.datasource.druid.testWhileIdle}")privateBoolean testWhileIdle;@Value("${spring.datasource.druid.testOnBorrow}")privateBoolean testOnBorrow;@Value("${spring.datasource.druid.testOnReturn}")privateBoolean testOnReturn;@Value("${spring.datasource.druid.poolPreparedStatements}")privateBoolean poolPreparedStatements;@Value("${spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize}")privateInteger maxPoolPreparedStatementPerConnectionSize;@Value("${spring.datasource.druid.filters}")privateString filters;@Value("${spring.datasource.druid.connectionProperties}")privateProperties connectionProperties;@Value("${spring.datasource.druid.web-stat-filter.enabled}")privateBoolean webStatFilterEnabled;@Value("${spring.datasource.druid.web-stat-filter.url-pattern}")privateString webStatFilterUrlPattern;@Value("${spring.datasource.druid.web-stat-filter.exclusions}")privateString webStatFilterExclusions;@Value("${spring.datasource.druid.stat-view-servlet.url-pattern}")privateString webStatViewServletUrlPattern;@Value("${spring.datasource.druid.stat-view-servlet.allow}")privateString webStatViewServletAllowedIPs;@Value("${spring.datasource.druid.stat-view-servlet.deny}")privateString webStatViewServletDenyIPs;@Value("${spring.datasource.druid.stat-view-servlet.reset-enable}")privateString webStatViewServletResetEnable;@Value("${spring.datasource.druid.stat-view-servlet.login-username}")privateString webStatViewServletLoginUsername;@Value("${spring.datasource.druid.stat-view-servlet.login-password}")privateString 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")publicDruidDataSourceDruidDataSource(){DruidDataSource druidDataSource=newDruidDataSource();//数据源连接参数配置
        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配置
     */@BeanpublicServletRegistrationBeanstatViewServlet(){ServletRegistrationBean servletRegistrationBean=newServletRegistrationBean(newStatViewServlet(),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
     */@BeanpublicFilterRegistrationBeanwebStatFilter(){FilterRegistrationBean filterRegistrationBean=newFilterRegistrationBean();
        filterRegistrationBean.setFilter(newWebStatFilter());//设置过滤器
        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 =  /*  #正确写法

在这里插入图片描述

  • 作者：似曾不相识
  • 原文链接：https://blog.csdn.net/weixin_43524214/article/details/123911472
    更新时间：2022-07-22 10:36:39