SpringBoot開發案例之配置Druid數據庫連接池的示例

前言

好久沒有更新Spring Boot系列文章，你說忙麼？也可能是，前段時間的關注點也許在其他方面了，最近項目中需要開發小程序，正好採用Spring Boot實現一個後端服務，後面會把相關的代碼案例分享出來，不至於大家做小程序後端服務的時候一頭霧水。

在Spring Boot下默認提供了若干種可用的連接池(dbcp,dbcp2, tomcat, hikari)，當然並不支持Druid，Druid來自於阿里系的一個開源連接池，它提供了非常優秀的監控功能，下面跟大家分享一下如何與Spring Boot集成。

版本環境

Spring Boot 1.5.2.RELEASE、Druid 1.1.6、JDK1.7

系統集成

添加pom.xml依賴:

 <!-- Jpa --><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId></dependency><!-- MySql --><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId></dependency><!-- druid --><dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.6</version></dependency>

配置application.properties：

 #數據源spring.datasource.url=jdbc:mysql://192.168.1.66:3306/spring_boot?characterEncoding=utf-8&useSSL=falsespring.datasource.username=rootspring.datasource.password=rootspring.datasource.driver-class-name=com.mysql.jdbc.Driverspring.datasource.type=com.alibaba.druid.pool.DruidDataSource# 初始化大小，最小，最大spring.datasource.initialSize=1spring.datasource.minIdle=3spring.datasource.maxActive=20# 配置獲取連接等待超時的時間spring.datasource.maxWait=60000# 配置間隔多久才進行一次檢測，檢測需要關閉的空閒連接，單位是毫秒spring.datasource.timeBetweenEvictionRunsMillis=60000# 配置一個連接在池中最小生存的時間，單位是毫秒spring.datasource.minEvictableIdleTimeMillis=30000spring.datasource.validationQuery=select 'x'spring.datasource.testWhileIdle=truespring.datasource.testOnBorrow=falsespring.datasource.testOnReturn=false# 打開PSCache，並且指定每個連接上PSCache的大小spring.datasource.poolPreparedStatements=truespring.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

配置yml文件(與上二選一)

 spring: datasource: url: jdbc:mysql://192.168.1.66:3306/spring-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false username: root password: root driver-class-name: com.mysql.jdbc.Driver platform: mysql type: com.alibaba.druid.pool.DruidDataSource # 下面為連接池的補充設置，應用到上面所有數據源中# 初始化大小，最小，最大initialSize: 1 minIdle: 3 maxActive: 20 # 配置獲取連接等待超時的時間maxWait: 60000 # 配置間隔多久才進行一次檢測，檢測需要關閉的空閒連接，單位是毫秒timeBetweenEvictionRunsMillis: 60000 # 配置一個連接在池中最小生存的時間，單位是毫秒minEvictableIdleTimeMillis: 30000 validationQuery: select 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false # 打開PSCache，並且指定每個連接上PSCache的大小poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 # 配置監控統計攔截的filters，去掉後監控界面sql無法統計，'wall'用於防火牆filters: stat,wall,slf4j # 通過connectProperties屬性來打開mergeSql功能；慢SQL記錄connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

配置Druid的監控統計功能

import java.sql.SQLException;import javax.sql.DataSource;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 com.alibaba.druid.pool.DruidDataSource;import com.alibaba.druid.support.http.StatViewServlet;import com.alibaba.druid.support.http.WebStatFilter;/** * 阿里數據庫連接池Druid配置* 創建者柒* 創建時間2018年3月15日*/@Configurationpublic 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", "admin"); //是否能夠重置數據禁用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; } }}

啟動應用，訪問地址：http://localhost:8080/druid/， 輸入配置的賬號密碼登錄之後，即可查看數據源及SQL統計等監控。效果圖如下：

當然，阿里巴巴也提供了Druid的SpringBoot集成版(druid-spring-boot-starter)，可參考以下鏈接。

參考：

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

https://github.com/alibaba/druid/wiki

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。