alibaba的druid连接池的监控的两种场景
一、导入druid包,适用spring、springboot
1、导包(版本自选)
这里省略了数据库的其他包只关注druid的相关
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.5</version>
</dependency>
2、配置yml
spring:
datasource:
url: jdbc:mysql://localhost:3306/mysql?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
username: root
password: 123456
#连接池的配置信息
initialSize: 10
minIdle: 10
maxActive: 100
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
type: com.alibaba.druid.pool.DruidDataSource
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
driver-class-name: com.mysql.cj.jdbc.Driver
3、写个配置类
@Configuration
public class DruidConfig {
private final String USERNAME = "cc";
private final String PASSWORD = "111111";
@Value("${spring.datasource.url:#{null}}")
private String dbUrl;
@Value("${spring.datasource.username: #{null}}")
private String username;
@Value("${spring.datasource.password:#{null}}")
private String password;
@Value("${spring.datasource.driverClassName:#{null}}")
private String driverClassName;
@Value("${spring.datasource.initialSize:#{null}}")
private Integer initialSize;
@Value("${spring.datasource.minIdle:#{null}}")
private Integer minIdle;
@Value("${spring.datasource.maxActive:#{null}}")
private Integer maxActive;
@Value("${spring.datasource.maxWait:#{null}}")
private Integer maxWait;
@Value("${spring.datasource.timeBetweenEvictionRunsMillis:#{null}}")
private Integer timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.minEvictableIdleTimeMillis:#{null}}")
private Integer minEvictableIdleTimeMillis;
@Value("${spring.datasource.validationQuery:#{null}}")
private String validationQuery;
@Value("${spring.datasource.testWhileIdle:#{null}}")
private Boolean testWhileIdle;
@Value("${spring.datasource.testOnBorrow:#{null}}")
private Boolean testOnBorrow;
@Value("${spring.datasource.testOnReturn:#{null}}")
private Boolean testOnReturn;
@Value("${spring.datasource.poolPreparedStatements:#{null}}")
private Boolean poolPreparedStatements;
@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize:#{null}}")
private Integer maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters:#{null}}")
private String filters;
@Value("{spring.datasource.connectionProperties:#{null}}")
private String connectionProperties;
@Bean
@Primary
public DruidDataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
//configuration
if (initialSize != null) {
datasource.setInitialSize(initialSize);
}
if (minIdle != null) {
datasource.setMinIdle(minIdle);
}
if (maxActive != null) {
datasource.setMaxActive(maxActive);
}
if (maxWait != null) {
datasource.setMaxWait(maxWait);
}
if (timeBetweenEvictionRunsMillis != null) {
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
}
if (minEvictableIdleTimeMillis != null) {
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
}
if (validationQuery != null) {
datasource.setValidationQuery(validationQuery);
}
if (testWhileIdle != null) {
datasource.setTestWhileIdle(testWhileIdle);
}
if (testOnBorrow != null) {
datasource.setTestOnBorrow(testOnBorrow);
}
if (testOnReturn != null) {
datasource.setTestOnReturn(testOnReturn);
}
if (poolPreparedStatements != null) {
datasource.setPoolPreparedStatements(poolPreparedStatements);
}
if (maxPoolPreparedStatementPerConnectionSize != null) { datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
}
if (connectionProperties != null) {
datasource.setConnectionProperties(connectionProperties);
}
List<Filter> filters = new ArrayList<>();
filters.add(statFilter());
filters.add(wallFilter());
datasource.setProxyFilters(filters);
return datasource;
}
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
servletRegistrationBean.setServlet(new StatViewServlet());
servletRegistrationBean.addUrlMappings("/druid/*");
Map<String, String> initParameters = new HashMap<>();
initParameters.put("resetEnable", "false"); //禁用HTML页面上的“Rest All”功能
//initParameters.put("allow", "10.8.9.115"); //ip白名单(没有配置或者为空,则允许所有访问)
initParameters.put("loginUsername", USERNAME); //++监控页面登录用户名
initParameters.put("loginPassword", PASSWORD); //++监控页面登录用户密码
//initParameters.put("deny", ""); //ip黑名单
//如果某个ip同时存在,deny优先于allow
servletRegistrationBean.setInitParameters(initParameters);
return servletRegistrationBean;
}
@Bean
public StatFilter statFilter() {
StatFilter statFilter = new StatFilter();
statFilter.setLogSlowSql(true);
statFilter.setMergeSql(true);
statFilter.setSlowSqlMillis(3000);
return statFilter;
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
//允许执行多条SQL
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);
wallFilter.setConfig(config);
return wallFilter;
}
}
4、访问
http://localhost:9000/druid 就可以打开druid连接池的登录页面,账号密码在配置类中
二、导入druid-spring-boot-starter包,适用springboot
1、导入包(版本自选)
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.6</version>
</dependency>
2、配置yml文件
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mysql?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
username: root
password: 123456
positionSlot: 1
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
#Oracle需要打开注释
#validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
#是否允许访问控制台,为false的话就不能访问控制台了
enabled: true
url-pattern: /druid/*
# IP白名单(没有配置或者为空,则允许所有访问)
# allow: 127.0.0.1,192.168.46.120
# IP黑名单 (存在共同时,deny优先于allow)
# deny: 192.168.46.121
# 禁用HTML页面上的“Reset All”功能
reset-enable: false
# 登录的账号密码
login-username: admin
login-password: admin
# 这句必须配, 否则通过网关访问不通
allow: ""
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: false
enabled: true
wall:
enabled: true
config:
multi-statement-allow: true
connection-properties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
web-stat-filter:
enabled: true
url-pattern: "/*"
exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
3、运行访问结果
访问:http://localhost:8080/druid/,登录名:admin,密码 admin
4、spring监控页没有数据解决方案
访问之后spring监控默认是没有数据的,但需要导入SprngBoot的AOP的Starter
①导包
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
②配置
spring.datasource.druid.aop-patterns: com.huaweicloud.controller.*
配置的值是指向的类名