标签：java true spring druid Druid SpringBoot3 使用 com datasource
SpringBoot3.x使用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
- 可访问页面
- 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监控
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监控
spring:
datasource:
druid:
aop-patterns: x.y.z.controller.* # Spring监控AOP切入点，多个用逗号分隔
其他
问题
超时
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分钟)
标签：java,
true,
spring,
druid,
Druid,
SpringBoot3,
使用,
com,
datasource
From： https://www.cnblogs.com/XuXiaoCong/p/18064633