springboot集成druid实现数据源监控

 

1、导入依赖

<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>

 

2、配置文件

spring.datasource.druid.username=root
spring.datasource.druid.password=123456
#连接的URL
spring.datasource.druid.url=jdbc:mysql://192.169.1.38:3306/emp_test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&serverTimezone=Asia/Shanghai
#检测连接是否有效的sql
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
###监控配置
# WebStatFilter配置
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,*.png,*.css,*.ico,/druid/*
# StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
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=admin
#允许登录监控页面ip白名单，不配置即所有ip均是白名单
spring.datasource.druid.stat-view-servlet.allow=
# 配置StatFilter
spring.datasource.druid.filter.stat.enable=true
spring.datasource.druid.filter.stat.db-type=mysql
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=5
# 配置WallFilter
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql
spring.datasource.druid.filter.wall.config.delete-allow=true
spring.datasource.druid.filter.wall.config.drop-table-allow=false
spring.datasource.druid.filter.wall.config.multi-statement-allow=true

 

3、发送http请求执行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 : 2.0
 * @date : 2020/6/17 9:32
 */
@RestController
public class UserController {

    private static final Logger logger = LoggerFactory.getLogger(UserController.class);

    @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

 

 

4、监控管理后台

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

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

 

 SQL监控页面

 

5、各配置项说明

 

配置项	说明
spring.datasource.druid.web-stat-filter.enabled=true	开启WebStatFilter拦截
spring.datasource.druid.web-stat-filter.url-pattern=/*	拦截哪些请求
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*	排查拦截哪些请求
spring.datasource.druid.stat-view-servlet.enabled=true	开启StatViewFilter监控管理后台
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*	监控管理后台访问地址
spring.datasource.druid.stat-view-servlet.reset-enable=false	是否关闭监控管理后台重置(Reset All)按钮
spring.datasource.druid.stat-view-servlet.login-username=admin	监控管理后台登录用户名
spring.datasource.druid.stat-view-servlet.login-password=123456	监控管理后台登录密码
spring.datasource.druid.stat-view-servlet.allow=	允许登录监控页面ip白名单，不配置即所有ip均是白名单
spring.datasource.druid.filter.stat.enable=true
spring.datasource.druid.filter.stat.db-type=mysql	数据库类型
spring.datasource.druid.filter.stat.log-slow-sql=true	显示慢日志
spring.datasource.druid.filter.stat.slow-sql-millis=5000	sql查询超过多少秒被判定是慢日志
spring.datasource.druid.filter.stat.merge-sql=true	是否合并Sql
spring.datasource.druid.filter.wall.enabled=true
spring.datasource.druid.filter.wall.db-type=mysql	数据库类型
spring.datasource.druid.filter.wall.config.delete-allow=true	是否允许执行DELETE语句
spring.datasource.druid.filter.wall.config.drop-table-allow=false	是否允许修改表
spring.datasource.druid.filter.wall.config.multi-statement-allow=true	是否允许一次执行多条语句，缺省关闭

 

关于是否合并sql的理解(spring.datasource.druid.filter.stat.merge-sql=true)

 

当你程序中存在没有参数化的sql执行时，sql统计的效果会不好。比如：

select * from t where id = 1
select * from t where id = 2
select * from t where id = 3

在统计中，显示为3条sql，这不是我们希望要的效果。StatFilter提供合并的功能，能够将这3个SQL合并为如下的SQL

select * from t where id = ?

 

参考地址

Druid使用手册: https://www.bookstack.cn/read/Druid/ffdd9118e6208531.md

Druid（二）监控统计功能之StatFilter:https://blog.csdn.net/HSH205572/article/details/86608705

druid github地址:https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter