SpringBoot2.0 基础案例(07)：集成Druid连接池，配置监控界面

一、druid连接池

1、druid简介

druid连接池是阿里巴巴开源的数据库连接池项目。druid连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防sql注入，内置loging能诊断hack应用行为。
druid连接池是阿里巴巴内部唯一使用的连接池，在内部数据库相关中间件tddl/drds 都内置使用强依赖了druid连接池，经过阿里内部数千上万的系统大规模验证，经过历年双十一超大规模并发验证。

2、druid特点

1）稳定性特性，阿里巴巴的业务验证
2）完备的监控信息，快速诊断系统的瓶颈
3）内置了wallfilter 提供防sql注入功能

二、整合springboot2.0框架

1、引入核心依赖

<!-- 数据库依赖 -->
<dependency>
    <groupid>mysql</groupid>
    <artifactid>mysql-connector-java</artifactid>
    <version>5.1.21</version>
</dependency>
<dependency>
    <groupid>com.alibaba</groupid>
    <artifactid>druid-spring-boot-starter</artifactid>
    <version>1.1.13</version>
</dependency>
<!-- jdbc 依赖 -->
<dependency>
    <groupid>org.springframework.boot</groupid>
    <artifactid>spring-boot-starter-jdbc</artifactid>
</dependency>

2、数据源配置文件

spring:
  application:
    # 应用名称
    name: node07-boot-druid
  datasource:
    type: com.alibaba.druid.pool.druiddatasource
    druid:
      driverclassname: com.mysql.jdbc.driver
      url: jdbc:mysql://localhost:3306/data_one?useunicode=true&characterencoding=utf8&zerodatetimebehavior=converttonull&usessl=false
      username: root
      password: 123
      initial-size: 10
      max-active: 100
      min-idle: 10
      max-wait: 60000
      pool-prepared-statements: true
      max-pool-prepared-statement-per-connection-size: 20
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      max-evictable-idle-time-millis: 60000
      validation-query: select 1 from dual
      # validation-query-timeout: 5000
      test-on-borrow: false
      test-on-return: false
      test-while-idle: true
      connectionproperties: druid.stat.mergesql=true;druid.stat.slowsqlmillis=5000
      #filters: #配置多个英文逗号分隔(统计，sql注入，log4j过滤)
      filters: stat,wall
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*

3、核心配置类

import com.alibaba.druid.pool.druiddatasource;
import com.alibaba.druid.support.http.statviewservlet;
import com.alibaba.druid.support.http.webstatfilter;
import org.slf4j.logger;
import org.slf4j.loggerfactory;
import org.springframework.beans.factory.annotation.autowired;
import org.springframework.beans.factory.annotation.value;
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 org.springframework.jdbc.core.jdbctemplate;

/**
 * druid数据库连接池配置文件
 */
@configuration
public class druidconfig {
    private static final logger logger = loggerfactory.getlogger(druidconfig.class);
    @value("${spring.datasource.druid.url}")
    private string dburl;
    @value("${spring.datasource.druid.username}")
    private string username;
    @value("${spring.datasource.druid.password}")
    private string password;
    @value("${spring.datasource.druid.driverclassname}")
    private string driverclassname;
    @value("${spring.datasource.druid.initial-size}")
    private int initialsize;
    @value("${spring.datasource.druid.max-active}")
    private int maxactive;
    @value("${spring.datasource.druid.min-idle}")
    private int minidle;
    @value("${spring.datasource.druid.max-wait}")
    private int maxwait;
    @value("${spring.datasource.druid.pool-prepared-statements}")
    private boolean poolpreparedstatements;
    @value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
    private int maxpoolpreparedstatementperconnectionsize;
    @value("${spring.datasource.druid.time-between-eviction-runs-millis}")
    private int timebetweenevictionrunsmillis;
    @value("${spring.datasource.druid.min-evictable-idle-time-millis}")
    private int minevictableidletimemillis;
    @value("${spring.datasource.druid.max-evictable-idle-time-millis}")
    private int maxevictableidletimemillis;
    @value("${spring.datasource.druid.validation-query}")
    private string validationquery;
    @value("${spring.datasource.druid.test-while-idle}")
    private boolean testwhileidle;
    @value("${spring.datasource.druid.test-on-borrow}")
    private boolean testonborrow;
    @value("${spring.datasource.druid.test-on-return}")
    private boolean testonreturn;
    @value("${spring.datasource.druid.filters}")
    private string filters;
    @value("{spring.datasource.druid.connection-properties}")
    private string connectionproperties;
    /**
     * druid 连接池配置
     */
    @bean     //声明其为bean实例
    public druiddatasource datasource() {
        druiddatasource datasource = new druiddatasource();
        datasource.seturl(dburl);
        datasource.setusername(username);
        datasource.setpassword(password);
        datasource.setdriverclassname(driverclassname);
        datasource.setinitialsize(initialsize);
        datasource.setminidle(minidle);
        datasource.setmaxactive(maxactive);
        datasource.setmaxwait(maxwait);
        datasource.settimebetweenevictionrunsmillis(timebetweenevictionrunsmillis);
        datasource.setminevictableidletimemillis(minevictableidletimemillis);
        datasource.setmaxevictableidletimemillis(minevictableidletimemillis);
        datasource.setvalidationquery(validationquery);
        datasource.settestwhileidle(testwhileidle);
        datasource.settestonborrow(testonborrow);
        datasource.settestonreturn(testonreturn);
        datasource.setpoolpreparedstatements(poolpreparedstatements);
        datasource.setmaxpoolpreparedstatementperconnectionsize(maxpoolpreparedstatementperconnectionsize);
        try {
            datasource.setfilters(filters);
        } catch (exception e) {
            logger.error("druid configuration initialization filter", e);
        }
        datasource.setconnectionproperties(connectionproperties);
        return datasource;
    }
    /**
     * jdbc操作配置
     */
    @bean(name = "dataonetemplate")
    public jdbctemplate jdbctemplate (@autowired druiddatasource datasource){
        return new jdbctemplate(datasource) ;
    }

    /**
     * 配置 druid 监控界面
     */
    @bean
    public servletregistrationbean statviewservlet(){
        servletregistrationbean srb =
                new servletregistrationbean(new statviewservlet(),"/druid/*");
        //设置控制台管理用户
        srb.addinitparameter("loginusername","root");
        srb.addinitparameter("loginpassword","root");
        //是否可以重置数据
        srb.addinitparameter("resetenable","false");
        return srb;
    }
    @bean
    public filterregistrationbean statfilter(){
        //创建过滤器
        filterregistrationbean frb =
                new filterregistrationbean(new webstatfilter());
        //设置过滤器过滤路径
        frb.addurlpatterns("/*");
        //忽略过滤的形式
        frb.addinitparameter("exclusions",
                "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return frb;
    }
}

4、简单测试类

@restcontroller
public class druidcontroller {
    private static final logger log = loggerfactory.getlogger(druidcontroller.class);
    @resource
    private jdbctemplate jdbctemplate ;
    @requestmapping("/druiddata")
    public string druiddata (){
        string sql = "select count(1) from d_phone" ;
        integer countone = jdbctemplate.queryforobject(sql,integer.class) ;
        // countone==2
        log.info("countone=="+countone);
        return "success" ;
    }
}

三、测试效果

完成一次数据请求后，访问如下链接。

http://localhost:8007/druid
输入配置的用户名和密码：
root root

1、druid监控首页

主要展示链接数据库的基础信息。

2、druid监控数据源

连接池配置的各项详细属性，可以参考这里查看，无需再从网上查找。

3、druid监控sql执行

所有执行的sql，都会在这里被监控到，且会有sql执行的详细计划。

四、源代码

github地址：知了一笑
https://github.com/cicadasmile/spring-boot-base
码云地址：知了一笑
https://gitee.com/cicadasmile/spring-boot-base