SpringBoot3.x使用Druid

SpringBoot3.x使用Druid

• BiliBili视频
• 官网：druid/druid-spring-boot-starter at master · alibaba/druid
• 参考文章
  • Spring Boot 3 集成 Druid 连接池详解

起步

安装

• 最新版本查询：https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-3-starter
• 注意：Druid配置变更后需要重启才能生效

    <properties>
        <druid.version>1.2.21</druid.version>
    </properties>

    <dependencies>

        <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid-spring-boot-starter</artifactId>
           <version>${druid.version}</version>
        </dependency>
        
        <!-- SpringBoot3.x使用这个库，才能够开启Druid监控网页 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>
      
    </dependencies>

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource		# Druid连接池！
    
    # 以下注释内容与原数据库连接相同
    url: jdbc:mysql://***
    username: ***
    password: ***
    driver-class-name: com.mysql.cj.jdbc.Driver

• 其他连接池设置

spring.datasource.druid.socket-timeout= 6000 # 请求超时时间（单位：毫秒）

spring.datasource.druid.initial-size=
spring.datasource.druid.max-active=
spring.datasource.druid.min-idle=
spring.datasource.druid.max-wait=
spring.datasource.druid.pool-prepared-statements=
spring.datasource.druid.max-pool-prepared-statement-per-connection-size= 
spring.datasource.druid.max-open-prepared-statements= #和上面的等价
spring.datasource.druid.validation-query=
spring.datasource.druid.validation-query-timeout=
spring.datasource.druid.test-on-borrow=
spring.datasource.druid.test-on-return=
spring.datasource.druid.test-while-idle=
spring.datasource.druid.time-between-eviction-runs-millis=
spring.datasource.druid.min-evictable-idle-time-millis=
spring.datasource.druid.max-evictable-idle-time-millis=
spring.datasource.druid.filters= #配置多个英文逗号分隔
....//more

基础连接信息

• 非必须，无聊看着玩的
• 创建任意控制器和任意路径，返回Druid的连接信息

@RestController
@RequestMapping("/index")
public class IndexController {
    @GetMapping("/druid-status")
    public Object druidStat(){
        return DruidStatManagerFacade.getInstance().getDataSourceStatDataList();
    }
}

• 响应示例如下

[
  {
    "Identity": 1583082378,
    "Name": "DataSource-1583082378",
    "DbType": "h2",
    "DriverClassName": "org.h2.Driver",
    "URL": "jdbc:h2:file:./demo-db",
    "UserName": "sa",
    "FilterClassNames": [
      "com.alibaba.druid.filter.stat.StatFilter"
    ],
    "WaitThreadCount": 0,
    "NotEmptyWaitCount": 0,
    "NotEmptyWaitMillis": 0,
    "PoolingCount": 2,
    "PoolingPeak": 2,
    "PoolingPeakTime": 1533782955104,
    "ActiveCount": 0,
    "ActivePeak": 1,
    "ActivePeakTime": 1533782955178,
    "InitialSize": 2,
    "MinIdle": 2,
    "MaxActive": 30,
    "QueryTimeout": 0,
    "TransactionQueryTimeout": 0,
    "LoginTimeout": 0,
    "ValidConnectionCheckerClassName": null,
    "ExceptionSorterClassName": null,
    "TestOnBorrow": true,
    "TestOnReturn": true,
    "TestWhileIdle": true,
    "DefaultAutoCommit": true,
    "DefaultReadOnly": null,
    "DefaultTransactionIsolation": null,
    "LogicConnectCount": 103,
    "LogicCloseCount": 103,
    "LogicConnectErrorCount": 0,
    "PhysicalConnectCount": 2,
    "PhysicalCloseCount": 0,
    "PhysicalConnectErrorCount": 0,
    "ExecuteCount": 102,
    "ErrorCount": 0,
    "CommitCount": 100,
    "RollbackCount": 0,
    "PSCacheAccessCount": 100,
    "PSCacheHitCount": 99,
    "PSCacheMissCount": 1,
    "StartTransactionCount": 100,
    "TransactionHistogram": [
      55,
      44,
      1,
      0,
      0,
      0,
      0
    ],
    "ConnectionHoldTimeHistogram": [
      53,
      47,
      3,
      0,
      0,
      0,
      0,
      0
    ],
    "RemoveAbandoned": false,
    "ClobOpenCount": 0,
    "BlobOpenCount": 0,
    "KeepAliveCheckCount": 0,
    "KeepAlive": false,
    "FailFast": false,
    "MaxWait": 1234,
    "MaxWaitThreadCount": -1,
    "PoolPreparedStatements": true,
    "MaxPoolPreparedStatementPerConnectionSize": 5,
    "MinEvictableIdleTimeMillis": 30001,
    "MaxEvictableIdleTimeMillis": 25200000,
    "LogDifferentThread": true,
    "RecycleErrorCount": 0,
    "PreparedStatementOpenCount": 1,
    "PreparedStatementClosedCount": 0,
    "UseUnfairLock": true,
    "InitGlobalVariants": false,
    "InitVariants": false
  }
]

Druid监控面板

启用面板

• Druid提供了大量监控数据，监控数据只保存在内存中，重启后会丢失，且无法集中查看
  • 面板中的"重置"按钮，指的是重置监控数据
• 访问地址(在url-pattern配置)：http://localhost:8080/druid
• 可访问页面
  • 首页
  • 数据源
  • JSON API
• IP转发问题
  • Nginx转发
    • 使用Nginx代理转发，原访问IP会被替换成Nginx的IP，所以allow需要设置成Nginx服务器的IP
    • 但是所有访问IP都被替换成Nginx的IP，所有请求都会放行，相当于allow失去了作用，记得使用账号
    • 最佳实践：外网Nginx直接屏蔽druid接口，开发机直接内网访问原始服务器地址，allow设置成开发机IP
  • k8s集群代理
    • k8s集群Ingress的IP是动态的，无法设置成准确IP，可以设置为"192.168.0.0/16,10.0.0.0/8"放行所有IP（同样有安全问题，记得增加账号密码）
    • 最佳实践：不要暴露druid接口，开发机使用"kubectl port-forward"或者"ktctl forward"命令将服务转到本地，再通过localhost访问
  • 其他
    • Nginx中设置"proxy_set_header Host $host;"的解决方案，在目前的最新版"1.2.21"中是无效的

spring:
  datasource:
    druid:
      stat-view-servlet:
        enabled: true           # 启用Druid监控面板
        url-pattern: /druid/*   # 面板路径(默认"/druid/*")（Druid已做了处理，不会被自定义过滤器拦截）
        
        reset-enable: false     # 面板上的重置按钮（禁用后面板上重置按钮依然会显示和互动，但是不起作用） 
        
        login-username: admin   # 账号(默认不需要登录，设置了才需要)
        login-password: 123456  # 密码
        
        allow: 127.0.0.1        # 白名单，多个使用逗号隔开（不在白名单中不能访问，默认开启localhost/127.0.0.1）
        deny: 127.0.0.1         # 黑名单，多个使用逗号隔开
        # 使用Nginx转发导致访问IP丢失，allow、deny均失效，可将allow设置为 192.168.0.0/16,10.0.0.0/8 允许所有请求，注意使用账号保护

SQL监控

• 启用"SQL监控"，未开启则面板无任何数据

spring:
  datasource:
    druid:
      filter:
        stat:
          enabled: true           # 启动SQL语句监控，同时会影响其他面板中的SQL展示（如：影响"URI监控"中的jdbc数据）
          db-type: mysql          # 数据库类型（实测没啥用，会自动根据上面的连接自动识别）

          log-slow-sql: true      # 记录慢日志（"SQL监控"面板中数值会标记为红色）
          slow-sql-millis: 3000   # 慢日志判定标准（单位：毫秒）
          slow-sql-log-level: ERROR # 自定义日志级别，默认日志级别"ERROR"

SQL防火墙

• 检查SQL语句，防止恶意操作
• 同时记录每一次Select/Insert/Update/...等操作

spring:
  datasource:
    druid:
      filter:
        wall:
          enabled: true               # 启用防火墙
          db-type: h2                 # 数据库类型（实测没啥用，会自动根据上面的连接自动识别）
          config:
            delete-allow: false       # 是否允许DELETE操作（报错异常：java.sql.SQLException:sql injection violation, dbType mysql, druid-version 1.2.21, delete not allow : DELETE FROM ...）
            drop-table-allow: false   # 是否允许DROP TABLE操作

Web应用/URI监控/Session监控

• 影响到"Web应用/URI监控/Session监控"面板

spring:
  datasource:
    druid:
      web-stat-filter:
        enabled: true     # 启用"Web应用/URI监控/Session监控"面板
        url-pattern: /*   # 监控这里指定的路径
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,*.html,*.json   # 过滤路径

        # "Session监控"面板设置（RestFul纯后端没有session）
        session-stat-enable: true
        session-stat-max-count: 1000
        principal-session-name: xxc
        principal-cookie-name: xxc
        profile-enable: true

Spring监控

• 会记录该切面下的执行时间，Jdbc执行数据
  • 需要开启安装aop依赖

spring:
  datasource:
    druid:
      aop-patterns: x.y.z.controller.* # Spring监控AOP切入点，多个用逗号分隔

其他

• 官网：druid/druid-spring-boot-starter at master · alibaba/druid
• 所有过滤器（上面配置值包含一部分）
  • StatFilter
  • WallFilter
  • ConfigFilter
  • EncodingConvertFilter
  • Slf4jLogFilter
  • Log4jFilter
  • Log4j2Filter
  • CommonsLogFilter

问题

超时

• 基本

Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure...
...
The last packet successfully received from the server was 11,034 milliseconds ago. The last packet sent successfully to the server was 20,386 milliseconds ago...

LastErrorMessage:
Communications link failure The last packet successfully received from the server was 11,034 milliseconds ago. The last packet sent successfully to the server was 20,386 milliseconds ago.

LastErrorClass:
com.mysql.cj.jdbc.exceptions.CommunicationsException

LastErrorStackTrace:
com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:912)
com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:354)
com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3462)
com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:434)
com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3460)
com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:158)
com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:483)
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
jdk.internal.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.base/java.lang.reflect.Method.invoke(Method.java:568)
org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
jdk.proxy2/jdk.proxy2.$Proxy170.query(Unknown Source)
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:81)
org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
jdk.proxy2/jdk.proxy2.$Proxy169.query(Unknown Source)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
...

spring:
  datasource:
    druid:
      socket-timeout: 60000 # 连接超时时间，60000毫秒(1分钟)