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

骑猪看日落 2023-10-04 23:09 25阅读 0赞

##### 1. 简介 #####

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

##### 2. 相关博客 #####

[DB SQL 转 ES DSL(支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法)][DB SQL _ ES DSL]

##### 3. 示例代码 #####

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

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');

*  创建项目![c5195f8939040004c8ce01a2ef7a43b5.jpeg][]
 *  修改pom.xml

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

*  配置数据源、监控等信息

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

*  创建实体类

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

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

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<>());
        }
    
    }

*  创建启动类

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_192.168.0.101_8080_druid]![73575877cd521e7f853bbd2c4eea93f6.jpeg][]
     *  白名单访问[http://127.0.0.1:8080/druid][http_127.0.0.1_8080_druid]![f1ebde89ce1b7bd423a0fd32322a9001.jpeg][]
     *  输入错误的用户名/密码![cdba398e75433a12926f8bd2711d54d6.jpeg][]
     *  输入正确的用户名/密码![1570788541ba8dc90178f63bf060548b.jpeg][]
 *  验证SQL统计(SQL合并、慢SQL)
    
     *  访问根据ID查询用户接口
    
        curl http://127.0.0.1:8080/user/1
    
    ![66f7322d93049f81f87c900818d8cb7a.jpeg][]
    
     *  访问查询所有用户接口
    
    为了测试慢SQL，需要给用户表多预置一些数据。
    
        curl http://127.0.0.1:8080/user/list
    
    ![4c62fb58824bc0b8c97f18cd54ad8214.jpeg][]  
    后台日志打印：
    
        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条件
    
        http://127.0.0.1:8080/user/update
    
    接口报500错误，后台日志打印(update none condition not allow)：
    
        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条件
    
        curl http://127.0.0.1:8080/user/delete
    
    接口报500错误，后台日志打印(delete none condition not allow)：
    
        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
        
        ......
    
    ![90f84749ae428b245377cca10a9190d2.jpeg][]
 *  WEB应用![b2f7efea99e80d4c3b3bb06fb1464cc8.jpeg][]
 *  URL监控![ccae0e51a8f877092afc8362ea91f991.jpeg][]

##### 5. 项目地址 #####

[spring-boot-druid-demo][]

[https_github.com_alibaba_druid]: https://github.com/alibaba/druid
[DB SQL _ ES DSL]: https://www.cnblogs.com/cao-lei/p/17062889.html
[c5195f8939040004c8ce01a2ef7a43b5.jpeg]: https://img-blog.csdnimg.cn/img_convert/c5195f8939040004c8ce01a2ef7a43b5.jpeg
[http_192.168.0.101_8080_druid]: http://192.168.0.101:8080/druid
[73575877cd521e7f853bbd2c4eea93f6.jpeg]: https://img-blog.csdnimg.cn/img_convert/73575877cd521e7f853bbd2c4eea93f6.jpeg
[http_127.0.0.1_8080_druid]: http://127.0.0.1:8080/druid
[f1ebde89ce1b7bd423a0fd32322a9001.jpeg]: https://img-blog.csdnimg.cn/img_convert/f1ebde89ce1b7bd423a0fd32322a9001.jpeg
[cdba398e75433a12926f8bd2711d54d6.jpeg]: https://img-blog.csdnimg.cn/img_convert/cdba398e75433a12926f8bd2711d54d6.jpeg
[1570788541ba8dc90178f63bf060548b.jpeg]: https://img-blog.csdnimg.cn/img_convert/1570788541ba8dc90178f63bf060548b.jpeg
[66f7322d93049f81f87c900818d8cb7a.jpeg]: https://img-blog.csdnimg.cn/img_convert/66f7322d93049f81f87c900818d8cb7a.jpeg
[4c62fb58824bc0b8c97f18cd54ad8214.jpeg]: https://img-blog.csdnimg.cn/img_convert/4c62fb58824bc0b8c97f18cd54ad8214.jpeg
[90f84749ae428b245377cca10a9190d2.jpeg]: https://img-blog.csdnimg.cn/img_convert/90f84749ae428b245377cca10a9190d2.jpeg
[b2f7efea99e80d4c3b3bb06fb1464cc8.jpeg]: https://img-blog.csdnimg.cn/img_convert/b2f7efea99e80d4c3b3bb06fb1464cc8.jpeg
[ccae0e51a8f877092afc8362ea91f991.jpeg]: https://img-blog.csdnimg.cn/img_convert/ccae0e51a8f877092afc8362ea91f991.jpeg
[spring-boot-druid-demo]: https://github.com/C3Stones/blog