HikariCP performs better than Druid, but Druid comes with a variety of monitoring tools, and ali has been endorsing it. This article describes how Spring Boot integrates with Druid data sources. Since Spring Boot versions 1.3.X and 1.4.X have slightly different druid configurations, they are described separately below.

Spring Boot 1.3.X configuration

Introducing Druid dependencies

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.23</version>
        </dependency>
Copy the code

New database connection pool configuration in application.properties

#druid datasouce database settings begin spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.driver-class-name=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/spring_boot? CharacterEncoding = utf-8 spring. The datasource. The username = root spring. The datasource. The password = 123456 # to replenish the connection pool Settings below, Apply to all of the above data sources Spring. The biggest datasource. InitialSize = 5 spring. The datasource. MinIdle = 5 spring. The datasource. MaxActive = 20 # configuration for connecting wait for timeout time Spring. The datasource. MaxWait = 60000 # configure how long is the interval to conduct a test, testing needs to be closed free connection, Unit is ms spring. The datasource. TimeBetweenEvictionRunsMillis = 60000 # to configure a connection in the pool minimum survival time, Unit is ms spring. The datasource. MinEvictableIdleTimeMillis = 300000 spring. The datasource. The validationQuery = SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false # Open the PSCache, And the size of the specified on each connection PSCache spring. The datasource. PoolPreparedStatements = true Spring. The datasource. MaxPoolPreparedStatementPerConnectionSize = 20 # configuration monitoring statistics of intercepting filters, remove the monitor interface after SQL is unable to statistics, 'wall' firewall used in spring. The datasource. Filters = stat, wall, log4j # by connectProperties attribute to open mergeSql function; Slow SQL record spring. The datasource. ConnectionProperties = druid. Stat. MergeSql = true; Druid. Stat. SlowSqlMillis = 5000 # merge multiple DruidDataSource monitoring data of the spring. The datasource. UseGlobalDataSourceStat = true # druid datasouce  database settings endCopy the code

Define Filter to ignore static resource interception


package com.bluecoffee.filter;

import com.alibaba.druid.support.http.WebStatFilter;

import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;

/** * Created by qianlong on 16/12/21. */
@WebFilter(filterName="druidStatFilter",urlPatterns="/*",
        initParams={
                @WebInitParam(name = "exclusions" value = "*. Js, *. GIF, * JPG, *. BMP, *. PNG, *. CSS, *. Ico, / druid / *")}) / / ignore resources public class DruidStatFilter extends WebStatFilter { }Copy the code

Define servlets for viewing the Druid console


package com.bluecoffee.servlet;

import com.alibaba.druid.support.http.StatViewServlet;

import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;

/** * Created by qianlong on 16/8/25. */
@SuppressWarnings("serial")
@WebServlet(urlPatterns = "/druid/*",
        initParams={
                @WebInitParam(name="allow",value="192.168.16.110,127.0.0.1"),// IP whitelist (not configured or empty, all access allowed) @webinitparam (name="deny",value="192.168.16.111"),// IP blacklist (deny takes precedence over allow when common) @webinitparam (name="loginUsername",value="admin"),// username @webinitparam (name="loginPassword",value="123456"), / / password @ WebInitParam (name ="resetEnable",value="false"} public class DruidStatViewServlet extends StatViewServlet {}Copy the code

Note the addition on the main execution class@ServletComponentScanAnnotations, otherwise the Servlet will not take effect


@SpringBootApplication
@ServletComponentScan
@Configuration
@EnableAutoConfiguration
public class Application {

    public static void main(String[] args) throws Exception {
        System.out.println("------------Application is start---------------"); SpringApplication.run(Application.class, args); }}Copy the code

You can open http://localhost:8080/druid/index.html see the effect, as shown in the figure below

Druid console

 

Spring Boot 1.4.X configuration

I initially integrated Druid on 1.3.3.RELEASE, but when I migrated to Spring Boot1.4.2, I found that the SQL console could not monitor SQL operations anyway, so I had to use @bean assembly to configure the Druid data source, and finally found that it worked. Druid: @bean: Druid: @bean: Druid: @bean: Druid: @bean

Added the Druid data source configuration file

Delete druid configuration from the original application.properties file and add the druid-config.properties configuration file

#druid datasouce database settings begin spring.druid.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.druid.datasource.driverClassName=com.mysql.jdbc.Driver spring.druid.datasource.url=jdbc:mysql://localhost:3306/spring_boot? CharacterEncoding = utf-8 spring. The druid. The datasource. The username = root spring. The druid. The datasource. The password = 123456 # to replenish the connection pool Settings below, Apply to all of the above data sources Spring. The biggest druid. The datasource. InitialSize = 5 spring. The druid. The datasource. MinIdle = 5 spring. Druid. The datasource. MaxActive = # 20 The configuration for connecting waiting timeout time spring. The druid. The datasource. MaxWait = 60000 # configuration interval how to conduct a test, testing needs to be closed free connection, Unit is milliseconds spring. The druid. The datasource. TimeBetweenEvictionRunsMillis = 60000 # to configure a connection in the pool minimum survival time, Unit is milliseconds spring. The druid. The datasource. MinEvictableIdleTimeMillis = 300000 spring. The druid. The datasource. ValidationQuery = 1 FROM the SELECT DUAL spring.druid.datasource.testWhileIdle=true spring.druid.datasource.testOnBorrow=false Spring. The druid. The datasource. TestOnReturn = false PSCache # open, And specify on each connection PSCache spring. The size of the druid. The datasource. PoolPreparedStatements = true Spring. The druid. The datasource. MaxPoolPreparedStatementPerConnectionSize = 20 # configuration monitoring statistics of intercepting filters, remove the monitor interface after SQL is unable to statistics, 'wall' firewall used in spring. The druid. The datasource. Filters = stat, wall, log4j, config # by connectProperties attribute to open mergeSql function; Slow SQL record spring. The druid. The datasource. ConnectionProperties = druid. Stat. MergeSql = true; Druid. Stat. SlowSqlMillis = 5000 # merge multiple DruidDataSource monitoring data of the spring. The druid. The datasource. UseGlobalDataSourceStat = true # druid datasouce database settings endCopy the code

New property reading class druidSettings.java


package com.bluecoffee.configuration;

import org.springframework.boot.context.properties.ConfigurationProperties;

/** * Created by qianlong on 2016/12/21. */
@ConfigurationProperties(prefix = "spring.druid.datasource",locations = "classpath:druid-config.properties")
public class DruidSettings {

    private String type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;

    private Integer initialSize;
    private Integer minIdle;
    private Integer maxActive;
    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 Integer maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;
    private boolean useGlobalDataSourceStat;

    // omit getter/setter methods

}
Copy the code

Read the configuration and instantiate the data source in Bean mode


package com.bluecoffee.configuration;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.Properties;

/** * Created by qianlong on 2016/12/21. */
@Configuration
@EnableConfigurationProperties(DruidSettings.class)
public class DruidDataSourceConfig {

    @Autowired
    private DruidSettings druidSettings;

    @Bean
    @ConfigurationProperties("spring.druid.datasource")
    public DruidDataSource dataSource( DataSourceProperties properties) throws Exception{
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(druidSettings.getDriverClassName());
        dataSource.setUrl(druidSettings.getUrl());
        dataSource.setUsername(druidSettings.getUsername());
        dataSource.setPassword(druidSettings.getPassword());
        dataSource.setInitialSize(druidSettings.getInitialSize());
        dataSource.setMinIdle(druidSettings.getMinIdle());
        dataSource.setMaxActive(druidSettings.getMaxActive());
        dataSource.setMaxWait(druidSettings.getMaxWait());
        dataSource.setTimeBetweenEvictionRunsMillis(druidSettings.getTimeBetweenEvictionRunsMillis());
        dataSource.setMinEvictableIdleTimeMillis(druidSettings.getMinEvictableIdleTimeMillis());
        String validationQuery = druidSettings.getValidationQuery();
        if(validationQuery ! =null&&!"".equals(validationQuery)) {
            dataSource.setValidationQuery(validationQuery);
        }
        dataSource.setTestWhileIdle(druidSettings.isTestWhileIdle());
        dataSource.setTestOnBorrow(druidSettings.isTestOnBorrow());
        dataSource.setTestOnReturn(druidSettings.isTestOnReturn());
        if(druidSettings.isPoolPreparedStatements()){
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(druidSettings.getMaxPoolPreparedStatementPerConnectionSize());
        }
        dataSource.setFilters(druidSettings.getFilters());// This is the most critical, otherwise SQL monitoring will not work
        String connectionPropertiesStr = druidSettings.getConnectionProperties();
        if(connectionPropertiesStr ! =null&&!"".equals(connectionPropertiesStr)){
            Properties connectProperties = new Properties();
            String[] propertiesList = connectionPropertiesStr.split(";");
            for(String propertiesTmp:propertiesList){
                String[] obj = propertiesTmp.split("=");
                String key = obj[0];
                String value = obj[1];
                connectProperties.put(key,value);
            }
            dataSource.setConnectProperties(connectProperties);
        }
        dataSource.setUseGlobalDataSourceStat(druidSettings.isUseGlobalDataSourceStat());

        returndataSource; }}Copy the code

Execute the main execution class to run the Druid console

Chapter 4-1-3-Spring Boot integration Druid data source

Author: blue coffee link: www.jianshu.com/p/d977ac680… The copyright of the book belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please indicate the source.