欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-28 20:06:31
一、Druid连接池 1、druid简介 Druid连接池是阿里巴巴开源的数据库连接池项目。Druid连接池为监控而生,内置强大的监控功能,监控特性不影响性能。功能强大,能防SQL注入,内置Loging能诊断Hack应用行为。 Druid连接池是阿里巴巴内部唯一使用的连接池,在内部数据库相关中间件T ......

一、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" ;
    }
}

三、测试效果

完成一次数据请求后,访问如下链接。
SpringBoot2.0 基础案例(07):集成Druid连接池,配置监控界面

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

1、druid监控首页

主要展示链接数据库的基础信息。
SpringBoot2.0 基础案例(07):集成Druid连接池,配置监控界面

2、druid监控数据源

连接池配置的各项详细属性,可以参考这里查看,无需再从网上查找。
SpringBoot2.0 基础案例(07):集成Druid连接池,配置监控界面

3、druid监控sql执行

所有执行的sql,都会在这里被监控到,且会有sql执行的详细计划。
SpringBoot2.0 基础案例(07):集成Druid连接池,配置监控界面

四、源代码

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

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