Spring Boot druid configuration

rely on

1. Add druid dependency to Spring Boot project

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>${druid.version}</version>
</dependency>

To configure

spring.datasource.url= 
spring.datasource.username=
spring.datasource.password=
# ... other configurations (optional, not required. If the embedded database is used, the above three items can also be omitted)

Details of data source configuration parameters

• #Druid JDBC configuration

#Database connection: mysql
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/group-pool?useUnicode=true&characterEncoding=UTF-8&useSSL=false
#Database user name
spring.datasource.username=root
#Database password
spring.datasource.password=sansicn
#Database driver class
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

• #Druid CP configuration

#Data source name. When there are multiple data sources, the name can be used to distinguish them
#The default build name is: "DataSource-" + System.identityHashCode(this)
spring.datasource.name=
#Data source type
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#Number of physical connections established at initialization. Initialization occurs when the display calls the init method, or the first getConnection
spring.datasource.initialSize=10
#Minimum number of connection pools
spring.datasource.minIdle=10
#Maximum number of connection pools
spring.datasource.maxActive=200
#The maximum waiting time, in milliseconds, to get the connection.
#After maxWait is configured, fair lock is enabled by default, and the concurrency efficiency will be reduced. If you need to configure the useUnfairLock property to true, you can use unfair lock.
spring.datasource.maxWait=60000
#There are two meanings: 
#1) The destroy thread detects the interval between connections
#2) For the judgment basis of testWhileIdle, see the description of testWhileIdle attribute in detail
spring.datasource.timeBetweenEvictionRunsMillis=60000
#The minimum lifetime of a connection in the pool, in milliseconds
spring.datasource.minEvictableIdleTimeMillis=300000
#Verify whether the connection is valid or not. Different database configurations are different
spring.datasource.validationQuery=SELECT 1 FROM DUAL
#It is recommended to configure it as true, which will not affect performance and ensure security
#Check when applying for connection. If the idle time is greater than timebetweenevicitionrunsmillis, execute validationQuery to check whether the connection is valid.
spring.datasource.testWhileIdle=true
#When applying for a connection, perform validationQuery to check whether the connection is valid. This configuration will reduce performance
spring.datasource.testOnBorrow=false
#Perform validationQuery to check whether the connection is valid when returning the connection. This configuration will reduce the performance
spring.datasource.testOnReturn=false
#Whether to cache preparedStatement, i.e. PSCache
#PSCache greatly improves the performance of database supporting cursors, such as oracle
#In mysql, it is recommended to close???
spring.datasource.poolPreparedStatements=true
#Size of PSCache on each connection
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# Configure the filters intercepted by monitoring statistics. After the filters are removed, the monitoring interface sql cannot be counted. The 'wall' is used for the firewall
spring.datasource.filters=stat,wall,log4j

Data source configuration class

package com.shuchuanggroup.group_pool.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * DRUID Data source configuration
 */
@Configuration
public class DruidConfig {

    @Value("${spring.datasource.url}")
    private String dbUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.initialSize}")
    private Integer initialSize;
    @Value("${spring.datasource.minIdle}")
    private Integer minIdle;
    @Value("${spring.datasource.maxActive}")
    private Integer maxActive;
    @Value("${spring.datasource.maxWait}")
    private Integer maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private Integer timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private Integer minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}")
    private Boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}")
    private Boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}")
    private Boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private Integer maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}")
    private String filters;
    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;

    @Bean
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        //configuration
        if(initialSize != null) {
            datasource.setInitialSize(initialSize);
        }
        if(minIdle != null) {
            datasource.setMinIdle(minIdle);
        }
        if(maxActive != null) {
            datasource.setMaxActive(maxActive);
        }
        if(maxWait != null) {
            datasource.setMaxWait(maxWait);
        }
        if(timeBetweenEvictionRunsMillis != null) {
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        }
        if(minEvictableIdleTimeMillis != null) {
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        }
        if(validationQuery!=null) {
            datasource.setValidationQuery(validationQuery);
        }
        if(testWhileIdle != null) {
            datasource.setTestWhileIdle(testWhileIdle);
        }
        if(testOnBorrow != null) {
            datasource.setTestOnBorrow(testOnBorrow);
        }
        if(testOnReturn != null) {
            datasource.setTestOnReturn(testOnReturn);
        }
        if(poolPreparedStatements != null) {
            datasource.setPoolPreparedStatements(poolPreparedStatements);
        }
        if(maxPoolPreparedStatementPerConnectionSize != null) {
            datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        }

        if(connectionProperties != null) {
            datasource.setConnectionProperties(connectionProperties);
        }

        List<Filter> filters = new ArrayList<>();
        filters.add(statFilter());
        filters.add(wallFilter());
        datasource.setProxyFilters(filters);

        return datasource;
    }

    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
        servletRegistrationBean.setServlet(new StatViewServlet());
        servletRegistrationBean.addUrlMappings("/druid/*");
        return servletRegistrationBean;
    }

    @Bean
    public StatFilter statFilter(){
        StatFilter statFilter = new StatFilter();
        statFilter.setLogSlowSql(true);
        statFilter.setMergeSql(true);
        statFilter.setSlowSqlMillis(1000);

        return statFilter;
    }

    @Bean
    public WallFilter wallFilter(){
        WallFilter wallFilter = new WallFilter();

        //Allow multiple SQL execution
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        wallFilter.setConfig(config);

        return wallFilter;
    }

}

Browser monitoring configuration

• Monitoring support

#Configure the filters intercepted by monitoring statistics. After the filters are removed, the monitoring interface sql cannot be counted. The 'wall' is used for the firewall
spring.datasource.filters=stat,wall,log4j

• StatViewServlet servlet configuration

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

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

@WebServlet(urlPatterns="/druid/*",initParams={
        // @Webinitparam (name = "allow", value = "192.168.1.72127.0.0.1"), / / IP white list
        // @WebInitParam(name="deny",value="192.168.1.73"),// IP blacklist
        @WebInitParam(name="loginUsername",value="admin"),// User name
        @WebInitParam(name="loginPassword",value="123456"),// Password
        @WebInitParam(name="resetEnable",value="false")//Display reset statistics button
    }
)
public class DruidStatViewServlet extends StatViewServlet {
    private static final long serialVersionUID = 1L;
}

• WebStatFilter static resource filter

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

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

@WebFilter(filterName="druidWebStatFilter",
    urlPatterns="/*",
    initParams={
        @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*")//Ignore resources
    }
)
public class DruidStatFilter extends WebStatFilter {}

Multi data source configuration: mybatis

//You only need to add exclusion parameters in the spring boot app annotation to exclude the automatic configuration function of the data source
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class OnlyswordApplication {
	public static void main(String[] args) {
		SpringApplication.run(OnlyswordApplication.class, args);
	}
}

• properties file

#db1
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/only_sword?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

#db2
spring.datasource.db2.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.db2.url=jdbc:mysql://127.0.0.1:3306/only_sword?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.db2.username=root
spring.datasource.db2.password=root

• datasource configuration

1. ds1 configuration

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
public class Datasource1Config {

    @Value("${spring.datasource.url}")
    private String dbUrl;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;

    //Generate data source 1
    @Bean(name = "ds1")
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        return datasource;
    }
}

1. ds2 configuration

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;

@Configuration
public class Datasource2Config {

    @Value("${spring.datasource.db2.url}")
    private String dbUrl;
    @Value("${spring.datasource.db2.username}")
    private String username;
    @Value("${spring.datasource.db2.password}")
    private String password;
    @Value("${spring.datasource.db2.driver-class-name}")
    private String driverClassName;

    //Generate data source 2
    @Bean(name = "ds2")
    @Primary
    public DataSource dataSource(){
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        return datasource;
    }
}

• mybatis configuration

1. properties configuration

# mybatis_plus_config
mybatis-plus.m1.mapper-locations=classpath*:mapper1/*Mapper.xml
mybatis-plus.m1.typeAliasesPackage=com.sword.onlysword.entity


mybatis-plus.m2.mapper-locations=classpath*:mapper2/*Mapper.xml
mybatis-plus.m2.typeAliasesPackage=com.sword.onlysword.entity

1. Reference the ds1 and scan the mapper, link them to the specified SqlSessionTemplate and register them with the Spring context to inject them into the bean.

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sword.onlysword.mapper1*", sqlSessionTemplateRef = "template1")
public class MybatisConfig {

    @Value("${mybatis-plus.m1.mapper-locations}")
    private String mapperLocations;
    @Value("${mybatis-plus.m1.typeAliasesPackage}")
    private String aliasesPackage;

    @Autowired
    @Qualifier("db1")
    private DataSource db1;

    @Bean
    public SqlSessionTemplate template1() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db1);
        //Configure entity mapping
        factoryBean.setTypeAliasesPackage(aliasesPackage);
        //Configure the package of mapper.xml
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return new SqlSessionTemplate(factoryBean.getObject());
    }

}

1. Reference ds2 and scan the mapper, link them to the specified SqlSessionTemplate and register them with the Spring context to inject them into the bean.

import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.sword.onlysword.mapper2*", sqlSessionTemplateRef = "template2")
public class MybatisConfig {

    @Value("${mybatis-plus.m2.mapper-locations}")
    private String mapperLocations;
    @Value("${mybatis-plus.m2.typeAliasesPackage}")
    private String aliasesPackage;

    @Autowired
    @Qualifier("db2")
    private DataSource db2;

    @Bean
    public SqlSessionTemplate template2() throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(db2);
        factoryBean.setTypeAliasesPackage(aliasesPackage);
        factoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return new SqlSessionTemplate(factoryBean.getObject());
    }

}

Dynamic configuration of multiple data sources

Druid Spring Boot Starter

• Simpler druid configuration

Druid

• Source address