current position:Home>Springboot -- integrating druid and mybatisplus to realize SQL monitoring

Springboot -- integrating druid and mybatisplus to realize SQL monitoring

2022-06-24 09:20:10Focus on writing bugs

Preface

stay Springboot In project application , Usually when connecting to the database , Meeting direct / indirect The introduction of a org.springframework.boot spring-boot-starter-jdbc rely on pom . By default HikariCP Database connection pool .

although HikariCP have Efficient Besides the characteristics of , And Druid comparison , There are still many shortcomings .

Druid Characteristics of connection pool

Druid Database connection pool , In addition to configuring the database connection thread pool , It can be done SQL Operation monitoring and other extended functions .

Highlights of its various configurations :

  • stat:Druid Built in provides a StatFilter, Used for statistical monitoring information .
  • wall:Druid defense SQL Inject the attack WallFilter It is through Druid Of SQL Parser analysis .

    Druid Provided SQL Parser Can be in JDBC Layer interception SQL Do something about it , For example, sub database and sub table 、 Audit, etc .

  • log4j2: This is Logging function , You can put sql Statement printed to log4j2 For troubleshooting .

Project testing and configuration

Next, we will single Springboot project , Integrate DruidMybatisPlus And so on .

Test Framework version

  • Springboot 2.1.4.RELEASE
  • Druid 1.1.23
  • Mybatis-plus 3.4.0

Depend on the introduction of

According to the above framework requirements , Select the corresponding version of pom Rely on files for configuration .

The main dependent information is as follows :

<!--  alibaba.com druid data source  -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.23</version>
</dependency>
<!-- mysql8  drive -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.20</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
    <exclusions>
        <!--  Exclude the default  HikariCP  data source  -->
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<!-- mybatis, Introduced  SpringBoot Of  JDBC  modular ,  therefore , The default is to use  hikari  As a data source  -->
<!--<dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> <exclusions> <exclusion> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> </exclusion> </exclusions> </dependency>-->

【 Be careful 】 Why remove HikariCP ?

because Springboot In project application , Usually when connecting to the database , Meeting direct / indirect The introduction of a org.springframework.boot spring-boot-starter-jdbc rely on pom, among The default connection pool is HikariCP .
Use Druid Connection pool , You need to remove the other default connection pools !

To configure Druid Connection pool

Main configuration database connection information 、 Monitoring information, etc . Its configuration application.yml As shown below :

spring:
  datasource:
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver   # mysql8  The connection driver of 
    url: jdbc:mysql://106.55.137.66:3306/mybatis_plus?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
    platform: mysql #  Database type 
    type: com.alibaba.druid.pool.DruidDataSource  #  Specify the data source type 
    ###  To configure  druid  Database connection pool 
    druid:
      #  Configure initialization size 、 Minimum 、 Maximum 
      initial-size: 5
      minIdle: 10
      max-active: 20
      #  Configure the timeout time for getting connection waiting ( Company : millisecond )
      max-wait: 60000
      #  Configure how often to test , Detects idle connections that need to be closed , In milliseconds 
      time-between-eviction-runs-millis: 2000
      #  Configure the minimum lifetime of a connection in the pool , In milliseconds 
      min-evictable-idle-time-millis: 600000
      max-evictable-idle-time-millis: 900000
      #  Used to test whether the connection is available SQL sentence , The default value is different for each database , This is a mysql
      validationQuery: select 1
      #  The application requests a connection from the connection pool , also testOnBorrow by false when , The connection pool will determine if the connection is idle , If it is , Verify that the connection is available 
      testWhileIdle: true
      #  If true, The default is false, When an application requests a connection from the connection pool , The connection pool will determine whether the connection is available 
      testOnBorrow: false
      #  If true( Default false), When the application is finished using the connection , When the connection pool reclaims the connection, it will judge whether the connection is still available 
      testOnReturn: false
      #  Whether the cache preparedStatement, That is to say PSCache.PSCache Great improvement in database performance supporting cursors , for instance oracle
      poolPreparedStatements: true
      #  To enable the PSCache, Must be configured greater than 0, When more than 0 when , poolPreparedStatements Auto trigger changed to true,
      #  stay Druid in , No existence Oracle Next PSCache The problem of using too much memory ,
      #  You can configure this value to be larger , for instance 100
      maxOpenPreparedStatements: 20
      #  Connect... In the pool minIdle No more connections , Idle time exceeds minEvictableIdleTimeMillis, Will perform keepAlive operation 
      keepAlive: true
      # Spring  monitor , utilize aop  Execution time of the specified interface ,jdbc Count and record 
      aop-patterns: "cn.xj.dao.*"
      ###########  Enable the built-in filter ( first  stat must , Otherwise we can't monitor SQL)##########
      filters: stat,wall,log4j2
      #  Configure your own monitoring, statistics and interception filter
      filter:
        #  Turn on druiddatasource Status monitoring of 
        stat:
          enabled: true
          db-type: mysql
          #  Slow opening sql monitor , exceed 5s  Think it's slow sql, Record it in the journal 
          log-slow-sql: true
          slow-sql-millis: 5000
        #  Log monitoring , Use slf4j  Log output 
        slf4j:
          enabled: true
          statement-log-error-enabled: true
          statement-create-after-log-enabled: false
          statement-close-after-log-enabled: false
          result-set-open-after-log-enabled: false
          result-set-close-after-log-enabled: false
      ##########  To configure WebStatFilter, Used to collect web Associated monitoring data  ##########
      web-stat-filter:
        enabled: true                   #  start-up  StatFilter
        url-pattern: /*                 #  Filter all url
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" #  Rule out unnecessary url
        session-stat-enable: true       #  Turn on session Statistical function 
        session-stat-max-count: 1000    # session Maximum number of , Default 100
      ##########  To configure StatViewServlet( Monitoring the page ), For display Druid Statistical information  ##########
      stat-view-servlet:
        enabled: true                   #  Enable StatViewServlet
        url-pattern: /druid/*           #  The path to the built-in monitoring page , The front page of the built-in monitoring page is /druid/index.html
        reset-enable: false              #  It is not allowed to empty Statistics , Recalculate 
        
        login-username: root            #  Configure the monitoring page to access the account and password 
        login-password: 123             # druid  Of web The interface is  /druid/login.html
        
        allow: 127.0.0.1           #  Address allowed to access , If allow Not configured or empty , All access is allowed 
        deny:                                        #  Access denied address ,deny Prior to the allow, If in deny In the list , Even in allow In the list , And they'll be rejected 

mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

To configure mybatisPlus

About creating database tables and classes , There is no repetition here , You can refer to the following article :

Mybatis-Plus Just read this one

About druid Of web Interface go AD To configure

When visiting the monitoring page , You may be at the bottom of the page (footer) See Alibaba's advertisement
reason : Introduced druid Of jar In bag common.js( There is a passage in it js The code is for the page footer Additional advertising )

If you need to remove , You can add the following configuration classes :

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.condition.ConditionalOnWebApplication;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.servlet.*;
import java.io.IOException;

@Configuration
@ConditionalOnWebApplication
@AutoConfigureAfter(DruidDataSourceAutoConfigure.class)
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled",
        havingValue = "true", matchIfMissing = true)
public class RemoveDruidAdConfig {
    

    /** *  Method name : removeDruidAdFilterRegistrationBean *  Methods described   Remove the ads at the bottom of the page  * @param properties com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties * @return org.springframework.boot.web.servlet.FilterRegistrationBean */
    @Bean
    public FilterRegistrationBean removeDruidAdFilterRegistrationBean(DruidStatProperties properties) {
    

        //  obtain web Monitor the parameters of the page 
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        //  extract common.js Configuration path 
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");

        final String filePath = "support/http/resources/js/common.js";

        // establish filter To filter 
        Filter filter = new Filter() {
    
            @Override
            public void init(FilterConfig filterConfig) throws ServletException {
    }

            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
    
                chain.doFilter(request, response);
                //  Reset buffer , The response header will not be reset 
                response.resetBuffer();
                //  obtain common.js
                String text = Utils.readFromResource(filePath);
                //  Regular substitution banner,  Remove the advertising message at the bottom 
                text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }

            @Override
            public void destroy() {
    }
        };

        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

Reference

Get along well with SpringBoot Monitoring statistics

copyright notice
author[Focus on writing bugs],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/175/202206240804392838.html

Random recommended