SpringBoot集成Druid数据库连接池

曾经终败给现在 2021-09-30 08:22 497阅读 0赞

##     有两种方式可以集成Druid。 ##

第一种：druid-spring-boot-starter模式，官方文档：[https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter][https_github.com_alibaba_druid_tree_master_druid-spring-boot-starter]，用maven引入相关依赖之后，直接在yml或者properties文件里就可以配置，springboot默认是没有引入这个starter的。

![20190729111248512.png][]

## 配置文件内容如下： ##

spring.datasource.url=jdbc:mysql://localhost:3306/mysql?useUnicode=true&characterEncoding=utf-8&useSSL=false  
spring.datasource.username=root  
spring.datasource.password=root  
spring.datasource.driver-class-name=com.mysql.jdbc.Driver  
spring.datasource.druid.initial-size=5 //线程池初始线程数  
spring.datasource.druid.max-active=20 //最大线程数  
spring.datasource.druid.min-idle=5 //最少保留线程数  
spring.datasource.druid.max-wait=60000  
spring.datasource.druid.pool-prepared-statements=  
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=   
spring.datasource.druid.max-open-prepared-statements= \#和上面的等价  
spring.datasource.druid.validation-query=SELECT 1  
spring.datasource.druid.validation-query-timeout=2000  
spring.datasource.druid.test-on-borrow=false  
spring.datasource.druid.test-on-return=false  
spring.datasource.druid.test-while-idle=true  
spring.datasource.druid.time-between-eviction-runs-millis=60000  
spring.datasource.druid.min-evictable-idle-time-millis=300000  
spring.datasource.druid.filters=stat,wall,log4j

第二种：不引入starter依赖的方式，参考文档：[https://github.com/alibaba/druid][https_github.com_alibaba_druid]，[https://github.com/alibaba/druid/wiki/常见问题][https_github.com_alibaba_druid_wiki]，首先引入maven依赖如下：

<dependency>  
            <groupId>com.alibaba</groupId>  
            <artifactId>druid</artifactId>  
            <version>1.1.10</version>  
        </dependency>

<dependency>  
            <groupId>mysql</groupId>  
            <artifactId>mysql-connector-java</artifactId>  
        </dependency>

##    yml配置如下： ##

spring:  
    datasource:  
        type: com.alibaba.druid.pool.DruidDataSource  
        driverClassName: com.mysql.jdbc.Driver  
        driver-class-name: com.mysql.jdbc.Driver  
        platform: mysql  
        url: jdbc:mysql://127.0.0.1:3306/tcbj-xxx?useUnicode=true&characterEncoding=utf-8&useSSL=false  
        username: root  
        password: root  
        initialSize: 5  
        minIdle: 5  
        maxActive: 20  
        maxWait: 60000  
        timeBetweenEvictionRunsMillis: 60000  
        minEvictableIdleTimeMillis: 300000  
        validationQuery: SELECT1FROMDUAL  
        testWhileIdle: true  
        testOnBorrow: false  
        testOnReturn: false  
        filters: stat,wall,log4j  
        logSlowSql: true

##    如果不生效，则配置一个DruidConfig配置类： ##

import com.alibaba.druid.pool.DruidDataSource;  
import com.alibaba.druid.support.http.StatViewServlet;  
import com.alibaba.druid.support.http.WebStatFilter;  
import org.slf4j.Logger;  
import org.slf4j.LoggerFactory;  
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 org.springframework.stereotype.Component;

import javax.sql.DataSource;  
import java.sql.SQLException;

@Configuration  
public class DruidConfiguration \{

private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);

private static final String DB\_PREFIX = "spring.datasource";

@Bean  
    public ServletRegistrationBean druidServlet() \{  
        logger.info("init Druid Servlet Configuration ");  
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/\*");  
        // IP白名单  
        servletRegistrationBean.addInitParameter("allow", "\*");  
        // IP黑名单(共同存在时，deny优先于allow)  
        servletRegistrationBean.addInitParameter("deny", "192.168.1.88");  
        //控制台管理用户  
        servletRegistrationBean.addInitParameter("loginUsername", "mydruid");  
        servletRegistrationBean.addInitParameter("loginPassword", "123456");  
        //是否能够重置数据 禁用HTML页面上的“Reset All”功能  
        servletRegistrationBean.addInitParameter("resetEnable", "false");  
        return servletRegistrationBean;  
    \}

@Bean  
    public FilterRegistrationBean filterRegistrationBean() \{  
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());  
        filterRegistrationBean.addUrlPatterns("/\*");  
        filterRegistrationBean.addInitParameter("exclusions", "\*.js,\*.gif,\*.jpg,\*.png,\*.css,\*.ico,/druid/\*");  
        return filterRegistrationBean;  
    \}

//解决 spring.datasource.filters=stat,wall,log4j 无法正常注册进去  
    @Component  
    @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     //声明其为Bean实例  
        @Primary  //在同样的DataSource中，首先使用被标注的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;  
        \}  
    \}

[https_github.com_alibaba_druid_tree_master_druid-spring-boot-starter]: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
[20190729111248512.png]: /images/20210923/a89e772fe9b544e38787a7e552cb5529.png
[https_github.com_alibaba_druid]: https://github.com/alibaba/druid
[https_github.com_alibaba_druid_wiki]: https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98