Spring Boot 使用Druid連接配接池

1、在pom.xml下<dependencies>添加項目依賴：

    <dependency>

        <groupId>com.alibaba</groupId>

        <artifactId>druid-spring-boot-starter</artifactId>

        <version>1.1.22</version>

    </dependency>

2、在application.properties檔案裡添加：

spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

spring.datasource.username=root

spring.datasource.password=123456

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

spring.thymeleaf.content-type=text/html

#關閉 Thymeleaf 的緩存開發過程中無需重新開機

spring.thymeleaf.cache=false

#設定thymeleaf頁面的編碼

spring.thymeleaf.encoding=UTF-8

spring.thymeleaf.mode=HTML

#設定thymeleaf頁面的字尾

spring.thymeleaf.suffix=.html

# 定位模闆的目錄

spring.thymeleaf.prefix=classpath:/templates/

# 初始化大小，最小，最大

spring.datasource.initialSize=1

spring.datasource.minIdle=3

spring.datasource.maxActive=20

# 配置擷取連接配接等待逾時的時間

spring.datasource.maxWait=60000

# 配置間隔多久才進行一次檢測，檢測需要關閉的空閑連接配接，機關是毫秒

spring.datasource.timeBetweenEvictionRunsMillis=60000

# 配置一個連接配接在池中最小生存的時間，機關是毫秒

spring.datasource.minEvictableIdleTimeMillis=30000

spring.datasource.validationQuery=select 'x'

spring.datasource.testWhileIdle=true

spring.datasource.testOnBorrow=false

spring.datasource.testOnReturn=false

# 打開PSCache，并且指定每個連接配接上PSCache的大小

spring.datasource.poolPreparedStatements=true

spring.datasource.maxPoolPreparedStatementPerConnectionSize=20

# 配置監控統計攔截的filters，去掉後監控界面sql無法統計，'wall'用于防火牆

spring.datasource.filters=stat,wall,slf4j

# 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄

spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# StatViewServlet配置，說明請參考Druid Wiki，配置_StatViewServlet配置

# 是否啟用StatViewServlet監控頁面，預設值為false考慮到安全問題預設并未啟動，如需啟用建議設定密碼或白名單以保障安全

spring.datasource.druid.stat-view-servlet.enabled=true 

spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*

## 禁止手動重置監控資料

spring.datasource.druid.stat-view-servlet.reset-enable=false

mybatis.mapper-locations=classpath:mapper/*.xml

3、建立DruidConfiguration配置類：

package com.example.testssm.config;

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 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.100");

        //控制台管理使用者

        servletRegistrationBean.addInitParameter("loginUsername", "admin");

        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;

    }

    @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

        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;

        }

    }

}

4、擷取Druid的監控資料以JSON的形式暴露出去

    @RestController

    public class DruidStatController {

        @GetMapping("/druid/stat")

        public Object druidStat(){

            // DruidStatManagerFacade#getDataSourceStatDataList 該方法可以擷取所有資料源的監控資料，除此之外 DruidStatManagerFacade 還提供了一些其他方法，你可以按需選擇使用。

            return DruidStatManagerFacade.getInstance().getDataSourceStatDataList();

        }

    }

5、Druid Monitor Web UI 位址：

通路：http://localhost:8080/druid/   登入名：admin   密碼：123456