1.新建springboot工程

1.1 pom依赖

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.6.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>druidDemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>druidDemo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

1.2 application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
#   　  # MYSQL 5 驱动com.mysql.jdbc.Driver;MYSQL 6+ 驱动com.mysql.cj.jdbc.Driver
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/sampledb?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
    # 连接池配置
    # 初始化时建立物理连接的个数
    initial-size: 5
    # 连接池包含连接的最大数量
    max-active: 20
    # 获取连接时最大等待时间，单位ms
    max-wait: 60000
    # 连接池包含连接的最小数量
    min-idle: 1
    # 申请连接时是否执行validationQuery检测连接的有效性，影响性能
    test-on-borrow: false
    # 归还连接时是否执行validationQuery检测连接的有效性，影响性能
    test-on-return: false

    # 避免mysql数据库单方面关闭连接，如mysql: show variable like '%timeout%'
    # 关注interactive_timeout和wait_timeout，默认8h,对于mysql的8h问题，解决办法:
    # 1.minEvictableIdleTimeMillis < maxEvictableIdleTimeMillis
    # 2.maxEvictableIdleTimeMillis + timeBetweenEvictionRunsMillis < 8h

    # 单位：ms，连接保持空闲而不被驱逐的最小时间，默认30min
    # 连接被destroy线程关闭条件：>minIdle && 空闲时间 > 1800000
    min-evictable-idle-time-millis: 1800000

    # 单位：ms，连接保持空闲而不被驱逐的最大时间，默认7h
    # 连接被destroy线程关闭条件：空闲时间 > 25200000，不管池子中的连接数是否>minIdle
    max-evictable-idle-time-millis: 25200000

    # 单位ms，默认1min，作用有两个:
    # 1.Destroy线程运行周期，见min-evictable-idle-time-millis和max-evictable-idle-time-millis
    # 2.test-while-idle使用，申请连接时，若空闲时间>time-between-eviction-runs-millis
    # 则执行validation-query检测连接是否有效 单位ms
    time-between-eviction-runs-millis: 60000
    test-while-idle: true

    # 检测连接是否有效的sql
    validation-query: SELECT 1

1.3 logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="false" scanPeriod="60 seconds" debug="false">
    <appender name="stdout" class="ch.qos.logback.core.ConsoleAppender">
        <layout class="ch.qos.logback.classic.PatternLayout">
            <pattern>%-4relative [%thread] %-5level %logger{35} - %msg %n</pattern>
        </layout>
    </appender>

    <root level="info">
        <appender-ref ref="stdout"/>
    </root>
</configuration>

1.4 测试类DruidDemoApplicationTests

package com.example.druiddemo;

import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.sql.DataSource;
import java.sql.SQLException;

@RunWith(SpringRunner.class)
@SpringBootTest
class DruidDemoApplicationTests {

    @Autowired
    private DataSource dataSource;

    @Test
    public void contextLoads() throws SQLException {
        System.out.println(dataSource.getConnection());
        System.out.println(dataSource);
        System.out.println(dataSource.getClass());
    }

}

1.5 运行测试用例结果

com.mysql.cj.jdbc.ConnectionImpl@313f8301
{
    CreateTime:"2022-03-16 20:36:56",
    ActiveCount:1,
    PoolingCount:0,
    CreateCount:1,
    DestroyCount:0,
    CloseCount:0,
    ConnectCount:1,
    Connections:[
    ]
}

配置文件中指定初始化5个连接，运行日志中只有1一个连接，差异的原因是因为datasource默认在DataSourceAutoConfiguration中被初始化，引入DataSourceConfiguration.Generic.class初始化数据源，DataSourceProperties中并没有对应上面1.2节中的初始化数据库连接池数量的属性，需要自行指定一个datasource配置属性类，如下

1.6 增加druid数据库属性配置

package com.example.druiddemo.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.List;

@Configuration
public class DemoDataSourceConfiguration {

    /**
     * 配置完yml后，springboot帮我们生成了druidDataSource
     * 默认
     * @param filters
     * @return
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource(@Autowired(required = false) List<Filter> filters) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setProxyFilters(filters);
        return druidDataSource;
    }
}

测试结果如下，达到预期

com.mysql.cj.jdbc.ConnectionImpl@5afbd567
{
    CreateTime:"2022-03-16 20:47:11",
    ActiveCount:1,
    PoolingCount:4,
    CreateCount:5,
    DestroyCount:0,
    CloseCount:0,
    ConnectCount:1,
    Connections:[
        {ID:1771292895, ConnectTime:"2022-03-16 20:47:13", UseCount:0, LastActiveTime:"2022-03-16 20:47:13"},
        {ID:1465146431, ConnectTime:"2022-03-16 20:47:13", UseCount:0, LastActiveTime:"2022-03-16 20:47:13"},
        {ID:846072718, ConnectTime:"2022-03-16 20:47:13", UseCount:0, LastActiveTime:"2022-03-16 20:47:13"},
        {ID:918200393, ConnectTime:"2022-03-16 20:47:13", UseCount:0, LastActiveTime:"2022-03-16 20:47:13"}
    ]
}

1.7 解决mysql8小时问题

关注interactive_timeout和wait_timeout，默认8h,对于mysql的8h问题，解决办法:
1.minEvictableIdleTimeMillis < maxEvictableIdleTimeMillis
2.maxEvictableIdleTimeMillis + timeBetweenEvictionRunsMillis < 8h

mysql相关命令
show VARIABLES like '%timeout%';
show variables like '%max_connections%';
show global status like 'Max_used_connections';

1.8 拓展分析mysql驱动创建连接源码

com.mysql.cj.jdbc.Driver 继承 com.mysql.cj.jdbc.NonRegisteringDriver
获取connect

com.mysql.cj.jdbc.NonRegisteringDriver#connect
--> com.mysql.cj.jdbc.ConnectionImpl#getInstance
--> com.mysql.cj.jdbc.ConnectionImpl#createNewIO
--> com.mysql.cj.NativeSession#connect
--> com.mysql.cj.protocol.a.NativeSocketConnection#connect
--> com.mysql.cj.protocol.StandardSocketFactory#connect

最终创建socket代码：

for (int i = 0; i < possibleAddresses.length; i++) { // length一般为 1
    try {
        this.rawSocket = createSocket(pset); // 创建socket

        configureSocket(this.rawSocket, pset);

        InetSocketAddress sockAddr = new InetSocketAddress(possibleAddresses[i], this.port);
        // bind to the local port if not using the ephemeral port
        if (localSockAddr != null) {
            this.rawSocket.bind(localSockAddr); // 绑定数据库地址
        }

        this.rawSocket.connect(sockAddr, getRealTimeout(connectTimeout));

        break;
    } catch (SocketException ex) {
        lastException = ex;
        resetLoginTimeCountdown();
        this.rawSocket = null;
    }
}

由mysql驱动源码可以分析得出数据库连接池使用BIO实现TCP/IP通信；

2.启用监控控制台页面

2.1 在DemoDataSourceConfiguration增加监控servlet配置

    @Bean
    public ServletRegistrationBean<StatViewServlet> statServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid" +
                "/*");
        Map<String, String> initParam = new HashMap<>();
        initParam.put("loginUsername", "druid");
        initParam.put("loginPassword", "druid");
        // limit access
        initParam.put("allow", "127.0.0.1");
        initParam.put("deny", "192.168.3.3");
        bean.setInitParameters(initParam);
        return bean;
    }

启动服务并访问 http://localhost:8080/druid/index.html 可见如下页面

image.png

2.2 过滤器配置

DemoDataSourceConfiguration中增加StatFilter配置，注意此Filter不是与Servlet相关的Filter

    /**
     * 两种filter配置方式
     * 1.filter 使用别名
     * 2.proxyFilters 自定义
     * <p>
     * 作用：监控sql执行情况
     *
     * @return
     */
    @Bean
    public StatFilter statFilter() {
        StatFilter statFilter = new StatFilter();
        statFilter.setMergeSql(true);
        statFilter.setSlowSqlMillis(3000);
        return statFilter;
    }

增加Controller类

package com.example.druiddemo.controller;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@RestController
public class UserController {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/selectBookById")
    public List<Map<String, Object>> selectBook(@RequestParam("bookId") Integer bookId) {
        String sql = "select * from book where id = ?";
        return jdbcTemplate.queryForList(sql, bookId);
    }

    @RequestMapping("/deleteTest")
    public String deleteTest() {
        String sql = "delete from book where id = 1";
        jdbcTemplate.update(sql);
        return "deleteTest ok";
    }
}

重启服务访问 http://localhost:8080/selectBookById?bookId=1
登录http://localhost:8080/druid即可在“sql监控”里看到sql运行记录

2.3 防火墙配置

DemoDataSourceConfiguration增加禁止sql删除配置

    /**
     * 定制防火墙规则
     * 配置参考：
     *
     * @return
     */
    @Bean
    public WallConfig wallConfig() {
        WallConfig wallConfig = new WallConfig();
        wallConfig.setDeleteAllow(false);
        return wallConfig;
    }

    @Bean
    public WallFilter wallFilter(WallConfig wallConfig) {
        WallFilter wallFilter = new WallFilter();
        wallFilter.setDbType(DbType.mysql);
        wallFilter.setConfig(wallConfig);
        return wallFilter;
    }

重启服务访问 http://localhost:8080/deleteTest
登录http://localhost:8080/druid即可在“sql监控”里看到sql运行记录

2.4 定制slf4j日志规则

DemoDataSourceConfiguration增加日志规则配置

    @Bean
    public Slf4jLogFilter slf4jLogFilter() {
        Slf4jLogFilter slf4jLogFilter = new Slf4jLogFilter();
        // 最终可执行sql
        slf4jLogFilter.setStatementExecutableSqlLogEnable(true);
        return slf4jLogFilter;
    }

在logback.xml增加配置

    <!--配置父级名称-->
    <logger name="druid.sql" level="debug" additivity="false">
        <appender-ref ref="stdout"/>
    </logger>

重启服务，访问http://localhost:8080/selectBookById?bookId=1，控制台可见如下日志

21089 [http-nio-8080-exec-8] DEBUG druid.sql.Statement - {conn-10005, pstmt-20001} created. select * from book where id = ? 
21090 [http-nio-8080-exec-8] DEBUG druid.sql.Statement - {conn-10005, pstmt-20001} Parameters : [1] 
21090 [http-nio-8080-exec-8] DEBUG druid.sql.Statement - {conn-10005, pstmt-20001} Types : [INTEGER] 
21092 [http-nio-8080-exec-8] DEBUG druid.sql.Statement - {conn-10005, pstmt-20001} executed. select *
from book
where id = 1 
21092 [http-nio-8080-exec-8] DEBUG druid.sql.Statement - {conn-10005, pstmt-20001, rs-50001} query executed. 2.567246 millis. select * from book where id = ? 
21092 [http-nio-8080-exec-8] DEBUG druid.sql.ResultSet - {conn-10005, pstmt-20001, rs-50001} open

2.5 定制web模块监控规则

DemoDataSourceConfiguration增加监控配置

    @Bean
    public FilterRegistrationBean<WebStatFilter> webStatFilter() {
        FilterRegistrationBean<WebStatFilter> webStatFilterBean = new FilterRegistrationBean<>();
        WebStatFilter webStatFilter = new WebStatFilter();
        // 禁用session模块统计
        webStatFilter.setSessionStatEnable(true);
        webStatFilterBean.setFilter(webStatFilter);
        Map<String, String> initParam = new HashMap<>();
        initParam.put("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        webStatFilterBean.setInitParameters(initParam);
        webStatFilterBean.setUrlPatterns(Arrays.asList("/*"));
        return webStatFilterBean;
    }

重启服务访问 http://localhost:8080/deleteTest，http://localhost:8080/selectBookById?bookId=2
登录http://localhost:8080/druid即可在“web应用”，“URL监控”里看到web监控信息

3. 如何使用druid-spring-boot-starter

作用：druid-spring-boot-starter简化了springboot整合druid

3.1 pom

删除druid

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.6</version>
</dependency>

增加druid-spring-boot-starter

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

修改application.yml文件

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
#   　  # MYSQL 5 驱动com.mysql.jdbc.Driver;MYSQL 6+ 驱动com.mysql.cj.jdbc.Driver
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/sampledb?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=GMT%2B8
    username: root
    password: 123456
    druid: ## 最大的配置差异体现在这个节点
      # 连接池配置
      # 初始化时建立物理连接的个数
      initial-size: 5
      # 连接池包含连接的最大数量
      max-active: 20
      # 获取连接时最大等待时间，单位ms
      max-wait: 60000
      # 连接池包含连接的最小数量
      min-idle: 1
      # 申请连接时是否执行validationQuery检测连接的有效性，影响性能
      test-on-borrow: false
      # 归还连接时是否执行validationQuery检测连接的有效性，影响性能
      test-on-return: false

      # 避免mysql数据库单方面关闭连接，如mysql: show variable like '%timeout%'
      # 关注interactive_timeout和wait_timeout，默认8h,对于mysql的8h问题，解决办法:
      # 1.minEvictableIdleTimeMillis < maxEvictableIdleTimeMillis
      # 2.maxEvictableIdleTimeMillis + timeBetweenEvictionRunsMillis < 8h

      # 单位：ms，连接保持空闲而不被驱逐的最小时间，默认30min
      # 连接被destroy线程关闭条件：>minIdle && 空闲时间 > 1800000
      min-evictable-idle-time-millis: 1800000

      # 单位：ms，连接保持空闲而不被驱逐的最大时间，默认7h
      # 连接被destroy线程关闭条件：空闲时间 > 25200000，不管池子中的连接数是否>minIdle
      max-evictable-idle-time-millis: 25200000

      # 单位ms，默认1min，作用有两个:
      # 1.Destroy线程运行周期，见min-evictable-idle-time-millis和max-evictable-idle-time-millis
      # 2.test-while-idle使用，申请连接时，若空闲时间>time-between-eviction-runs-millis
      # 则执行validation-query检测连接是否有效 单位ms
      time-between-eviction-runs-millis: 60000
      test-while-idle: true

      # 检测连接是否有效的sql
      valid ation-query: SELECT 1

    # 配置 DruidStatViewServlet
      stat-view-servlet:
        url-pattern: /druid/*
        # IP 白名单，没有配置或者为空，则允许所有访问
        allow: 127.0.0.1
        # IP 黑名单，若白名单也存在，则优先使用
        deny: 192.168.3.3
        # 登录用户名/密码
        login-username: druid
        login-password: druid
        enabled: true # 不配置，则打不开控制台页面
      # 监控过滤器配置
      filter:
        stat:
          enabled: true
          merge-sql: true
          slow-sql-millis: 3000
        wall:
          enabled: true
          db-type: mysql
          config:
            delete-allow: false
        slf4j:
          enabled: true
          statement-executable-sql-log-enable: true
      # web模块
      web-stat-filter:
        enabled: true
        session-stat-enable: false
        url-pattern: /*
        exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"

重启服务，重复2.1至2.5中的验证步骤。