Spring Boot集成Druid实现数据源管理与监控的详细步骤_Java

1. 引言

在程序员的日常工作中，经常需要编写数据库操作相关的程序，而这就需要数据连接池中间件用于管理数据库连接。数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。通过数据库连接池能明显提高对数据库操作的性能。在java应用程序开发中，常用的连接池有dbcp、c3p0、proxool等。

而在spring boot开发框架下，它默认提供了若干种可用的连接池，其中包括druid连接池（来自于阿里系的一个开源连接池），除在连接池之外，druid还提供了非常优秀的数据库监控和扩展功能。在此，根据项目实践中的应用，讲解如何实现spring boot与druid连接池的集成。

1.1 环境准备

jdk 1.8
spring boot 2.0.0.release
mysql 5.7
druid 1.1.9

1.2 druid介绍

druid是阿里开源的一个jdbc应用组件，其包括三部分：

druiddriver 代理driver，能够提供基于filter－chain模式的插件体系。
druiddatasource 高效可管理的数据库连接池。
sqlparser sql语法分析

通过druid连接池中间件，我们可以实现：

可以监控数据库访问性能，druid内置提供了一个功能强大的statfilter插件，能够详细统计sql的执行性能，这对于线上分析数据库访问性能有帮助。
替换传统的dbcp和c3p0连接池中间件。druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
数据库密码加密。直接把数据库密码写在配置文件中，这是不好的行为，容易导致安全问题。druiddruiver和druiddatasource都支持passwordcallback。
sql执行日志，druid提供了不同的logfilter，能够支持common-logging、log4j和jdklog，你可以按需要选择相应的logfilter，监控你应用的数据库访问情况。
扩展jdbc，如果你要对jdbc层有编程的需求，可以通过druid提供的filter-chain机制，很方便编写jdbc层的扩展插件。

关于druid的更多详细信息可以参考druid官方文档

2. 配置druid连接池

（1）添加maven依赖

<dependencies>
      ... 此处省略其他配置 ...
      <!-- configuration module -->
      <dependency>
        <groupid>org.springframework.boot</groupid>
        <artifactid>spring-boot-configuration-processor</artifactid>
        <optional>true</optional>
      </dependency>
      <!-- mysql driver -->
      <dependency>
        <groupid>mysql</groupid>
        <artifactid>mysql-connector-java</artifactid>
        <version>5.1.45</version>
            <scope>runtime</scope>
      </dependency>
      <!-- druid pool -->
      <dependency>
        <groupid>com.alibaba</groupid>
        <artifactid>druid</artifactid>
        <version>1.1.9</version>
      </dependency>
      <dependency>
            <groupid>com.alibaba</groupid>
            <artifactid>druid-spring-boot-starter</artifactid>
            <version>1.1.9</version>
      </dependency>
</dependencies>

（2） spring boot配置文件配置

spring boot配置文件有application.properties和application.yml两种配置文件方式，此处采用的是application.yml的配置方式。

# spring datasource settings
spring:
  datasource:
    name: druiddatasource
    type: com.alibaba.druid.pool.druiddatasource
    druid:
      driver-class-name: com.mysql.jdbc.driver
      url: jdbc:mysql://192.168.202.17:3306/auth_service?useunicode=true&characterencoding=utf-8&autoreconnect=true&failoverreadonly=false
      username: root
      password: 123456
      filters: stat,wall,log4j,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信息：

filter类名	别名
default	com.alibaba.druid.filter.stat.statfilter
stat	com.alibaba.druid.filter.stat.statfilter
mergestat	com.alibaba.druid.filter.stat.mergestatfilter
encoding	com.alibaba.druid.filter.encoding.encodingconvertfilter
log4j	com.alibaba.druid.filter.logging.log4jfilter
log4j2	com.alibaba.druid.filter.logging.log4j2filter
slf4j	com.alibaba.druid.filter.logging.slf4jlogfilter
commonlogging	com.alibaba.druid.filter.logging.commonslogfilter
wall	com.alibaba.druid.wall.wallfilter

（3） druid配置信息定制

通过druid-spring-boot-starter可以自动完成相关的配置，而无须自定义配置文件，具体参考druid-spring-boot-starter。

通过druid-spring-boot-starter的spring boot配置信息示例：

spring:
  datasource:
    name: druiddatasource
    type: com.alibaba.druid.pool.druiddatasource
    driver-class-name: com.mysql.jdbc.driver
    url: jdbc:mysql://192.168.202.17:3306/auth_service?useunicode=true&characterencoding=utf-8&autoreconnect=true&failoverreadonly=false
    username: root
    password: 123456
    druid:
      filters: stat,wall,log4j,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

在此，主要通过定制的配置文件对druid进行自定义属性配置，配置文件如下：

package com.garyond.hurricane.config;
import org.springframework.boot.context.properties.configurationproperties;
@configurationproperties(prefix = "spring.datasource.druid")
public class druiddatasourceproperties {
    private string driverclassname;
    private string url;
    private string username;
    private string password;
    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;
    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;
    }
}

（3）配置druid相关的servlet和filter

package com.garyond.hurricane.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.autowired;
import org.springframework.boot.autoconfigure.condition.conditionalonmissingbean;
import org.springframework.boot.context.properties.enableconfigurationproperties;
import org.springframework.boot.web.servlet.filterregistrationbean;
import org.springframework.boot.web.servlet.servletregistrationbean;
import org.springframework.context.annotation.bean;
import org.springframework.context.annotation.configuration;
import javax.sql.datasource;
import java.sql.sqlexception;
@configuration
@enableconfigurationproperties({druiddatasourceproperties.class})
public class druidconfig {
    @autowired
    private druiddatasourceproperties properties;
    @bean
    @conditionalonmissingbean
    public datasource druiddatasource() {
        druiddatasource druiddatasource = new druiddatasource();
        druiddatasource.setdriverclassname(properties.getdriverclassname());
        druiddatasource.seturl(properties.geturl());
        druiddatasource.setusername(properties.getusername());
        druiddatasource.setpassword(properties.getpassword());
        druiddatasource.setinitialsize(properties.getinitialsize());
        druiddatasource.setminidle(properties.getminidle());
        druiddatasource.setmaxactive(properties.getmaxactive());
        druiddatasource.setmaxwait(properties.getmaxwait());
        druiddatasource.settimebetweenevictionrunsmillis(properties.gettimebetweenevictionrunsmillis());
        druiddatasource.setminevictableidletimemillis(properties.getminevictableidletimemillis());
        druiddatasource.setvalidationquery(properties.getvalidationquery());
        druiddatasource.settestwhileidle(properties.istestwhileidle());
        druiddatasource.settestonborrow(properties.istestonborrow());
        druiddatasource.settestonreturn(properties.istestonreturn());
        druiddatasource.setpoolpreparedstatements(properties.ispoolpreparedstatements());
        druiddatasource.setmaxpoolpreparedstatementperconnectionsize(properties.getmaxpoolpreparedstatementperconnectionsize());
        try {
            druiddatasource.setfilters(properties.getfilters());
            druiddatasource.init();
        } catch (sqlexception e) {
            e.printstacktrace();
        }
        return druiddatasource;
    }
    /**
     * 注册servlet信息， 配置监控视图
     *
     * @return
     */
    @bean
    @conditionalonmissingbean
    public servletregistrationbean druidservlet() {
        servletregistrationbean servletregistrationbean = new servletregistrationbean(new statviewservlet(), "/druid/*");
        //白名单：
        servletregistrationbean.addinitparameter("allow","192.168.6.195");
        //ip黑名单 (存在共同时，deny优先于allow) : 如果满足deny的话提示:sorry, you are not permitted to view this page.
        servletregistrationbean.addinitparameter("deny","192.168.6.73");
        //登录查看信息的账号密码, 用于登录druid监控后台
        servletregistrationbean.addinitparameter("loginusername", "admin");
        servletregistrationbean.addinitparameter("loginpassword", "admin");
        //是否能够重置数据.
        servletregistrationbean.addinitparameter("resetenable", "true");
        return servletregistrationbean;
    }
    /**
     * 注册filter信息, 监控拦截器
     *
     * @return
     */
    @bean
    @conditionalonmissingbean
    public filterregistrationbean filterregistrationbean() {
        filterregistrationbean filterregistrationbean = new filterregistrationbean();
        filterregistrationbean.setfilter(new webstatfilter());
        filterregistrationbean.addurlpatterns("/*");
        filterregistrationbean.addinitparameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterregistrationbean;
    }
}

注：
- @enableconfigurationproperties({druiddatasourceproperties.class}) 用于导入上一步druid的配置信息
- public servletregistrationbean druidservlet() 相当于web servlet配置
- public filterregistrationbean filterregistrationbean() 相当于web filter配置

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

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

/**   
 * 配置监控拦截器, druid监控拦截器   
 * @classname: druidstatfilter    
 * @author garyond
 * @date 2018年4月24日
 */    
@webfilter(filtername="druidwebstatfilter",    
urlpatterns="/*",    
initparams={    
    @webinitparam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"),// 忽略资源    
})   
public class druidstatfilter extends webstatfilter {  
}

配置druid监控视图（相当于servletregistrationbean）

/**   
 * druid监控视图配置   
 *
 * @classname: druidstatviewservlet    
 * @author garyond  
 * @date 2018年4月24日 
 */    
@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;  
}