Spring boot integrates Druid data connection pool

What is Druid?

Druid is Alibaba's open source database connection pool. Druid can provide powerful monitoring and extension functions.

Where to download druid

maven central warehouse: http://central.maven.org/maven2/com/alibaba/druid/

How to get the source code of Druid

Druid is an open source project. The source code is hosted on github. The source code warehouse address is https://github.com/alibaba/druid . At the same time, every time Druid releases the official version and snapshot, it will package the source code. You can find the source code of the relevant version from the above download address

How to configure maven

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <! -- the latest version number can be obtained through the central warehouse. Here, use the version number in my project -- >
    <version>1.0.11</version>
</dependency>

Start configuration

• First, application.properties

# Database configuration
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# Note: Druid's data source must be used here
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.name=test
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=10
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=3600000
spring.datasource.minEvictableIdleTimeMillis=3600000
spring.datasource.validationQuery=select 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=true
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,slf4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.useGlobalDataSourceStat=true

# druid login information
druid.config.user-name=root
druid.config.password=root
druid.config.url-mapping=/druid/*

• Encapsulate database configuration to Java Bean

@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DBProperties {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;
    private boolean useGlobalDataSourceStat;

    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 String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    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 int getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(int 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;
    }

    public String getConnectionProperties() {
        return connectionProperties;
    }

    public void setConnectionProperties(String connectionProperties) {
        this.connectionProperties = connectionProperties;
    }

    public boolean isUseGlobalDataSourceStat() {
        return useGlobalDataSourceStat;
    }

    public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
        this.useGlobalDataSourceStat = useGlobalDataSourceStat;
    }

}

@Configuration
@ConfigurationProperties(prefix = "druid.config")
public class DruidProperties {

    private String userName;

    private String password;

    private String urlMapping;

    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 String getUrlMapping() {
        return urlMapping;
    }

    public void setUrlMapping(String urlMapping) {
        this.urlMapping = urlMapping;
    }
}

• Configure data sources

/**
 * Persistent layer related configuration
 */
@Configuration
public class SessionFactoryConfig {

    private static final Logger LOGGER = LoggerFactory.getLogger(SessionFactoryConfig.class);

    private final DBProperties db;

    @Autowired
    public SessionFactoryConfig(DBProperties db) {
        this.db = db;
    }

    /**
     * create data source
     */
    @Bean
    // If the data source page displays (*) property for user to setup when visiting Druid, the following Bean injection methods can be used
    // @Bean(destroyMethod = "close", initMethod = "init")
    @Primary
    public DataSource dataSource() {
        final DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(db.getUrl());
        datasource.setUsername(db.getUsername());
        datasource.setPassword(db.getPassword());
        datasource.setDriverClassName(db.getDriverClassName());
        datasource.setInitialSize(db.getInitialSize());
        datasource.setMinIdle(db.getMinIdle());
        datasource.setMaxActive(db.getMaxActive());
        datasource.setMaxWait(db.getMaxWait()); datasource.setTimeBetweenEvictionRunsMillis(db.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(db.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(db.getValidationQuery());
        datasource.setTestOnReturn(db.isTestOnReturn());
        datasource.setPoolPreparedStatements(db.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(db.getMaxPoolPreparedStatementPerConnectionSize());
        datasource.setUseGlobalDataSourceStat(db.isUseGlobalDataSourceStat());
        try {
            datasource.setFilters(db.getFilters());
        } catch (SQLException e) {
            LOGGER.info("druid configuration initialization filter: " + e);
        }
        datasource.setConnectionProperties(db.getConnectionProperties());
        return datasource;
    }
}

• Druid connection pool configuration

/**
 * Druid Connection pool configuration
 */
@Configuration
public class DruidConfiguration {

    private static final Logger LOGGER = LoggerFactory.getLogger(DruidConfiguration.class);

    private final DruidProperties druidProperties;

    @Autowired
    public DruidConfiguration(DruidProperties dp) {
        this.druidProperties = dp;
    }

    /**
     * Access path, user name and password
     */
    @Bean
    public ServletRegistrationBean druidConfig() {
        LOGGER.info("init Druid Servlet Configuration ");
        final ServletRegistrationBean srb = new ServletRegistrationBean();
        srb.setServlet(new StatViewServlet());
        LOGGER.info("Druid Config Info --> {}", druidProperties);
        srb.addUrlMappings(druidProperties.getUrlMapping());
        srb.addInitParameter("loginUsername", druidProperties.getUserName());
        srb.addInitParameter("loginPassword", druidProperties.getPassword());
        return srb;
    }

    /**
     * Configure filters
     */
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        final FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

• Entrust Druid with Spring monitoring

/**
 * Entrust Druid with Spring monitoring
 */
@Configuration
public class SpringMonitoringConfig {

    @Bean(name = "druid-stat-interceptor")
    public DruidStatInterceptor druidStatInterceptor() {
        return new DruidStatInterceptor();
    }

    @Bean(name = "druid-stat-pointcut")
    // Non singleton
    @Scope("prototype")
    public JdkRegexpMethodPointcut druidStatPointcut() {
        final JdkRegexpMethodPointcut pointcut = new JdkRegexpMethodPointcut();
        pointcut.setPatterns("com.ajm.punch_the_clock.controller.*", "com.ajm.punch_the_clock.mapper.*");
        return pointcut;
    }

    @Bean
    public DefaultPointcutAdvisor druidStatAdvisor(@Qualifier("druid-stat-interceptor") final DruidStatInterceptor druidStatInterceptor,
                                                   @Qualifier("druid-stat-pointcut") final JdkRegexpMethodPointcut druidStatPointcut) {
        final DefaultPointcutAdvisor defaultPointAdvisor = new DefaultPointcutAdvisor();
        defaultPointAdvisor.setPointcut(druidStatPointcut);
        defaultPointAdvisor.setAdvice(druidStatInterceptor);
        return defaultPointAdvisor;
    }

}

• Project start, visit http://127.0.0.1: port number / druid