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