【原创】druid控制台快速实践 & 无法监控到sql问题_druiddatasource设置到sqlsessionfactorybean后没有sql监控

1.监控台集成编码

@EnableTransactionManagement
@Configuration
@Slf4j
@EnableConfigurationProperties(MybatisPlusProperties.class)
public class MybatisPlusConfig {

    @Value("${spring.datasource.druid.username:root}")
    private String druidAccount;

    @Value("${spring.datasource.druid.password:root}")
    private String druidPassword;

    /**
     * 如需调整 {@link com.baomidou.mybatisplus.autoconfigure.MybatisPlusAutoConfiguration#sqlSessionFactory(DataSource)}
     */
    @Bean("sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(MybatisPlusProperties properties,  ApplicationContext applicationContext) throws Exception {···}

    /**
     * 注册一个StatViewServlet,进行druid监控页面配置
     * @return servlet registration bean
     */
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.console", havingValue = "enabled")
    public ServletRegistrationBean druidStatViewServlet() {
        // 先配置管理后台的servLet，访问的入口为/druid/
        ServletRegistrationBean servletRegistrationBean =
                new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        servletRegistrationBean.addInitParameter("loginUsername", druidAccount);
        servletRegistrationBean.addInitParameter("loginPassword", druidPassword);
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * 注册一个过滤器，允许页面正常浏览
     * @return filter registration bean
     */
    @Bean
    @ConditionalOnProperty(name = "spring.datasource.druid.console", havingValue = "enabled")
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        // 添加过滤规则.
        filterRegistrationBean.addUrlPatterns("/*");
        // 添加不需要忽略的格式信息.
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

2.数据源连接池服务配置

spring:
...............................................................................................
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource  #使用Druid连接池
    url: jdbc:mysql://xxxx:3307/idaas?serverTimezone=GMT%2B8&useUnicode=true&&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true
    username: root
    password: xxxx
    initial-size: 3
    max-active: 100
    min-idle: 5
    max-wait: 12000
    pool-prepared-statements: true
    validation-query: select 1 from dual
    filters: mergeStat
    useGlobalDataSourceStat: true
    druid:
      username: admin123
      password: admin123
      console: enabled
...............................................................................................
sw:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource  #使用Druid连接池
    url: jdbc:mysql://xxxx:3306/kiam?serverTimezone=GMT%2B8&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&allowMultiQueries=true
    username: root
    password: xxxx
    initial-size: 3
    max-active: 100
    min-idle: 5
    max-wait: 12000
    pool-prepared-statements: true
    validation-query: select 1 from dual
    filters: mergeStat
    useGlobalDataSourceStat: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

配置解释：

# 数据库
spring:
  datasource:
    MyDb1:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://数据库ip:3306/数据库名
      username: 用户名
      password: 密码
    MyDb2:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.jdbc.Driver
      url: jdbc:mysql://数据库ip:3306/数据库名
      username: 用户名
      password: 密码
    druid:
      #最大活跃数
      maxActive: 20
      #初始化数量
      initialSize: 1
      #最大连接等待超时时间
      maxWait: 60000
      #打开PSCache，并且指定每个连接PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      #通过connectionProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      minIdle: 1
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1 from dual
      validationQuery1: select 1
      # 注意 有的数据库不支持select 1 from dual 在配置处需要修改为下方的select 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      #配置监控统计拦截的filters，去掉后监控界面sql将无法统计,'wall'用于防火墙
      filters: stat, wall, log4j
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

3.数据源连接池控制台

• 地址：http://X.X.X.X:8090/v5/druid/login.html
• 界面：
  

4.druid控制台无法监控到sql

• 原因：filter类名为空，缺少filters，貌似没有注册进

4.1 解决方案：

1. 修改jvm启动参数：

   -Ddruid.filters=mergeStat -Ddruid.useGlobalDataSourceStat=true，
   1

   转自：Druid常见问题

2. 增加配置参数

   druid:
     filters: mergeStat     # 配置监控统计拦截的filters，去掉后监控界面sql将无法统计,'wall'用于防火墙
     useGlobalDataSourceStat: true       # 合并多个DruidDataSource的监控数据
   1
   2
   3

3. 关联学习：springboot项目配置多数据库连接