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


Configure connection pool

# JDBC configuration
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
# Minimum number of connections
# Maximum number of simultaneous connections
# Configure the timeout time of getting connection waiting, in milliseconds
# Configure how often to check the interval. Check the idle connections that need to be closed. The unit is ms
# Configure the minimum lifetime of a connection in the pool, in milliseconds
# 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
# 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
# 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
# Open PSCache and specify the size of PSCache on each connection
# 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
# Open mergeSql function through connectProperties property; slow SQL record

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;

@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";

    private String dbUrl;

    private String username;

    private String password;

    private String driverClassName;

    private int initialSize;

    private int minIdle;

    private int maxActive;

    private int maxWait;

    private boolean testWhileIdle;

    private int timeBetweenEvictionRunsMillis;

    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
    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
    private boolean testOnReturn;

    private int minEvictableIdleTimeMillis;

     * When on, a statement pool will be created for each connection, and the PreparedStatements created by the method will be cached:
    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
    private int maxPoolPreparedStatementPerConnectionSize;

    private boolean defaultAutoCommit;*/

    private String filters;

     * Connection parameters sent to JDBC driver when a new connection is established
    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")
    public DataSource dataSource() throws Exception {
        DruidDataSource datasource = new DruidDataSource();
        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")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        return sessionFactory.getObject();

     * Define transaction manager
     * @param dataSource
     * @return
    @Bean(name = "transactionManager")
    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
# Multiple white list IP S separated by commas
# Multiple blacklist IP S are separated by commas -- deny takes precedence over allow when they exist together
# druid monitoring management interface login account
# Login password of druid monitoring management interface
# Whether to enable the reset data function

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;

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

    private String webStatFilterEnabled;
    private String allow;
    private String deny;
    private String loginUsername;
    private String loginPassword;
    private String resetEnable;

     * Register Servlet information and configure monitoring view
     * @return
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        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
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        //Intercept url type, * means to intercept all requests
        //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

The login page of Druid monitoring appears
Congratulations, configuration succeeded


Topics: Spring Druid xml JDBC