Druid简单应用
一、Druid基本配置
1、基于Spring配置文件的方式
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: Asia/Shanghai
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/customer_ticket?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
initial-size: 5 # 初始化连接池大小
min-idle: 3 # 最小维持的连接数大小
max-active: 40 # 最大活跃连接数
max-wait: 60000 # 最长等待时间
time-between-eviction-runs-millis: 6000 # 关闭空闲连接间隔（毫秒）
min-evictable-idle-time-millis: 30000 # 连接最小存活时间
validation-query: select 1 from dual # 验证连接存活sql
test-while-idle: true # 判断连接是否可用
test-on-borrow: false # 在获取连接前验证连接是否可用
test-on-return: false # 在归还连接前验证连接是否可用
pool-prepared-statements: false # 是否缓存PSTMT
max-pool-prepared-statement-per-connection-size: 20 # 配置 PSTMT 缓存个数
2、基于Bean的方式
首先是配置相关参数
spring:
lcl:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/customer_ticket?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
initial-size: 5 # 初始化连接池大小
min-idle: 3 # 最小维持的连接数大小
max-active: 40 # 最大活跃连接数
max-wait: 60000 # 最长等待时间
time-between-eviction-runs-millis: 6000 # 关闭空闲连接间隔（毫秒）
min-evictable-idle-time-millis: 30000 # 连接最小存活时间
validation-query: select 1 from dual # 验证连接存活sql
validation-query-timeout: 1000 # 验证连接存活超时时间
test-while-idle: true # 判断连接是否可用
test-on-borrow: false # 在获取连接前验证连接是否可用
test-on-return: false # 在归还连接前验证连接是否可用
pool-prepared-statements: false # 是否缓存PSTMT
max-pool-prepared-statement-per-connection-size: 20 # 配置 PSTMT 缓存个数
手动设置DataSource
@Configuration
public class DruidDataSourceConfigration {
@Bean("dataSource")
public DataSource getDatasource(
@Value("${spring.lcl.datasource.druid.driver-class-name}")
String driverClassName,
@Value("${spring.lcl.datasource.druid.url}")
String url,
@Value("${spring.lcl.datasource.druid.username}")
String userName,
@Value("${spring.lcl.datasource.druid.password}")
String password,
@Value("${spring.lcl.datasource.druid.initial-size}")
int initialSize,
@Value("${spring.lcl.datasource.druid.min-idle}")
int minIdle,
@Value("${spring.lcl.datasource.druid.max-active}")
int maxActive,
@Value("${spring.lcl.datasource.druid.max-wait}")
int maxWait,
@Value("${spring.lcl.datasource.druid.time-between-eviction-runs-millis}")
int timeBetweenEvictionRunsMillis,
@Value("${spring.lcl.datasource.druid.min-evictable-idle-time-millis}")
int minEvictableIdleTimeMillis,
@Value("${spring.lcl.datasource.druid.validation-query}")
String validationQuery,
@Value("${spring.lcl.datasource.druid.validation-query-timeout}")
int validationQueryTimeout,
@Value("${spring.lcl.datasource.druid.test-while-idle}")
boolean testWhileIdle,
@Value("${spring.lcl.datasource.druid.test-on-borrow}")
boolean testOnBorrow,
@Value("${spring.lcl.datasource.druid.test-on-return}")
boolean testOnReturn,
@Value("${spring.lcl.datasource.druid.pool-prepared-statements}")
boolean poolPreparedStatements,
@Value("${spring.lcl.datasource.druid.max-pool-prepared-statement-per-connection-size}")
){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(password);
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setValidationQueryTimeout(validationQueryTimeout);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
return dataSource;
}
}
二、Druid监控界面
1、基于Yaml配置
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
...
filters: stat
# 配置DruidStatViewServlet
stat-view-servlet:
enabled: true
url-pattern: '/druid/*'
login-username: admin
login-password: admin
2、基于Bean配置
在spring.lcl.datasource中也添加相关参数
@Configuration
public class DruidMonitorConfiguration {
/**
* 开启监控
* @return
*/
@Bean("druidStatViewServlet")
public ServletRegistrationBean<StatViewServlet> getDruidStatViewServlet(
@Value("${spring.lcl.datasource.druid.stat-view-servlet.login-username}")
String username,
@Value("${spring.lcl.datasource.druid.stat-view-servlet.login-password}")
String password,
@Value("${spring.lcl.datasource.druid.stat-view-servlet.enabled}")
String resetEnable
){
ServletRegistrationBean<StatViewServlet> registrationBean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_ALLOW, "127.0.0.1"); // 白名单
registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_DENY, ""); // 黑名单
registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_USERNAME, username); // 用户名
registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_PASSWORD, password); // 密码
registrationBean.addInitParameter(StatViewServlet.PARAM_NAME_RESET_ENABLE, resetEnable); // 允许重置
return registrationBean;
}
3、控制台
三、Web访问监控
1、Yaml配置
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
...
web-stat-filter:
enabled: true
url-pattern: '/*'
exclusions: '*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*'
2、Bean配置
在spring.lcl.datasource中也添加相关参数
/**
* url 监控
* @param webStatFilter
* @return
*/
@Bean
@DependsOn("webStatFilter")
public FilterRegistrationBean<WebStatFilter> getDruidWebStatFilter(
WebStatFilter webStatFilter,
@Value("${spring.lcl.datasource.druid.web-stat-filter.url-pattern}")
String UrlPatterns,
@Value("${spring.lcl.datasource.druid.web-stat-filter.exclusions}")
String exclusions){
FilterRegistrationBean<WebStatFilter> registrationBean = new FilterRegistrationBean<>(webStatFilter);
registrationBean.addUrlPatterns("/*"); // 对所有的路径都进行监控配置
registrationBean.addInitParameter(WebStatFilter.PARAM_NAME_EXCLUSIONS, "*.js,*.gif,*.jpg,*.bmp,*.css,*.ico,/druid/*"); // 路径排除
return registrationBean;
}
@Bean("webStatFilter")
public WebStatFilter getWebStatFilter(
@Value("${spring.lcl.datasource.druid.web-stat-filter.enabled}")
boolean enabled
){
// 获取 web 状态过滤
WebStatFilter webStatFilter = new WebStatFilter();
// 对 session 状态进行过滤
webStatFilter.setSessionStatEnable(enabled);
return webStatFilter;
}
3、控制台
当请求多次时，查看控制台的URI监控，即可看到对请求的监控信息，包括请求路径、请求次数、请求时间总和、请求最慢耗时、最大并发、区间分布等等
区间分布从前到后分别表示：0-1毫秒次数、1-10毫秒次数、10-100毫秒次数、100-1000毫秒次数、1-10秒次数、10-100秒次数、100-1000秒次数、1000秒以上次数，从而可以查看接口的响应分布。
除了可以查看URI的监控外，还可以查看Session监控，这样就可以查看有多少用户访问
四、SQL监控
1、Yml配置
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
...
filters: stat
filter:
stat:
log-slow-sql: true
merge-sql: true
slow-sql-millis: 1000
2、Bean配置
首先配置sql监控
/**
* 定义sql监控
* @return
*/
@Bean("sqlStatFilter")
public StatFilter getSqlStatFilter(
@Value("${spring.lcl.datasource.druid.filter.stat.merge-sql}")
boolean mergeSql,
@Value("${spring.lcl.datasource.druid.filter.stat.log-slow-sql}")
boolean logSlowSql,
@Value("${spring.lcl.datasource.druid.filter.stat.slow-sql-millis}")
int slowSqlMillis
){
StatFilter statFilter = new StatFilter();
statFilter.setMergeSql(mergeSql); // 是否要合并统计
statFilter.setLogSlowSql(logSlowSql); // 慢sql统计
statFilter.setSlowSqlMillis(slowSqlMillis); // 慢sql执行时间（毫秒）
return statFilter;
}
然后将filter集合与dataSource整合
@Configuration
public class DruidDataSourceConfigration {
@Bean("dataSource")
public DataSource getDatasource(
@Value("${spring.lcl.datasource.druid.driver-class-name}")
String driverClassName,
......
int maxPoolPreparedStatementPerConnectionSize,
@Autowired StatFilter sqlStatFilter
){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
......
List<Filter> filterList = new ArrayList<>();
filterList.add(sqlStatFilter);
dataSource.setProxyFilters(filterList);
return dataSource;
}
3、控制台
五、SQL 防火墙
1、yaml配置
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
......
filters: stat,wall
filter:
stat:
log-slow-sql: true
merge-sql: true
slow-sql-millis: 1000
wall:
config:
multi-statement-allow: true
delete-allow: false
2、Bean配置
首先创建WallFilter
@Bean("wallConfig")
public WallConfig getWallConfig(
@Value("${spring.lcl.datasource.druid.filter.wall.config.multi-statement-allow}")
String multiStatementAllow,
@Value("${spring.lcl.datasource.druid.filter.wall.config.delete-allow}")
String deleteAllow
){
WallConfig wallConfig = new WallConfig();
wallConfig.setMultiStatementAllow(multiStatementAllow); // 是否允许并行多个statement操作（批处理）
wallConfig.setDeleteAllow(deleteAllow); // 是否允许执行删除
return wallConfig;
}
@Bean("wallfFilter")
public WallFilter getWallFilter(WallConfig wallConfig){
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig);
return wallFilter;
}
然后将filter集合与dataSource整合
package com.lcl.galaxy.lcl.galaxy.druid.config;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
@Configuration
public class DruidDataSourceConfigration {
@Bean("dataSource")
public DataSource getDatasource(
......
@Autowired StatFilter sqlStatFilter,
@Autowired WallFilter wallFilter
){
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClassName);
......
List<Filter> filterList = new ArrayList<>();
filterList.add(sqlStatFilter);
filterList.add(wallFilter);
dataSource.setProxyFilters(filterList);
return dataSource;
}
}
3、控制台
六、Spring 监控
1、引入AOP包并开启AOP
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
2、配置AOP
@Configuration
public class DruidSpringConfig {
/**
* 创建Druid连接器
* @return
*/
@Bean("druidStatInterceptor")
public DruidStatInterceptor getDruidStatInterceptor(){
DruidStatInterceptor druidStatInterceptor = new DruidStatInterceptor();
return druidStatInterceptor;
}
/**
* 获取切面
* @return
*/
@Bean("jdkRegexpMethodPointcut")
public JdkRegexpMethodPointcut getJdkRegexpMethodPointcut(){
JdkRegexpMethodPointcut jdkRegexpMethodPointcut = new JdkRegexpMethodPointcut();
jdkRegexpMethodPointcut.setPatterns("com.lcl.galaxy.lcl.galaxy.druid.dao.*","com.lcl.galaxy.lcl.galaxy.druid.apis.*","com.lcl.galaxy.lcl.galaxy.druid.service.*");
//jdkRegexpMethodPointcut.setPattern("com.lcl.galaxy.lcl.galaxy.druid..*");
return jdkRegexpMethodPointcut;
}
/**
* 使用AOP模式实现切面
* @param druidStatInterceptor
* @param pointcut
* @return
*/
@Bean("druidSpringStatAdvisor")
public DefaultPointcutAdvisor getDruidSpringStatAdvisor(DruidStatInterceptor druidStatInterceptor, JdkRegexpMethodPointcut pointcut){
DefaultPointcutAdvisor defaultPointcutAdvisor = new DefaultPointcutAdvisor();
defaultPointcutAdvisor.setPointcut(pointcut);
defaultPointcutAdvisor.setAdvice(druidStatInterceptor);
return defaultPointcutAdvisor;
}
}
3、控制台
七、Druid日志记录
1、yaml
spring:
datasource:
druid:
......
filters: stat,wall,log4j2
filter:
stat:
log-slow-sql: true
merge-sql: true
slow-sql-millis: 1000
wall:
config:
multi-statement-allow: true
delete-allow: false
log4j2:
enabled: true
statement-executable-sql-log-enable: true
2、Bean配置
配置LogFilter
@Configuration
public class DruidLogConfig {
@Bean("logFilter")
public LogFilter getLogFilter(){
Slf4jLogFilter logFilter = new Slf4jLogFilter();
logFilter.setDataSourceLogEnabled(true); // 启用数据库的日志
logFilter.setStatementExecutableSqlLogEnable(true); // 记录执行日志
return logFilter;
}
}
将LogFilter添加到过滤器中
@Configuration
public class DruidDataSourceConfigration {
@Bean("dataSource")
public DataSource getDatasource(
......
@Autowired StatFilter sqlStatFilter,
@Autowired WallFilter wallFilter,
@Autowired LogFilter logFilter
){
DruidDataSource dataSource = new DruidDataSource();
......
List<Filter> filterList = new ArrayList<>();
filterList.add(sqlStatFilter);
filterList.add(wallFilter);
filterList.add(logFilter);
dataSource.setProxyFilters(filterList);
return dataSource;
}
}
为了演示，可以将慢sql的时间改为1毫秒
3、控制台
可以在控制台看到告警信息
4、日志输出
可以在日志中看到ERROR级别的慢sql告警信息
2023-12-11 00:28:42.091 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 164 millis. select 1 from dual[]
2023-12-11 00:28:42.103 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select 1 from dual[]
2023-12-11 00:28:42.114 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select 1 from dual[]
2023-12-11 00:28:42.128 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select 1 from dual[]
2023-12-11 00:28:42.139 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select 1 from dual[]
2023-12-11 00:28:42.143 INFO 16592 --- [nio-8080-exec-3] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2023-12-11 00:28:42.159 ERROR 16592 --- [nio-8080-exec-3] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select
id, order_id, vender_id
from order_info_new[]
2023-12-11 00:28:42.166 INFO 16592 --- [nio-8080-exec-3] c.l.g.l.g.druid.service.OrderService : 查询结果为===[1-1-1, 2-1-2]
2023-12-11 00:28:44.812 ERROR 16592 --- [nio-8080-exec-4] c.alibaba.druid.filter.stat.StatFilter : slow sql 1 millis. select
id, order_id, vender_id
from order_info_new[]
2023-12-11 00:28:44.813 INFO 16592 --- [nio-8080-exec-4] c.l.g.l.g.druid.service.OrderService : 查询结果为===[1-1-1, 2-1-2]
------------------------------------------------------------------
-----------------------------------------------------------
---------------------------------------------
朦胧的夜 留笔~~
-----------------------------------------------------------
---------------------------------------------
朦胧的夜 留笔~~
