Configure Druid

The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one. It releases the database connection whose idle time exceeds the maximum idle time to avoid database connection omission caused by not releasing the database connection. The performance of database operation can be significantly improved through database connection pool.

Spring Boot provides several available connection pools by default. The default data source is org apache. tomcat. jdbc. pool. DataSource. Druid is an open source connection pool provided by Alibaba. In addition to the connection pool, Druid also provides excellent database monitoring and expansion functions.

Druid is an open source JDBC application component of Alibaba. It mainly includes three parts:

• DruidDriver: proxy Driver, which can provide plug-in system based on Filter Chain mode.

• DruidDataSource: efficient and manageable database connection pool.

• SQLParser: practical SQL parsing.

  Through Druid connection pool middleware, you can:

• Monitor database access performance. Druid has built-in a powerful StatFilter plug-in, which can make detailed statistics on the execution performance of SQL, which is helpful for online analysis of database access performance.

• Replace the traditional DBCP and C3P0 connection pool middleware. Druid provides an efficient, powerful and scalable database connection pool. Database password encryption. Writing the database password directly in the configuration file is easy to lead to security problems. Both DruidDriver and DruidDataSource support PasswordCallback.

• SQL execution log. Druid provides different logfilters, which can support common logging, Log4j and JdkLog. You can select the corresponding LogFilter as needed to monitor the database access of your application. Extend JDBC. If you have programming requirements for the JDBC layer, you can easily write extensions for the JDBC layer through the filter chain mechanism provided by Druid.

1. In POM Add Druid dependency to XML file

   <!--Druid-->
   <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid-spring-boot-starter</artifactId>
       <version>1.2.3</version>
   </dependency>
   
   <dependency>
     	<groupId>org.springframework.boot</groupId>
     	<artifactId>spring-boot-configuration-processor</artifactId>
     	<optional>true</optional>
   </dependency>
   

2. Modify application Yaml configuration file, replace the original data source configuration with Druid data source, and configure data source related parameters

   spring:
     datasource:
       name: druidDataSource
       type: com.alibaba.druid.pool.DruidDataSource
       druid:
         driver-class-name: com.mysql.jdbc.Driver
         url: jdbc:mysql://127.0.0.1:3306/festmon?characterEncoding=UTF-8&useSSL=false
         username: root
         password: 123456
         filters: stat,wall,slf4j,config                  #Configure the filters for monitoring statistics interception. After removing the filters, the SQL of the monitoring interface cannot be counted. The wall is used for the firewall.
         max-active: 100            #maximum connection
         initial-size: 1            #Initialization size
         max-wait: 60000            #Get connection wait timeout
         min-idle: 1                #Minimum number of connections
         time-between-eviction-runs-millis: 60000         #How often is the detection performed? The unit is milliseconds to detect the idle connections that need to be closed.
         min-evictable-idle-time-millis: 300000           #The minimum lifetime of a connection in the pool, in milliseconds.
         validation-query: select 'x'
         test-while-idle: true
         test-on-borrow: false
         test-on-return: false
         pool-prepared-statements: true
         max-open-prepared-statements: 50
         max-pool-prepared-statement-per-connection-size: 20
   

3. Druid Spring Starter simplifies many configurations. If the default configuration does not meet the requirements, you can also customize the configuration. Create the properties folder, and create druiddatasourceproperties Class file

   package com.springframe.festmon.properties;
   
   import org.springframework.boot.context.properties.ConfigurationProperties;
   
   @ConfigurationProperties(prefix = "spring.datasource.druid")  //Scan attribute prefix of configuration class
   public class DruidDataSourceProperties {
   
       // jdbc
       private String driverClassName;
       private String url;
       private String username;
       private String password;
       // jdbc connection pool
       private int initialSize;
       private int minIdle;
       private int maxActive = 100;
       private long maxWait;
       private long timeBetweenEvictionRunsMillis;
       private long minEvictableIdleTimeMillis;
       private String validationQuery;
       private boolean testWhileIdle;
       private boolean testOnBorrow;
       private boolean testOnReturn;
       private boolean poolPreparedStatements;
       private int maxPoolPreparedStatementPerConnectionSize;
       // filter
       private String filters;
   
       public String getDriverClassName() {
           return driverClassName;
       }
   
       public void setDriverClassName(String driverClassName) {
           this.driverClassName = driverClassName;
       }
   
       public String getUrl() {
           return url;
       }
   
       public void setUrl(String url) {
           this.url = url;
       }
   
       public String getUsername() {
           return username;
       }
   
       public void setUsername(String username) {
           this.username = username;
       }
   
       public String getPassword() {
           return password;
       }
   
       public void setPassword(String password) {
           this.password = password;
       }
   
       public int getInitialSize() {
           return initialSize;
       }
   
       public void setInitialSize(int initialSize) {
           this.initialSize = initialSize;
       }
   
       public int getMinIdle() {
           return minIdle;
       }
   
       public void setMinIdle(int minIdle) {
           this.minIdle = minIdle;
       }
   
       public int getMaxActive() {
           return maxActive;
       }
   
       public void setMaxActive(int maxActive) {
           this.maxActive = maxActive;
       }
   
       public long getMaxWait() {
           return maxWait;
       }
   
       public void setMaxWait(long maxWait) {
           this.maxWait = maxWait;
       }
   
       public long getTimeBetweenEvictionRunsMillis() {
           return timeBetweenEvictionRunsMillis;
       }
   
       public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
           this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
       }
   
       public long getMinEvictableIdleTimeMillis() {
           return minEvictableIdleTimeMillis;
       }
   
       public void setMinEvictableIdleTimeMillis(long minEvictableIdleTimeMillis) {
           this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
       }
   
       public String getValidationQuery() {
           return validationQuery;
       }
   
       public void setValidationQuery(String validationQuery) {
           this.validationQuery = validationQuery;
       }
   
       public boolean isTestWhileIdle() {
           return testWhileIdle;
       }
   
       public void setTestWhileIdle(boolean testWhileIdle) {
           this.testWhileIdle = testWhileIdle;
       }
   
       public boolean isTestOnBorrow() {
           return testOnBorrow;
       }
   
       public void setTestOnBorrow(boolean testOnBorrow) {
           this.testOnBorrow = testOnBorrow;
       }
   
       public boolean isTestOnReturn() {
           return testOnReturn;
       }
   
       public void setTestOnReturn(boolean testOnReturn) {
           this.testOnReturn = testOnReturn;
       }
   
       public boolean isPoolPreparedStatements() {
           return poolPreparedStatements;
       }
   
       public void setPoolPreparedStatements(boolean poolPreparedStatements) {
           this.poolPreparedStatements = poolPreparedStatements;
       }
   
       public int getMaxPoolPreparedStatementPerConnectionSize() {
           return maxPoolPreparedStatementPerConnectionSize;
       }
   
       public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
           this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
       }
   
       public String getFilters() {
           return filters;
       }
   
       public void setFilters(String filters) {
           this.filters = filters;
       }
   }
   

4. Configure Servlet and Filter

   Create a new DruidConfig configuration class under the config package, which is mainly used to inject attributes and connection pool related configurations, such as black-and-white list, monitoring and management background login account password, etc

   package com.springframe.festmon.config;
   
   import com.alibaba.druid.pool.DruidDataSource;
   import com.alibaba.druid.support.http.StatViewServlet;
   import com.alibaba.druid.support.http.WebStatFilter;
   import com.springframe.festmon.properties.DruidDataSourceProperties;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
   import org.springframework.boot.context.properties.EnableConfigurationProperties;
   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.servlet.Filter;
   import javax.servlet.Servlet;
   import javax.sql.DataSource;
   import java.sql.SQLException;
   import java.util.Collections;
   import java.util.HashMap;
   import java.util.Map;
   
   
   @Configuration
   @EnableConfigurationProperties({DruidDataSourceProperties.class})
   //@The EnableConfigurationProperties annotation is used to import the configuration information of the Druid customized in the previous step.
   public class DruidConfig {
   
       @Autowired
       private DruidDataSourceProperties properties;
   
       @Bean
       @ConditionalOnMissingBean
       public DataSource druidDataSource() {
           DruidDataSource druidDataSource = new DruidDataSource();
           druidDataSource.setDriverClassName(properties.getDriverClassName());
           druidDataSource.setUrl(properties.getUrl());
           druidDataSource.setUsername(properties.getUsername());
           druidDataSource.setPassword(properties.getPassword());
           druidDataSource.setInitialSize(properties.getInitialSize());
           druidDataSource.setMinIdle(properties.getMinIdle());
           druidDataSource.setMaxActive(properties.getMaxActive());
           druidDataSource.setMaxWait(properties.getMaxWait());
           druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
           druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
           druidDataSource.setValidationQuery(properties.getValidationQuery());
           druidDataSource.setTestWhileIdle(properties.isTestWhileIdle());
           druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
           druidDataSource.setTestOnReturn(properties.isTestOnReturn());
           druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
           druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
           try {
               druidDataSource.setFilters(properties.getFilters());
               druidDataSource.init();
           } catch (SQLException e) {
               e.printStackTrace();
           }
           return druidDataSource;
       }
   
       /**
        * Configure the Servlet of Druid monitoring management background;
        * There is no web when the Servlet container is built in XML file, so the Servlet registration method of Spring Boot is used
        * public ServletRegistrationBean druidServlet()Equivalent to WebServlet configuration.
        */
       @Bean
       @ConditionalOnMissingBean
       public ServletRegistrationBean<Servlet> druidServlet(){
           ServletRegistrationBean<Servlet> servletServletRegistrationBean = new ServletRegistrationBean<Servlet>(new StatViewServlet(), "/druid/*");
           //White list
           servletServletRegistrationBean.addInitParameter("allow","127.0.0.1");  //It means that only the local machine can access. When it is empty or null, it means that all access is allowed
           //ip Blacklist (deny takes precedence over allow when there is a common)
           //If deny is satisfied, you will be prompted, sorry, you are not allowed to view this page
           servletServletRegistrationBean.addInitParameter("deny","172.13.13.31");
           //The account and password for logging in and viewing information are used to log in Druid monitoring background
           servletServletRegistrationBean.addInitParameter("loginUsername","admin");
           servletServletRegistrationBean.addInitParameter("loginPassword","admin");
           //Can I reset the data
           servletServletRegistrationBean.addInitParameter("resetEnable","true");
           return servletServletRegistrationBean;
       }
   
       /**
        * Configure the filter of web monitoring for Druid monitoring
        * WebStatFilter: Used to configure management association monitoring statistics between Web and Druid data sources
        * public FilterRegistrationBean filterRegistrationBean()Equivalent to Web Filter configuration.
        */
       @Bean
       @ConditionalOnMissingBean
       public FilterRegistrationBean<Filter> filterFilterRegistrationBean(){
           FilterRegistrationBean<Filter> bean = new FilterRegistrationBean<Filter>();
           bean.setFilter(new WebStatFilter());
   
           //exclusions: sets which requests are filtered and excluded so that statistics are not performed
           Map<String, String> initParams = new HashMap<>();
           initParams.put("exclusions", "*.js,*.css,/druid/*");
           bean.setInitParameters(initParams);
   
           //"/ *" means to filter all requests
           bean.setUrlPatterns(Collections.singletonList("/*"));
           return bean;
       }
   }		
   

Configure slf4j

Spring Boot supports slf4j+logback logging framework by default. If you want to customize the logging policy, you can add a configuration file in the root directory for some configuration.

1. In POM Add dependency to XML file

   <!-- @Slf4j annotation -->
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
   </dependency>
   

2. Add Lombok plug-in under idea

   

3. Modify application Yaml file

   logging:
     config: logback.xml
     level:
       com.springframe.festmon.dao: trace
   

4. Create logback. Log in the root directory XML file

   <?xml version="1.0" encoding="UTF-8"?>
   <!-- configuration Label properties
       scan        When this property is set to true If the configuration file changes, it will be reloaded. The default value is true. 
       scanPeriod  Set the time interval for monitoring whether the configuration file is modified. If no time unit is given, the default unit is milliseconds. When scan by true This property takes effect when. The default interval is 1 minute.
       debug       When this property is set to true When, it will be printed out logback Internal log information, real-time viewing logback Running status. The default value is false. 
   -->
   <configuration debug="false">
       <!--The address where the log file is stored, using an absolute path-->
       <property name="LOG_HOME" value="/Users/YYX/Desktop/MyProject/Personal log file/Spring\ Cloud\ +\ Vue project/Spring\ Cloud backstage Demo/festmon/logs"/>
       <!-- Format of log
           %d          Represents the date
           %thread     Represents the thread name
           %-5level    Log level, 5 characters wide from the left
           %logger{56} The name of the class to which the log print belongs, which is limited to 56 characters
           %msg        Log message
           %n          Is a newline character
           color setting    %Color (above parameters), for example,%highlight(%-5level)
           Support color    "%black", "%red", "%green","%yellow","%blue", "%magenta","%cyan", "%white", "%gray", "%boldRed",
                       "%boldGreen", "%boldYellow", "%boldBlue", "%boldMagenta""%boldCyan", "%boldWhite" and "%highlight"
       -->
       <property name="LOG_FORMAT" value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%-5level] [%logger{56}]: %msg%n"/>
       <property name="LOG_COLOR_FORMAT" value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%cyan(%thread)] [%highlight(%-5level)] [%green(%logger{56})]: %msg%n"/>
   
       <!-- console output  -->
       <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
           <!-- Filter out TRACE Level log-->
           <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
               <level>TRACE</level>
           </filter>
           <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
               <!-- Formatted output of logs -->
               <pattern>${LOG_COLOR_FORMAT}</pattern>
           </encoder>
       </appender>
   
       <!-- Generate log files every day -->
       <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
           <!-- Filter out TRACE Level log-->
           <filter class="ch.qos.logback.classic.filter.ThresholdFilter">
               <level>TRACE</level>
           </filter>
           <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
               <!--The file name of the log file output-->
               <FileNamePattern>${LOG_HOME}/%d{yyyy-MM-dd}.log</FileNamePattern>
               <!--Log file retention days-->
               <MaxHistory>180</MaxHistory>
           </rollingPolicy>
           <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
               <!--Format output-->
               <pattern>${LOG_FORMAT}</pattern>
           </encoder>
           <!--Maximum size of log file-->
           <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
               <MaxFileSize>10MB</MaxFileSize>
           </triggeringPolicy>
       </appender>
   
       <!-- Log output level -->
       <root level="INFO">
           <appender-ref ref="STDOUT"/>
           <appender-ref ref="FILE"/>
       </root>
   
       <!-- Specify the local log level according to special requirements -->
       <logger name="org.springframework.jdbc.datasource.DataSourceTransactionManager" level="DEBUG"/>
   </configuration>
   

5. test

   Modify SysUserController file

   @RestController
   @Slf4j
   public class SysUserController {
   
       @Autowired
       private SysUserService sysUserService;
   
       @ApiOperation(value = "Select all users")
       @GetMapping("/user/selectAll")
       public Object selectAllUsers(){
           log.info("======Test log info Level printing=====");
           log.error("=====Test log error Level printing====");
           log.warn("======Test log warn Level printing=====");
           return sysUserService.list();
       }
   }
   

   The test results are as follows