SpringBoot开发案例之配置Druid数据库连接池的示例

时间：2022-03-23 16:08:35

前言

好久没有更新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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- 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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#数据源
spring.datasource.url=jdbc:mysql://192.168.1.66:3306/spring_boot?characterEncoding=utf-8&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 初始化大小，最小，最大
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

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
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的监控统计功能

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
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日
 */
@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", "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统计等监控。效果图如下：

SpringBoot开发案例之配置Druid数据库连接池的示例

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

参考：

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

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：https://blog.52itstyle.com/archives/2544

标签：

相关文章