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分钟)

SpringBoot3.x使用Druid

SpringBoot3.x使用Druid

起步

安装

基础连接信息

Druid监控面板

启用面板

SQL监控

SQL防火墙

Web应用/URI监控/Session监控

Spring监控

其他

问题

超时

言午日尧耳总

引用和评论

LangChain接入本地/国产大模型

AES 加密和解密demo

Thymeleaf的使用

Spring Boot起步，CRUD、错误处理与宝塔部署

实现 Angular 程序处理进度条

Spring boot连接Minio实现文件的上传以及下载

记录下解决自定义 Feign 调用，响应content-type 格式不正确导致的解析错误