在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统计等监控。效果图如下: