I. Druid
Druid is the best database connection pool in the Java language, and also the open source project of Alibaba. Druid is a database connection pool developed by Alibaba called monitoring. In terms of function, performance and scalability, Druid is superior to other database connection pools, including DBCP, C3P0, BoneCP, Proxool, JBoss DataSource, etc., killing everything in a second. Moreover, Druid has deployed more than 600 applications in Alibaba, which has passed the severe test of large-scale deployment of production environment for several years!
2, Project and document address
https://github.com/alibaba/druid
https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
3, filters: stat,wall,log4j
- stat: Druid provides a StatFilter built-in for statistics monitoring information. If you need to configure the access page, you can refer to, https://github.com/alibaba/druid/wiki/%E9%85%8D%E7%BD%AE_StatViewServlet%E9%85%8D%E7%BD%AE , I haven't studied this yet. If you have any research trouble, please pay attention to it and tell me that you will find an opportunity to update it in the future. After all, it is very useful.
- wall: Druid's WallFilter to defend against SQL injection attacks is analyzed through Druid's SQL Parser. The SQL Parser provided by Druid can intercept SQL in the JDBC layer for corresponding processing, such as database splitting, table splitting, auditing, etc.
- Log4j: This is the function of logging. You can print sql statements to log4j for troubleshooting.
Four, Maven
<!-- mysql drive --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!-- Alibaba druid Database connection pool --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.3</version> </dependency>
5, application.yml configuration of Druid
#Spring Boot Config
#port
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/xxx?useSSL=false&useUnicode=true&characterEncoding=utf-8
username: root
password: 112233
driver-class-name: com.mysql.jdbc.Driver
platform: mysql
type: com.alibaba.druid.pool.DruidDataSource
# The following is the supplementary settings of the connection pool, which are applied to all data sources above
# Initialization size, min, Max
initialSize: 5
minIdle: 5
maxActive: 20
# Configure the timeout time for getting connection waiting
maxWait: 60000
# Configure how often to check the interval. Check the idle connections that need to be closed, in milliseconds
timeBetweenEvictionRunsMillis: 60000
# Configure the minimum lifetime of a connection in the pool, in milliseconds
minEvictableIdleTimeMillis: 30000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
# Open PSCache and specify the size of PSCache on each connection
poolPreparedStatements: true
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
filters: stat,wall,slf4j
logSlowSql: true
# Open mergeSql function through connectProperties property; slow SQL record
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# Merge monitoring data from multiple druiddatasources
#useGlobalDataSourceStat: true6, DruidConfiguration configuration class
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
@Configuration
public class DruidConfiguration {
private static final String DB_PREFIX = "spring.datasource";
@Bean
public ServletRegistrationBean statViewServlet(){
//Create servlet registration entity
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
//Set ip white list
servletRegistrationBean.addInitParameter("allow","127.0.0.1");
//Set ip blacklist. If allow and deny exist together, deny takes precedence over allow
servletRegistrationBean.addInitParameter("deny","192.168.0.19");
//Set up console administration users
servletRegistrationBean.addInitParameter("loginUsername","druid");
servletRegistrationBean.addInitParameter("loginPassword","123456");
//Can data be reset
servletRegistrationBean.addInitParameter("resetEnable","false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean statFilter(){
//Create filter
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
//Set filter filter path
filterRegistrationBean.addUrlPatterns("/*");
//Ignore the form of filtering
filterRegistrationBean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
//Solve that spring.datasource.filters=stat,wall,log4j cannot be registered normally
@ConfigurationProperties(prefix = DB_PREFIX)
class IDataSourceProperties {
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;
@Bean //Declare it as a Bean instance
@Primary //In the same DataSource, first use the annotated DataSource
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
//configuration
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
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;
}
}
}
