Springboot project configuration Druid database connection pool, and monitor statistics function

pom.xml configuration Dependencies

<!--Https://mvnrepository.com/artifact/com.alibaba/druid -<Dependency>     <groupId>Com.alibaba</groupId>     <Artifactid>Druid</Artifactid>     <version>1.1.6</version></Dependency> 

resource file configuration information

whether it is a yml file or a properties file, use the following configuration

# Database Access configuration spring.datasource.type:com.alibaba.druid.pool.druiddatasourcespring.datasource.url:jdbc:mysql:// 10.170.1.16:3306/cispapi?useunicode=true&characterencoding=utf-8spring.datasource.username:rootspring.datasource.password:sinoway123spring.datasource.driverclassname: com.mysql.jdbc.driver# the following supplemental settings for connection pooling, applied to all data sources above # initialization size, minimum, maximum spring.datasource.initialSize: 5spring.datasource.minidle:5spring.datasource.maxactive:20# configuration Gets the time of the connection wait timeout spring.datasource.maxwait:60000# How long does the configuration interval take to detect the idle connection that needs to be closed, in milliseconds spring.datasource.timebetweenevictionrunsmillis:60000# to configure the minimum time for a connection to survive in the pool, Units are in milliseconds Spring.datasource.minevictableidletimemillis:300000spring.datasource.validationquery:select 1 from DUALspring.datasource.testWhileIdle:truespring.datasource.testOnBorrow:falsespring.datasource.testOnReturn: false# opens Pscache, and specifies the size of the Pscache on each connection spring.datasource.poolPreparedStatements: truespring.datasource.maxpoolpreparedstatementperconnectionsize:20# Configuration Monitoring Statistics interception filters, removed after the monitoring interface SQL can not be counted, ' wall ' For firewall spring.datasource.filters:stat,wall,log4jspring.datasource.logslowsql:true# Open the Mergesql function with the Connectproperties property; slow SQL Records spring.datasource.connectionproperties:druid.stat.mergesql=true;druid.stat.slowsqlmillis=5000 

Data Source Configuration class

Importjava.sql.SQLException;ImportJavax.sql.DataSource;ImportOrg.slf4j.Logger;Importorg.slf4j.LoggerFactory;ImportOrg.springframework.beans.factory.annotation.Value;ImportOrg.springframework.boot.web.servlet.FilterRegistrationBean;ImportOrg.springframework.boot.web.servlet.ServletRegistrationBean;ImportOrg.springframework.context.annotation.Bean;Importorg.springframework.context.annotation.Configuration;Importorg.springframework.context.annotation.Primary;ImportCom.alibaba.druid.pool.DruidDataSource;ImportCom.alibaba.druid.support.http.StatViewServlet;ImportCom.alibaba.druid.support.http.WebStatFilter; @Configuration Public classDruiddbconfig {PrivateLogger Logger = Loggerfactory.getlogger (druiddbconfig.class); @Value ("${spring.datasource.url}")    PrivateString Dburl; @Value ("${spring.datasource.username}")    PrivateString username; @Value ("${spring.datasource.password}")    PrivateString password; @Value ("${spring.datasource.driverclassname}")    PrivateString Driverclassname; @Value ("${spring.datasource.initialsize}")    Private intinitialsize; @Value ("${spring.datasource.minidle}")    Private intMinidle; @Value ("${spring.datasource.maxactive}")    Private intmaxactive; @Value ("${spring.datasource.maxwait}")    Private intmaxwait; @Value ("${spring.datasource.timebetweenevictionrunsmillis}")    Private intTimebetweenevictionrunsmillis; @Value ("${spring.datasource.minevictableidletimemillis}")    Private intMinevictableidletimemillis; @Value ("${spring.datasource.validationquery}")    PrivateString Validationquery; @Value ("${spring.datasource.testwhileidle}")    Private BooleanTestwhileidle; @Value ("${spring.datasource.testonborrow}")    Private BooleanTestonborrow; @Value ("${spring.datasource.testonreturn}")    Private BooleanTestonreturn; @Value ("${spring.datasource.poolpreparedstatements}")    Private Booleanpoolpreparedstatements; @Value ("${spring.datasource.maxpoolpreparedstatementperconnectionsize}")    Private intmaxpoolpreparedstatementperconnectionsize; @Value ("${spring.datasource.filters}")    PrivateString Filters; @Value ("{spring.datasource.connectionProperties}")    PrivateString connectionproperties; @Value ("${spring.datasource.logslowsql}")    PrivateString Logslowsql; //Configuring the Monitoring statistics feature//Access Pathhttp://127.0.0.1: 8081/cisp/druid/login.html//1. Configuring the servlet@Bean PublicServletregistrationbean Druidservlet () {Servletregistrationbean reg=NewServletregistrationbean (); Reg.setservlet (NewStatviewservlet ()); Reg.addurlmappings ("/druid/*"); Reg.addinitparameter ("Loginusername", username);//user name can also be set, and directly write deadReg.addinitparameter ("Loginpassword", password);//password can also be set, and directly write deadReg.addinitparameter ("Logslowsql", logslowsql);//Slow SQL RecordsReg.addinitparameter ("Allow", "101.6.244.30");//IP Whitelist (no configuration or null, all access is allowed, if multiple configurations are separated by commas)Reg.addinitparameter ("Resetenable", "false");//disable the "Reset All" feature on HTML pages        returnreg; }    //2. Configure the filter@Bean PublicFilterregistrationbean Filterregistrationbean () {Filterregistrationbean Filterregistrationbean=NewFilterregistrationbean (); Filterregistrationbean.setfilter (NewWebstatfilter ()); Filterregistrationbean.addurlpatterns ("/*"); Filterregistrationbean.addinitparameter ("Exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//Ignore ResourcesFilterregistrationbean.addinitparameter ("Profileenable", "true"); returnFilterregistrationbean; } @Bean//declare it as a bean instance@Primary//in the same datasource, first use the labeled DataSource     PublicDataSource DataSource () {Druiddatasource DataSource=NewDruiddatasource (); Datasource.seturl ( This. Dburl);        Datasource.setusername (username);        Datasource.setpassword (password);        Datasource.setdriverclassname (Driverclassname); //Configurationdatasource.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); returnDataSource; }}

then start the project and visit:

Http://IP:port/CISP/druid/login.html

Enter the user name and password you set

Click Sign In

After a database query is made in the project,

Here you can view the configured connection pool, as well as database related information

Frequently asked questions, you can also visit

Https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98

Springboot project configuration Druid database connection pool, and monitor statistics function