springboot+mybatis+druid的配置(sql监控平台)

1.依赖

<!-- mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <!-- druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

2.配置yml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/newsell?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
     #把默认数据源切换成druid
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      db-type: mysql
      initial-size: 10 # 初始化连接数
      min-idle: 10
      max-active: 20 # 最大连接数
      max-wait: 60000 # 连接超时时间
      filter:
        stat:
          enabled: true
          log-slow-sql: true  # 开启mansql监控
          slow-sql-millis: 2000 # 超过2ms就是慢sql 
          merge-sql: false
      break-after-acquire-failure: true  # 开启重连机制
      connection-error-retry-attempts: 2 # 尝试重连两次

mybatis:
  mapper-locations: classpath:mapper/*.xml
  configuration:
    # 打印语句
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 自动转换驼峰
    map-underscore-to-camel-case: true

3.druid配置类

package com.zykj.newsell.config;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * druid配置类
 *
 * @author lc
 * @version 1.0
 * @date 2022/4/20 10:20
 */
@Configuration
public class DruidConfig {
    private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);
    @Bean
    public DataSource createDruidDataSource(){
        DataSource dataSource= DruidDataSourceBuilder.create().build();
        return dataSource;
    }

    @Bean
    public ServletRegistrationBean druidServlet(){

        logger.info("init Druid Servlet Configuration ");
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();
        // 设置监控页面的登录名和密码以及连接ip
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认就是允许所有访问
        //initParams.put("deny","黑名单的ip");
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能
        initParams.put("resetEnable", "false");

        bean.setInitParameters(initParams);
        return bean;
    }

    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean(new WebStatFilter());
        bean.setFilter(new WebStatFilter());
        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return  bean;
    }

}

4.验证druid的监控网页

http://localhost:8326/druid/login.html

输入配置类中配置的密码

5. 配合springsecurity的情况

要关闭csrf或者开启csrf但是对druid忽略，而且/druid/**的路径忽略不验证-----修改security的配置类

 // 认证的处理
    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
                .csrf().disable()
               // .csrf().ignoringAntMatchers("/druid/*").and()
                .sessionManagement().sessionCreationPolicy(SessionCreationPolicy.STATELESS)
                .and()
                .authorizeRequests()
                .antMatchers("/user/register", "/user/login", "/user/logout", "/user/logout",
                        "/three/total/getQHLToken", "/user/set/pw",

                        "/druid/**",

                        "/doc.html/**", "/swagger-ui.html/**", "/webjars/**",
                        "/v2/api-docs",//swagger api json
                        "/swagger-resources/configuration/ui",//用来获取支持的动作
                        "/swagger-resources",//用来获取api-docs的URI
                        "/swagger-resources/configuration/security",//安全选项
                        "/swagger-ui.html").anonymous()    // 忽视的路径
                .anyRequest().authenticated();

        // 添加token过滤器
        http.addFilterBefore(jwtAuthenticationTokenFilter, UsernamePasswordAuthenticationFilter.class);
        //允许跨域
        http.cors();
    }

6.注意事项

进入druid的web监控页面

6.1 数据源报(*) property for user to setup

第一次你会发现报数据源那一栏报(*) property for user to setup，这是因为你的代码没有调用过sql，调用一下就好了

6.2 数据源的filter类名是空

这种情况就会导致SQL监控那一栏就不会有数据，修改一下配置文件

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/newsell?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    druid:
      db-type: mysql
      initial-size: 3 # 初始化连接数
      max-active: 8 # 最大连接数
      max-wait: 60000 # 连接超时时间
      filter:
        stat:
          enabled: true
          log-slow-sql: true
          slow-sql-millis: 1000
          merge-sql: false

正常的应该是这样的