查看: 839|回复: 0

[Java代码] SpringBoot开发案例之配置Druid数据库连接池的示例

发表于 2018-4-14 08:00:02

前言

好久没有更新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依赖:

  1. <!-- Jpa -->
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-data-jpa</artifactId>
  5. </dependency>
  6. <!-- MySql -->
  7. <dependency>
  8. <groupId>mysql</groupId>
  9. <artifactId>mysql-connector-java</artifactId>
  10. </dependency>
  11. <!-- druid -->
  12. <dependency>
  13. <groupId>com.alibaba</groupId>
  14. <artifactId>druid</artifactId>
  15. <version>1.1.6</version>
  16. </dependency>
复制代码

配置application.properties:

  1. #数据源
  2. spring.datasource.url=jdbc:mysql://192.168.1.66:3306/spring_boot?characterEncoding=utf-8&useSSL=false
  3. spring.datasource.username=root
  4. spring.datasource.password=root
  5. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  6. spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
  7. # 初始化大小,最小,最大
  8. spring.datasource.initialSize=1
  9. spring.datasource.minIdle=3
  10. spring.datasource.maxActive=20
  11. # 配置获取连接等待超时的时间
  12. spring.datasource.maxWait=60000
  13. # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
  14. spring.datasource.timeBetweenEvictionRunsMillis=60000
  15. # 配置一个连接在池中最小生存的时间,单位是毫秒
  16. spring.datasource.minEvictableIdleTimeMillis=30000
  17. spring.datasource.validationQuery=select 'x'
  18. spring.datasource.testWhileIdle=true
  19. spring.datasource.testOnBorrow=false
  20. spring.datasource.testOnReturn=false
  21. # 打开PSCache,并且指定每个连接上PSCache的大小
  22. spring.datasource.poolPreparedStatements=true
  23. spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
  24. # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
  25. spring.datasource.filters=stat,wall,slf4j
  26. # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
  27. spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
复制代码

配置yml文件(与上二选一)

  1. spring:
  2. datasource:
  3. url: jdbc:mysql://192.168.1.66:3306/spring-boot?useUnicode=true&characterEncoding=utf-8&useSSL=false
  4. username: root
  5. password: root
  6. driver-class-name: com.mysql.jdbc.Driver
  7. platform: mysql
  8. type: com.alibaba.druid.pool.DruidDataSource
  9. # 下面为连接池的补充设置,应用到上面所有数据源中
  10. # 初始化大小,最小,最大
  11. initialSize: 1
  12. minIdle: 3
  13. maxActive: 20
  14. # 配置获取连接等待超时的时间
  15. maxWait: 60000
  16. # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
  17. timeBetweenEvictionRunsMillis: 60000
  18. # 配置一个连接在池中最小生存的时间,单位是毫秒
  19. minEvictableIdleTimeMillis: 30000
  20. validationQuery: select 'x'
  21. testWhileIdle: true
  22. testOnBorrow: false
  23. testOnReturn: false
  24. # 打开PSCache,并且指定每个连接上PSCache的大小
  25. poolPreparedStatements: true
  26. maxPoolPreparedStatementPerConnectionSize: 20
  27. # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
  28. filters: stat,wall,slf4j
  29. # 通过connectProperties属性来打开mergeSql功能;慢SQL记录
  30. connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
复制代码

配置Druid的监控统计功能

  1. import java.sql.SQLException;
  2. import javax.sql.DataSource;
  3. import org.slf4j.Logger;
  4. import org.slf4j.LoggerFactory;
  5. import org.springframework.boot.context.properties.ConfigurationProperties;
  6. import org.springframework.boot.web.servlet.FilterRegistrationBean;
  7. import org.springframework.boot.web.servlet.ServletRegistrationBean;
  8. import org.springframework.context.annotation.Bean;
  9. import org.springframework.context.annotation.Configuration;
  10. import com.alibaba.druid.pool.DruidDataSource;
  11. import com.alibaba.druid.support.http.StatViewServlet;
  12. import com.alibaba.druid.support.http.WebStatFilter;
  13. /**
  14. * 阿里数据库连接池 Druid配置
  15. * 创建者 柒
  16. * 创建时间 2018年3月15日
  17. */
  18. @Configuration
  19. public class DruidConfiguration {
  20. private static final Logger logger = LoggerFactory.getLogger(DruidConfiguration.class);
  21. private static final String DB_PREFIX = "spring.datasource";
  22. @Bean
  23. public ServletRegistrationBean druidServlet() {
  24. logger.info("init Druid Servlet Configuration ");
  25. ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
  26. // IP白名单 (没有配置或者为空,则允许所有访问)
  27. servletRegistrationBean.addInitParameter("allow", "");
  28. // IP黑名单(共同存在时,deny优先于allow)
  29. //servletRegistrationBean.addInitParameter("deny", "192.168.1.100");
  30. //控制台管理用户
  31. servletRegistrationBean.addInitParameter("loginUsername", "admin");
  32. servletRegistrationBean.addInitParameter("loginPassword", "admin");
  33. //是否能够重置数据 禁用HTML页面上的“Reset All”功能
  34. servletRegistrationBean.addInitParameter("resetEnable", "false");
  35. return servletRegistrationBean;
  36. }
  37. @Bean
  38. public FilterRegistrationBean filterRegistrationBean() {
  39. FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
  40. filterRegistrationBean.addUrlPatterns("/*");
  41. filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
  42. return filterRegistrationBean;
  43. }
  44. @ConfigurationProperties(prefix = DB_PREFIX)
  45. class IDataSourceProperties {
  46. private String url;
  47. private String username;
  48. private String password;
  49. private String driverClassName;
  50. private int initialSize;
  51. private int minIdle;
  52. private int maxActive;
  53. private int maxWait;
  54. private int timeBetweenEvictionRunsMillis;
  55. private int minEvictableIdleTimeMillis;
  56. private String validationQuery;
  57. private boolean testWhileIdle;
  58. private boolean testOnBorrow;
  59. private boolean testOnReturn;
  60. private boolean poolPreparedStatements;
  61. private int maxPoolPreparedStatementPerConnectionSize;
  62. private String filters;
  63. private String connectionProperties;
  64. [url=home.php?mod=space&uid=4377]@Bean[/url]
  65. public DataSource dataSource() {
  66. DruidDataSource datasource = new DruidDataSource();
  67. datasource.setUrl(url);
  68. datasource.setUsername(username);
  69. datasource.setPassword(password);
  70. datasource.setDriverClassName(driverClassName);
  71. //configuration
  72. datasource.setInitialSize(initialSize);
  73. datasource.setMinIdle(minIdle);
  74. datasource.setMaxActive(maxActive);
  75. datasource.setMaxWait(maxWait);
  76. datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
  77. datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
  78. datasource.setValidationQuery(validationQuery);
  79. datasource.setTestWhileIdle(testWhileIdle);
  80. datasource.setTestOnBorrow(testOnBorrow);
  81. datasource.setTestOnReturn(testOnReturn);
  82. datasource.setPoolPreparedStatements(poolPreparedStatements);
  83. datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
  84. try {
  85. datasource.setFilters(filters);
  86. } catch (SQLException e) {
  87. System.err.println("druid configuration initialization filter: " + e);
  88. }
  89. datasource.setConnectionProperties(connectionProperties);
  90. return datasource;
  91. }
  92. public String getUrl() {
  93. return url;
  94. }
  95. public void setUrl(String url) {
  96. this.url = url;
  97. }
  98. public String getUsername() {
  99. return username;
  100. }
  101. public void setUsername(String username) {
  102. this.username = username;
  103. }
  104. public String getPassword() {
  105. return password;
  106. }
  107. public void setPassword(String password) {
  108. this.password = password;
  109. }
  110. public String getDriverClassName() {
  111. return driverClassName;
  112. }
  113. public void setDriverClassName(String driverClassName) {
  114. this.driverClassName = driverClassName;
  115. }
  116. public int getInitialSize() {
  117. return initialSize;
  118. }
  119. public void setInitialSize(int initialSize) {
  120. this.initialSize = initialSize;
  121. }
  122. public int getMinIdle() {
  123. return minIdle;
  124. }
  125. public void setMinIdle(int minIdle) {
  126. this.minIdle = minIdle;
  127. }
  128. public int getMaxActive() {
  129. return maxActive;
  130. }
  131. public void setMaxActive(int maxActive) {
  132. this.maxActive = maxActive;
  133. }
  134. public int getMaxWait() {
  135. return maxWait;
  136. }
  137. public void setMaxWait(int maxWait) {
  138. this.maxWait = maxWait;
  139. }
  140. public int getTimeBetweenEvictionRunsMillis() {
  141. return timeBetweenEvictionRunsMillis;
  142. }
  143. public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
  144. this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
  145. }
  146. public int getMinEvictableIdleTimeMillis() {
  147. return minEvictableIdleTimeMillis;
  148. }
  149. public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
  150. this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
  151. }
  152. public String getValidationQuery() {
  153. return validationQuery;
  154. }
  155. public void setValidationQuery(String validationQuery) {
  156. this.validationQuery = validationQuery;
  157. }
  158. public boolean isTestWhileIdle() {
  159. return testWhileIdle;
  160. }
  161. public void setTestWhileIdle(boolean testWhileIdle) {
  162. this.testWhileIdle = testWhileIdle;
  163. }
  164. public boolean isTestOnBorrow() {
  165. return testOnBorrow;
  166. }
  167. public void setTestOnBorrow(boolean testOnBorrow) {
  168. this.testOnBorrow = testOnBorrow;
  169. }
  170. public boolean isTestOnReturn() {
  171. return testOnReturn;
  172. }
  173. public void setTestOnReturn(boolean testOnReturn) {
  174. this.testOnReturn = testOnReturn;
  175. }
  176. public boolean isPoolPreparedStatements() {
  177. return poolPreparedStatements;
  178. }
  179. public void setPoolPreparedStatements(boolean poolPreparedStatements) {
  180. this.poolPreparedStatements = poolPreparedStatements;
  181. }
  182. public int getMaxPoolPreparedStatementPerConnectionSize() {
  183. return maxPoolPreparedStatementPerConnectionSize;
  184. }
  185. public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
  186. this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
  187. }
  188. public String getFilters() {
  189. return filters;
  190. }
  191. public void setFilters(String filters) {
  192. this.filters = filters;
  193. }
  194. public String getConnectionProperties() {
  195. return connectionProperties;
  196. }
  197. public void setConnectionProperties(String connectionProperties) {
  198. this.connectionProperties = connectionProperties;
  199. }
  200. }
  201. }
复制代码

启动应用,访问地址: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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持程序员之家。



回复

使用道具 举报