Analysis of usage configuration of Druid Connection pool under Spring boot

Introduction: A number of available connection pools are provided by default under Spring Boot, druid from an open source connection pool in the Ali Department, and provides excellent monitoring capabilities beyond the connection pool, which explains how to integrate with spring boot.

1. Environmental description

Spring Boot 1.4.0.RELEASE, JDK 1.8

2. Druid Introduction

Druid is a JDBC component that includes three parts:
Druiddriver Agent driver, can provide the plug-in system based on Filter-chain mode.   Druiddatasource Efficient and manageable database connection pool. Sqlparser

What Druid can do.
can monitor database access performance, the Druid built-in provides a powerful Statfilter plug-in to detail SQL performance, which is useful for online analysis of database access performance. Replace DBCP and C3P0.   Druid provides an efficient, powerful, scalable database connection pool. Database password encryption. Writing the database password directly in the configuration file is bad behavior and can lead to security problems.    Druiddruiver and Druiddatasource both support PasswordCallback. SQL execution log, Druid provides different logfilter, can support common-logging, log4j and Jdklog, you can choose the corresponding logfilter as needed, monitor the database access you have applied.
To extend JDBC, if you want to have programming requirements for the JDBC layer, you can easily write the JDBC layer extensions through the Filter-chain mechanism provided by Druid.

Project Address: Https://github.com/alibaba/druid

3. Spring Boot and Druid integration

MySQL Driver Driver Package:

<dependency>
			<groupId>mysql</groupId>
			<artifactid>mysql-connector-java</ artifactid>
			<scope>runtime</scope>
		</dependency>

The JPA dependency pack for Spring boot:

<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId> Spring-boot-starter-data-jpa</artifactid>
		</dependency>

Ali's Druid Dependency pack:

<dependency>
			<groupId>com.alibaba</groupId>
			<artifactid>druid</artifactid >
			<version>1.0.25</version>
		</dependency>

Application.properties configuration information in Spring boot:

# driver configuration information Spring.datasource.type=com.alibaba.druid.pool.druiddatasource Spring.datasource.url = Jdbc:mysql://127.0.0.1:3306/mealsystem?useunicode=true&characterencoding=utf-8 Spring.datasource.username =
Root Spring.datasource.password = 123456 Spring.datasource.driverClassName = Com.mysql.jdbc.Driver #连接池的配置信息
Spring.datasource.initialsize=5 spring.datasource.minidle=5 spring.datasource.maxactive=20
spring.datasource.maxwait=60000 spring.datasource.timebetweenevictionrunsmillis=60000
spring.datasource.minevictableidletimemillis=300000 Spring.datasource.validationquery=select 1 from DUAL
Spring.datasource.testwhileidle=true Spring.datasource.testonborrow=false Spring.datasource.testonreturn=false
Spring.datasource.poolpreparedstatements=true spring.datasource.maxpoolpreparedstatementperconnectionsize=20 Spring.datasource.filters=stat,wall,log4j spring.datasource.connectionproperties=druid.stat.mergesql=true; druid.stat.slowsqlmillis=5000 

There is no problem driving configuration information in spring Boot1.4.0, but the connection pool configuration information no longer supports the configuration items here, that is, the corresponding connection pool cannot be directly supported through the configuration items, and these configuration items listed here can be implemented through custom datasource.

Currently, the default supported connection pool in spring boot is DBCP,DBCP2, Tomcat, and Hikari three kinds of connection pools.

Because Druid is temporarily out of direct support in spring Bootz, configuration information needs to be customized:

@Configuration public class Druiddbconfig {private Logger Logger = Loggerfactory.getlogger (Druiddbconfig.class);
    
    @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.timebetweenevictionrunsmillis}") private int timebetweenevictionrunsmillis;
    
    @Value ("${spring.datasource.minevictableidletimemillis}") private int minevictableidletimemillis; @Value ("${spring.datasource.vAlidationquery} ") Private String validationquery;
    
    @Value ("${spring.datasource.testwhileidle}") Private Boolean testwhileidle;
    
    @Value ("${spring.datasource.testonborrow}") Private Boolean testonborrow;
    
    @Value ("${spring.datasource.testonreturn}") Private Boolean Testonreturn;
    
    @Value ("${spring.datasource.poolpreparedstatements}") Private Boolean poolpreparedstatements; @Value ("${spring.datasource.maxpoolpreparedstatementperconnectionsize}") Private int
    
    Maxpoolpreparedstatementperconnectionsize;
    
    @Value ("${spring.datasource.filters}") Private String filters;
    
    @Value ("{spring.datasource.connectionProperties}") Private String connectionproperties; @Bean//Declare that it is a Bean instance @Primary//In the same DataSource, first use the DataSource public DataSource DataSource () {Druidda
    	
    	Tasource DataSource = new Druiddatasource ();
    	Datasource.seturl (This.dburl);
 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) {logger.error ("Druid Configuration Initialization Filter", e); } datasource.setconnectionproperties (ConnectionpropErties);
    return datasource;
 }
}

The Druiddbconfig class is @configuration annotated as the configuration information, and the DataSource object is @bean declared and managed by the spring container, @ Primary says the DataSource defined here will cover the datasource of other sources.

# The following supplemental settings for connection pooling are applied to all data sources above

# Initialize size, MIN, max

Spring.datasource.initialsize=5

Spring.datasource.minidle=5

Spring.datasource.maxactive=20

# Configure time to get connection wait timeout

spring.datasource.maxwait=60000

# How long does the configuration interval take to detect and detect idle connections that need to be closed, in milliseconds

spring.datasource.timebetweenevictionrunsmillis=60000

# Configure the minimum time that a connection is to survive in the pool, in milliseconds

spring.datasource.minevictableidletimemillis=300000

Spring.datasource.validationquery=select 1 from DUAL

Spring.datasource.testwhileidle=true

Spring.datasource.testonborrow=false

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 monitoring of statistical interception of the filters, removed after the monitoring interface SQL can not be counted, ' wall ' for firewalls

spring.datasource.filters=stat,wall,log4j

# Turn on the Mergesql function by Connectproperties property; Slow SQL record

spring.datasource.connectionproperties=druid.stat.mergesql=true;druid.stat.slowsqlmillis=5000

# Combine monitoring data for multiple Druiddatasource

#spring. datasource.useglobaldatasourcestat=true

Note that the old spring boot version of Spring.datasource.type is not recognized. To configure the StatView servlet:

Implementation Class of filter:

Import Javax.servlet.annotation.WebFilter;
Import Javax.servlet.annotation.WebInitParam;

Import Com.alibaba.druid.support.http.WebStatFilter;

@WebFilter (filtername= "Druidwebstatfilter", urlpatterns= "/*",
    initparams={
        @WebInitParam (name= " Exclusions ", value=" *.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/* ")//Ignore resource
   }
) public
class Druidstatfilter extends Webstatfilter {

}

Statviewservlet:

Import Javax.servlet.annotation.WebInitParam;
Import Javax.servlet.annotation.WebServlet;

Import Com.alibaba.druid.support.http.StatViewServlet;

@WebServlet (urlpatterns= "/druid/*",
    initparams={
         @WebInitParam (name= "Allow", value= " 127.0.0.1,192.168.163.1 "),//IP White list (is not configured or empty, allow all access)
         @WebInitParam (name=" Deny ", value=" 192.168.1.73 "),// IP blacklist (when in common, deny takes precedence over allow)
         @WebInitParam (name= "Loginusername", value= "admin"),//username
         @WebInitParam (name= " Loginpassword ", value=" 123456 "),//Password
         @WebInitParam (name=" resetenable ", value=" false ")//disable" Reset all "on HTML page Feature
}) public
class Druidstatviewservlet extends Statviewservlet {
	private static final long Serialversionuid = -2688872071445249539l;

}

These two classes are equivalent to declaring a servlet in Web.xml, equivalent to the following configuration information (Web.xml):

<servlet>  
        <servlet-name>DruidStatView</servlet-name>  
        <servlet-class> com.alibaba.druid.support.http.statviewservlet</servlet-class>  
    </servlet>  
    < servlet-mapping>  
        <servlet-name>DruidStatView</servlet-name>  
        <url-pattern>/druid/* </url-pattern>  
    </servlet-mapping>  

Configuration information for filter:

<filter>  
        <filter-name>DruidWebStatFilter</filter-name>  
        <filter-class> com.alibaba.druid.support.http.webstatfilter</filter-class>  
        <init-param>  
            <param-name >exclusions</param-name>  
            <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</ param-value>  
        </init-param>  
      </filter>  
      <filter-mapping>  
        <filter-name> druidwebstatfilter</filter-name>  
        <url-pattern>/*</url-pattern>  
      </filter-mapping >  

Then the corresponding configuration work is completed, the direct start can see the corresponding application.

4. Run the interface and introduce

Access Address: http://192.168.163.1:8080/druid/index.html

5. Reference http://blog.csdn.net/xiaoyu411502/article/details/51392237 http://stackoverflow.com/questions/32833641/ Not-able-to-set-spring-datasource-type