Learn springboot step by step -- the story of springboot and Druid

Posted by drayfuss on Thu, 28 Nov 2019 20:42:12 +0100

Add dependency

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.0</version>
        </dependency>

Configure connection pool

# JDBC configuration
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.url = jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username = root
spring.datasource.password = root
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver

# Connection pool configuration information
# Number of initial connections
spring.datasource.initialSize=5
# Minimum number of connections
spring.datasource.minIdle=5
# Maximum number of simultaneous connections
spring.datasource.maxActive=20
# Configure the timeout time of getting connection waiting, in milliseconds
spring.datasource.maxWait=60000
# Configure how often to check the interval. Check the idle connections that need to be closed. The unit is ms
spring.datasource.timeBetweenEvictionRunsMillis=60000
# Configure the minimum lifetime of a connection in the pool, in milliseconds
spring.datasource.minEvictableIdleTimeMillis=300000
# SQL query, used to verify the connection taken from the connection pool, before returning the connection to the caller. If specified, the query must be a SQL SELECT and return at least one row of records
# from dual:dual is a virtual table for testing
spring.datasource.validationQuery=SELECT 1 FROM DUAL
# Indicates whether the connection is verified by the free connection collector, if any. If the detection fails, the connection is removed from the pool
# Note: to take effect after setting to true, the validationQuery parameter must be set to a non empty string
spring.datasource.testWhileIdle=true
# Indicates whether the connection is verified before it is removed from the pool. If the verification fails, the connection is removed from the pool and an attempt is made to remove another connection
# Note: to take effect after setting to true, the validationQuery parameter must be set to a non empty string
spring.datasource.testOnBorrow=false
# Indicates whether to check before returning to the pool
#Note: to take effect after setting to true, the validationQuery parameter must be set to a non empty string
spring.datasource.testOnReturn=false
# Open PSCache and specify the size of PSCache on each connection
spring.datasource.poolPreparedStatements=true
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
# Open mergeSql function through connectProperties property; slow SQL record
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000;

Adding log4j to spring.datasource.filters will run with this error:
Factory method 'dataSource' threw exception; nested exception is java.lang.NoClassDefFoundError: org/apache/log4j/Logger
Just get rid of it.

To facilitate future expansion, a data source configuration interface is provided here

Create DatabaseConfig interface

package com.test.springbm.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * Data source configuration interface:
 * To facilitate future expansion, a data source configuration interface is provided here, and druid configuration is only an implementation class of this interface, which is convenient for switching different data sources in the future
 */
public interface DatabaseConfig {
    /**
     * Define data source
     * @return
     * @throws Exception
     */
    DataSource dataSource() throws Exception;

    /**
     * Define session factory
     * @param dataSource
     * @return
     * @throws Exception
     */
    SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception;

    /**
     * Define transaction manager
     * @param dataSource
     * @return
     */
    DataSourceTransactionManager dataSourceTransactionManager(DataSource dataSource);
}

Create DruidConfig class to implement DatabaseConfig interface

package com.test.springbm.config.impl;

/**
 *                             _ooOoo_
 *                            o8888888o
 *                            88" . "88
 *                            (| -_- |)
 *                            O\  =  /O
 *                         ____/`---'\____
 *                       .'  \\|     |//  `.
 *                      /  \\|||  :  |||//  \
 *                     /  _||||| -:- |||||-  \
 *                     |   | \\\  -  /// |   |
 *                     | \_|  ''\---/''  |   |
 *                     \  .-\__  `-`  ___/-. /
 *                   ___`. .'  /--.--\  `. . __
 *                ."" '<  `.___\_<|>_/___.'  >'"".
 *               | | :  `- \`.;`\ _ /`;.`/ - ` : | |
 *               \  \ `-.   \_ __\ /__ _/   .-` /  /
 *          ======`-.____`-.___\_____/___.-`____.-'======
 *                             `=---='
 *          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
 *                     Buddha bless never BUG
 *            Buddha said:
 *                   Office building office, office programmer;
 *                   The programmers write programs and exchange them for drinks.
 *                   Drunk only sit on the Internet, drunk to sleep under the net;
 *                   Drunk and sober day after day, the Internet next year after year.
 *                   I wish I didn't bow to my boss in the computer room;
 *                   Mercedes Benz and BMW are expensive, and bus self programmers.
 *                   Others laugh at my madness, I laugh at my life too cheap;
 *                   I don't see beautiful girls all over the street. Which one is the programmer?
 */

import com.alibaba.druid.pool.DruidDataSource;
import com.test.springbm.config.DatabaseConfig;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@ConfigurationProperties(value="application.properties")
@MapperScan(basePackages = DruidConfig.PACKAGE, sqlSessionFactoryRef = "sessionFactory")
public class DruidConfig implements DatabaseConfig {

	//The location of the report where your Mapper.java is located, scan the classes under this package
    public static final String PACKAGE = "com.test.springbm.mapper";
	//The location of your Mapper.xml, classpath is the relative path, * Mapper.xml represents all XML ending in Mapper
    public static final String MAPPER = "classpath:/mapper/*Mapper.xml";

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;

    @Value("${spring.datasource.initialSize}")
    private int initialSize;

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

    @Value("${spring.datasource.maxActive}")
    private int maxActive;

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.validationQuery}")
    private String validationQuery;

    /**
     * Indicates whether to check before removing the connection from the pool. If the check fails, remove the connection from the pool and try to remove another one. < br / >
     * Note: to take effect after setting to true, the validationQuery parameter must be set to a non empty string
     */
    @Value("${spring.datasource.testOnBorrow}")
    private boolean testOnBorrow;

    /**
     * Indicates whether to check before returning to the pool < br / >
     * Note: to take effect after setting to true, the validationQuery parameter must be set to a non empty string
     */
    @Value("${spring.datasource.testOnReturn}")
    private boolean testOnReturn;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    /**
     * When on, a statement pool will be created for each connection, and the PreparedStatements created by the method will be cached:
     */
    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    /**
     * Unlimited the maximum number of open statements that the statement pool can allocate at the same time. If it is set to 0, it means unlimited
     */
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
    private int maxPoolPreparedStatementPerConnectionSize;

    /*@Value("${spring.datasource.defaultAutoCommit:false}")
    private boolean defaultAutoCommit;*/

    @Value("${spring.datasource.filters}")
    private String filters;

    /**
     * Connection parameters sent to JDBC driver when a new connection is established
     */
    @Value("${spring.datasource.connectionProperties}")
    private String connectionProperties;

    /**
     * Define data source
     * Note that @ Primary annotation means that when there are multiple Bean candidates in automatic assembly, the Bean annotated as @ Primary will be the preferred one, otherwise an exception will be thrown. SpringBoot provides
     * A default dataSource is provided. When we need to use our own connection pool, we can use the @ primary annotation to tell SpringBoot to use its own dataSource first
     * @return
     * @throws Exception
     */
    @Bean(name = "dataSource")
    @Primary
    @Override
    public DataSource dataSource() throws Exception {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(this.username);
        datasource.setPassword(this.password);
        datasource.setDriverClassName(this.driverClassName);
        datasource.setInitialSize(this.initialSize);
        datasource.setMinIdle(this.minIdle);
        datasource.setMaxActive(this.maxActive);
        datasource.setMaxWait(this.maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(this.timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(this.minEvictableIdleTimeMillis);
        datasource.setValidationQuery(this.validationQuery);
        datasource.setTestWhileIdle(this.testWhileIdle);
        datasource.setTestOnBorrow(this.testOnBorrow);
        datasource.setTestOnReturn(this.testOnReturn);
        datasource.setPoolPreparedStatements(this.poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(this.maxPoolPreparedStatementPerConnectionSize);
        /*datasource.setDefaultAutoCommit(this.defaultAutoCommit);*/
        datasource.setFilters(this.filters);
        datasource.setConnectionProperties(this.connectionProperties);
        return datasource;
    }

    /**
     * Define session factory
     * Note: the meaning of "ualifier" is qualified. Through this indicator, it indicates which implementation class is what we need,
     * We need to modify the calling code and add @ Qualifier annotation. Note that the parameter name of @ Qualifier must be one of the names of the @ Service annotation defined before!
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "sessionFactory")
    @Primary
    @Override
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactory.setMapperLocations(resolver.getResources(DruidConfig.MAPPER));
        return sessionFactory.getObject();
    }

    /**
     * Define transaction manager
     * @param dataSource
     * @return
     */
    @Bean(name = "transactionManager")
    @Override
    public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

Because all properties are injected through @ value, as long as they are set in application.properties, we only need to change them

//The location of the report where your Mapper.java is located, scan the classes under this package
public static final String PACKAGE = "com.test.springbm.mapper";
//The location of your Mapper.xml, classpath is the relative path, * Mapper.xml represents all XML ending in Mapper
public static final String MAPPER = "classpath:/mapper/*Mapper.xml";

These two properties are fine

Next, we can turn on druid monitoring

First, create a new configuration file druid-monitor.properties to configure

# Enable StatFilter default value true
spring.datasource.druid.web-stat-filter.enabled=true
# Multiple white list IP S separated by commas
druid.monitor.allow=127.0.0.1
# Multiple blacklist IP S are separated by commas -- deny takes precedence over allow when they exist together
druid.monitor.deny=0.0.0.0
# druid monitoring management interface login account
druid.monitor.loginUsername=root
# Login password of druid monitoring management interface
druid.monitor.loginPassword=root
# Whether to enable the reset data function
druid.monitor.resetEnable=false

Newly build

package com.test.springbm.monitor;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.test.springbm.config.impl.DruidConfig;
import org.springframework.beans.factory.annotation.Value;
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 org.springframework.context.annotation.PropertySource;

@Configuration
@PropertySource(value = "classpath:config/druid-monitor.properties")
@EnableConfigurationProperties({DruidConfig.class})
public class DruidMonitorConfiguration {

    @Value("${spring.datasource.druid.web-stat-filter.enabled}")
    private String webStatFilterEnabled;
    @Value("${druid.monitor.allow}")
    private String allow;
    @Value("${druid.monitor.deny}")
    private String deny;
    @Value("${druid.monitor.loginUsername}")
    private String loginUsername;
    @Value("${druid.monitor.loginPassword}")
    private String loginPassword;
    @Value("${druid.monitor.resetEnable}")
    private String resetEnable;

    /**
     * Register Servlet information and configure monitoring view
     * @return
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        servletRegistrationBean.addInitParameter("spring.datasource.druid.web-stat-filter.enabled",this.webStatFilterEnabled);
        servletRegistrationBean.addInitParameter("allow", this.allow);
        servletRegistrationBean.addInitParameter("deny", this.deny);
        servletRegistrationBean.addInitParameter("loginUsername", this.loginUsername);
        servletRegistrationBean.addInitParameter("loginPassword", this.loginPassword);
        servletRegistrationBean.addInitParameter("resetEnable", this.resetEnable);
        return servletRegistrationBean;
    }

    /**
     * Register Filter information and monitor interceptors
     * @return
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        //Intercept url type, * means to intercept all requests
        filterRegistrationBean.addUrlPatterns("/*");
        //Ignore resources
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

You only need to import your configuration file and the bytecode file of DruidConfig.java above
@PropertySource(value = "classpath:config/druid-monitor.properties") @EnableConfigurationProperties({DruidConfig.class})

This is the end of Druid configuration

To access the Druid monitoring page

Visit
http://localhost:8080/druid/login.html
The login page of Druid monitoring appears
Congratulations, configuration succeeded

End

Topics: Spring Druid xml JDBC