SpringBoot + Druid + Mybatis-Plus + Mysql 实现数据库监控

1. 简介#

  在日常的WEB开发中都会使用数据库存储信息。大多数情况我们只是使用了数据库，而无法感知业务对数据库的压力，从而无法有目的的提升性能。在使用数据库时，都会选用常见的C3P0、DBCP、Hikari、Druid连接池，虽然SpringBoot官方强调Hikari性能更好，但我更倾向于功能更加全面的Druid连接池。
  Druid是阿里巴巴生态中的一员，除了提供数据库连接池，还提供SQL解析、数据源监控的能力。
  Github地址：https://github.com/alibaba/druid

3. 示例代码#

创建数据库及初始化表数据

Copy
CREATE DATABASE `test`;

USE `test`;

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `username` varchar(10) DEFAULT NULL COMMENT '用户名称',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`)
) COMMENT='用户表';

INSERT INTO `t_user`(`username`, `sex`, `phone`) VALUES ('张三', '1', '13500000000');
INSERT INTO `t_user`(`username`, `sex`, `phone`) VALUES ( '李四', '0', '18311111111');
INSERT INTO `t_user`(`username`, `sex`, `phone`) VALUES ( '王五', '1', '15522222222');

创建项目
修改pom.xml

Copy
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.c3stones</groupId>
    <artifactId>spring-boot-druid-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.8</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.16</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置数据源、监控等信息

Copy
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
    # Druid配置
    druid:
      # 初始化连接池大小
      initial-size: 5
      # 连接池最小空闲数
      min-idle: 5
      # 连接池最大连接数
      max-active: 20
      # 连接时最大等待时间（单位：毫秒）
      max-wait: 60000
      # 检测关闭空闲连接的时间间隔（单位：毫秒）
      time-between-eviction-runs-millis: 60000
      # 保持空闲连接不被关闭的最小生存时间（单位：毫秒）
      min-evictable-idle-time-millis: 300000
      # 检测连接有效的SQL
      # 为空则test-while-idle、test-on-borrow、test-on-return配置失效
      validation-query: SELECT 1
      # 检测连接是否有效的超时时间
      validation-query-timeout: 1
      # 检测空闲连接
      # 不影响性能，建议开启
      test-while-idle: true
      # 检测获取连接时的有效性
      # 开启后会影响性能
      test-on-borrow: false
      # 检测归还连接时的有效性
      # 开启后会影响性能
      test-on-return: false
      # 是否开启PSCache，即是否缓存preparedStatement（提升写入、查询效率）
      # 建议在支持游标的数据库开启，例如：Oracle
      pool-prepared-statements: false
      # 每个连接上PSCache的最大值
      # 如果大于0，pool-prepared-statements自动开启
      max-pool-prepared-statement-per-connection-size: -1
      # 配置默认的监控统计拦截的Filter
      # 不配置则监控页面中的SQL无法统计
      # stat - SQL监控配置
      # wall - SQL防火墙配置
      # slf4j - Druid日志配置
      filters: stat,wall,slf4j
      # 配置过滤器
      filter:
        # SQL监控配置
        stat:
          enabled: true
          db-type: mysql
          # 是否开启慢SQL统计
          log-slow-sql: true
          # 慢SQL时间
          slow-sql-millis: 10000
          # 慢SQL日志级别
          slow-sql-log-level: ERROR
          # 是否开启合并SQL
          # 开启后，select * from table where id = 1 和 select * from table where id = 2 将合并为 select * from table where id = ?
          merge-sql: true
        # SQL防火墙配置
        wall:
          enabled: true
          config:
            # 允许新增
            insert-allow: true
            # 允许更新
            update-allow: true
            # 禁止更新时无条件
            update-where-none-check: true
            # 允许删除
            delete-allow: true
            # 禁止删除时无条件
            delete-where-none-check: true
            # 禁止对表ALTER
            alter-table-allow: false
            # 禁止对表DROP
            drop-table-allow: false
        # Druid日志配置
        slf4j:
          enabled: true
          # 关闭数据源日志
          data-source-log-enabled: false
          # 关闭连接日志
          connection-log-enabled: false
          # 开启执行SQL日志
          statement-executable-sql-log-enable: true
          # 开启结果映射日志
          result-set-log-enabled: true
      # 配置统计页面
      stat-view-servlet:
        enabled: true
        # 允许重置监控数据
        reset-enable: true
        # 访问白名单
        allow: 127.0.0.1
        # 访问黑名单
        deny: 192.168.0.100
        # 访问用户名
        login-username: druid
        # 访问密码
        login-password: 123456
      # 配置统计页面过滤
      web-stat-filter:
        enabled: true
        # 过滤路径
        url-pattern: /*
        # 排除路径
        exclusions: .js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
        # 开启session统计
        session-stat-enable: true
        # session统计的最大个数
        session-stat-max-count: 100

# Mybatis-plus配置
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  global-config:
    db-config:
      # 主键类型：自增
      id-type: AUTO
      # 表前缀
      table-prefix: t_
#  configuration:
#    # 打印sql，生产建议关闭
#    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

创建实体类

Copy
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;

/**
 * 用户
 *
 * @author CL
 */
@Data
@TableName(keepGlobalPrefix = true)
public class User extends Model<User> {

    @TableId
    private Long id;

    @TableField
    private String username;

    @TableField
    private String sex;

    @TableField
    private String phone;

}

创建Mapper

Copy
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.c3stones.entity.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * 用户 Mapper
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

创建Controller

Copy
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.c3stones.entity.User;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * 用户 Controller
 *
 * @author CL
 */
@RestController
@RequestMapping("/user")
public class UserController {

    /**
     * 根据ID查询
     *
     * @param id ID
     * @return 用户信息
     */
    @RequestMapping("/{id}")
    public User get(@PathVariable Long id) {
        return new User().selectById(id);
    }

    /**
     * 查询全部用户
     *
     * @return 用户列表
     */
    @RequestMapping("/list")
    public List<User> list() {
        return new User().selectAll();
    }

    /**
     * 更新用户
     *
     * @return 更新结果
     */
    @RequestMapping("/update")
    public Boolean update() {
        return new User().update(new LambdaUpdateWrapper<User>().set(User::getPhone, "123456789"));
    }

    /**
     * 删除用户
     *
     * @return 删除结果
     */
    @RequestMapping("/delete")
    public Boolean delete() {
        return new User().delete(new QueryWrapper<>());
    }

}

创建启动类

Copy
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * 启动类
 *
 * @author CL
 */
@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

4. 测试#

测试访问权限
- 非白名单访问： http://192.168.0.101:8080/druid
- 白名单访问http://127.0.0.1:8080/druid
- 输入错误的用户名/密码
- 输入正确的用户名/密码

验证SQL统计（SQL合并、慢SQL）

访问根据ID查询用户接口

Copy
curl http://127.0.0.1:8080/user/1

访问查询所有用户接口

为了测试慢SQL，需要给用户表多预置一些数据。

Copy
curl http://127.0.0.1:8080/user/list

后台日志打印：

Copy
2023-02-26 20:13:20.197 ERROR 162564 --- [nio-8080-exec-1] c.alibaba.druid.filter.stat.StatFilter   : slow sql 19744 millis. SELECT  id,username,sex,phone  FROM t_user[]

验证SQL防火墙

验证更新时无WHERE条件

Copy
http://127.0.0.1:8080/user/update

接口报500错误，后台日志打印（update none condition not allow）：

Copy
2023-02-26 20:14:35.732 ERROR 73852 --- [nio-8080-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException: 
Error updating database.  Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.16, update none condition not allow : UPDATE t_user  SET phone=?

......

验证删除时无WHERE条件

Copy
curl http://127.0.0.1:8080/user/delete

接口报500错误，后台日志打印（delete none condition not allow）：

Copy
2023-02-26 20:15:19.536 ERROR 73852 --- [nio-8080-exec-6] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.apache.ibatis.exceptions.PersistenceException: 
Error updating database.  Cause: java.sql.SQLException: sql injection violation, dbType mysql, druid-version 1.2.16, delete none condition not allow : DELETE FROM t_user

......