SpringBoot整合Druid并配置数据源监控

一、导入Maven依赖

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.1.0</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>2.1.5.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
            <optional>true</optional>
            <version>2.1.5.RELEASE</version>
        </dependency>
    </dependencies>

二、配置文件

appliaction.yml内容为

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.169.1.21:3306/msg_his?serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    # 数据源其它配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

这样配置数据库连接池参数是不生效的 ,因为我们的所有属性都设置再spring.datasource下，而spring.datasource这些参数都在DataSourceProperties这个类所对应的，但是在DataSourceProperties这个配置下并没有这些druid连接池参数。所以，我们做如下配置

package com.harara.druid.config;

import com.alibaba.druid.pool.DruidDataSource;import org.springframework.boot.context.properties.ConfigurationProperties;import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;/**
 * @author : harara
 * @version : 1.0
 * @date : 2020/6/16 16:59
 */
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

}

三、配置Druid数据源监控

package com.harara.druid.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
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.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @author : harara
 * @version : 1.0
 * @date : 2020/6/16 16:59
 */
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    /**
     * 配置Druid的监控
     * 配置一个监控管理后台的Servlet
     * @return
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");

        Map<String,String> initParam = new HashMap<String, String>();
        //允许登录的用户名
        initParam.put("loginUsername","admin");
        //允许登录的密码
        initParam.put("loginPassword","123456");
        //允许哪些ip地址访问，不写就是默认允许所有访问
        initParam.put("allow","");
        //拒绝访问ip名单
        initParam.put("deny","192.169.1.1");
        //添加初始化参数
        bean.setInitParameters(initParam);
        return bean;
    }

    /**
     * 配置一个web监控的filter
     * @return
     */
    @Bean
    public FilterRegistrationBean druidWebStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        //拦截哪些请求
        bean.setUrlPatterns(Arrays.asList("/*"));

        Map<String,String> initParam = new HashMap<String, String>();
        //排除拦截哪些请求
        initParam.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParam);
        return bean;
  }

}

四、访问监控页面

输入http://localhost:8080/druid访问监控管理后台页面

使用上面代码中设置的账号密码进行登录

 

五、发送请求执行sql测试

package com.harara.druid.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

/**
 * @author : harara
 * @version : 1.0
 * @date : 2020/6/17 9:32
 */
@RestController
public class UserController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/user")
    public List<Map<String, Object>> hello() {
        List<Map<String, Object>> lists = jdbcTemplate.queryForList("select host,user from mysql.user ", new Object[]{});
        return lists;
    }
}

请求 http://localhost:8080/user

六、SQL监控页面显示执行的sql

SQL监控页面