26.数据源配置和自动管理

 

项目工程一:

项目依赖

pom.xml

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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>12_springboot_datasource</artifactId>
<version>1.0</version>
<name>12_springboot_datasource</name>
<description>Spring Boot data source</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

<!-- 引入JDBC和MySQL的依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>

<!-- 引入dbcp2依赖 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
</dependency>

<!-- durid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.10</version>
</dependency>


</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

1.SpringBoot默认使用的数据源【HikariDataSource】

application.yml

不需要指定spring.datasource.type

1
2
3
4
5
6
7
# 数据源配置信息
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo_filesystem
username: root
password: 123456

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.demo;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ApplicationTests {

@Autowired
private DataSource dataSource;

@Test
void contextLoads() {
System.out.println("dataSource = " + dataSource);
// dataSource = HikariDataSource (null)
}
}

2.Spring自带的数据源【DriverManagerDataSource】

application.yml

指定spring.datasource.type=DriverManagerDataSource

1
2
3
4
5
6
7
8
9
# 数据源配置信息
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo_filesystem
username: root
password: 123456
# 指定数据源的类型
type: org.springframework.jdbc.datasource.DriverManagerDataSource

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.demo;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ApplicationTests {

@Autowired
private DataSource dataSource;

@Test
void contextLoads() {
System.out.println("dataSource = " + dataSource);
// dataSource = org.springframework.jdbc.datasource.DriverManagerDataSource@97d0c06
}
}

3.SpringBoot使用dbcp2数据源

application.yml

指定spring.datasource.type=dbcp2

1
2
3
4
5
6
7
8
9
# 数据源配置信息
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo_filesystem
username: root
password: 123456
# 指定数据源的类型
type: org.apache.commons.dbcp2.BasicDataSource

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
package com.example.demo;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ApplicationTests {

@Autowired
private DataSource dataSource;

@Test
void contextLoads() {
System.out.println("dataSource = " + dataSource);
// dataSource = org.apache.commons.dbcp2.BasicDataSource@d5d5353
}
}

4.SpringBoot使用druid数据源(自定义starter),并配置监控页面

创建自定义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
package com.example.demo.config;

import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;

@Configuration
@ConditionalOnClass(value = {DruidDataSource.class})
public class DruidAutoConfiguration {

@Bean(initMethod = "init")
@ConfigurationProperties(prefix = "druid.datasource")
public DruidDataSource druidDataSource() {
DruidDataSource druidDataSource = new DruidDataSource();
return druidDataSource;
}

/* --------------- 以下为配置监控页面 --------------- */

private String urlMappings = "/druid/*";

private String loginUsername = "admin";
private String loginPassword ="123456";

private String allow ="";
private String deny ="";

/*
* allow和deny填写ip地址
* ip地址之间用,分隔,不能加空格
* localhost作为ip地址不合法
* 请求访问的url写localhost,该请求不会被拦截,一直有效
*/
// private String allow ="127.0.0.1, 192.168.33.10, 192.168.22.130";
// private String deny ="localhost";

// 2022-06-09 22:50:33.034 ERROR 14136 --- [nio-8080-exec-1] c.a.druid.support.http.ResourceServlet : initParameter config error, allow : 127.0.0.1, 192.168.33.10, 192.168.22.130

// java.lang.IllegalArgumentException: Invalid IP Address [ 192.168.33.10]
// at com.alibaba.druid.support.http.util.IPAddress.parseIPAddress(IPAddress.java:169) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPAddress.<init>(IPAddress.java:30) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPRange.parseRange(IPRange.java:100) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPRange.<init>(IPRange.java:41) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.ResourceServlet.initAuthEnv(ResourceServlet.java:84) [druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.ResourceServlet.init(ResourceServlet.java:54) [druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.StatViewServlet.init(StatViewServlet.java:67) [druid-1.2.10.jar:1.2.10]
// at javax.servlet.GenericServlet.init(GenericServlet.java:158) [tomcat-embed-core-9.0.63.jar:4.0.FR]
// at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1164) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:804) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:128) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at java.lang.Thread.run(Thread.java:748) [na:1.8.0_271]
// Caused by: java.lang.NumberFormatException: For input string: " 192"
// at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[na:1.8.0_271]
// at java.lang.Integer.parseInt(Integer.java:569) ~[na:1.8.0_271]
// at java.lang.Integer.parseInt(Integer.java:615) ~[na:1.8.0_271]
// at com.alibaba.druid.support.http.util.IPAddress.parseIPAddress(IPAddress.java:141) ~[druid-1.2.10.jar:1.2.10]
// ... 25 common frames omitted

// 2022-06-09 22:50:33.035 ERROR 14136 --- [nio-8080-exec-1] c.a.druid.support.http.ResourceServlet : initParameter config error, deny : localhost

// java.lang.IllegalArgumentException: Invalid IP Address [localhost]
// at com.alibaba.druid.support.http.util.IPAddress.parseIPAddress(IPAddress.java:150) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPAddress.<init>(IPAddress.java:30) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPRange.parseRange(IPRange.java:100) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.util.IPRange.<init>(IPRange.java:41) ~[druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.ResourceServlet.initAuthEnv(ResourceServlet.java:104) [druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.ResourceServlet.init(ResourceServlet.java:54) [druid-1.2.10.jar:1.2.10]
// at com.alibaba.druid.support.http.StatViewServlet.init(StatViewServlet.java:67) [druid-1.2.10.jar:1.2.10]
// at javax.servlet.GenericServlet.init(GenericServlet.java:158) [tomcat-embed-core-9.0.63.jar:4.0.FR]
// at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1164) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardWrapper.allocate(StandardWrapper.java:804) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:128) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:890) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1743) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.63.jar:9.0.63]
// at java.lang.Thread.run(Thread.java:748) [na:1.8.0_271]


/*
* 配置监控页面
*/
@Bean
public ServletRegistrationBean<StatViewServlet> servletRegistrationBeanStatViewServlet() {
// 创建servlet
StatViewServlet statViewServlet = new StatViewServlet();
// 创建ServletRegistrationBean
ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<>();
// 注入servlet
servletRegistrationBean.setServlet(statViewServlet);
// 注入url
servletRegistrationBean.addUrlMappings(urlMappings);
// 注入属性
servletRegistrationBean.addInitParameter("loginUsername", loginUsername);
servletRegistrationBean.addInitParameter("loginPassword", loginPassword);
servletRegistrationBean.addInitParameter("allow", allow);
servletRegistrationBean.addInitParameter("deny", deny);

return servletRegistrationBean;
}
}

application.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
# druid数据源配置
druid:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo_filesystem
username: root
password: 123456

initial-size: 3
min-idle: 5
max-active: 100
max-wait: 10
validation-query: select 'x'

测试类

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
package com.example.demo;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ApplicationTests {

@Autowired
private DataSource dataSource;

@Test
void contextLoads() {
System.out.println("dataSource = " + dataSource);
/*
dataSource = {
CreateTime:"2022-06-12 22:56:16",
ActiveCount:0,
PoolingCount:3,
CreateCount:3,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
{ID:591247593, ConnectTime:"2022-06-12 22:56:17", UseCount:0, LastActiveTime:"2022-06-12 22:56:17"},
{ID:1086508626, ConnectTime:"2022-06-12 22:56:17", UseCount:0, LastActiveTime:"2022-06-12 22:56:17"},
{ID:1614238, ConnectTime:"2022-06-12 22:56:17", UseCount:0, LastActiveTime:"2022-06-12 22:56:17"}
]
}
2022-06-12 22:56:18.342 INFO 13420 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closing ...
2022-06-12 22:56:18.346 INFO 13420 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closed
*/
}
}

访问监控页面

1.运行Application.java启动类

1
2
3
4
5
6
7
8
9
10
11
12
13
package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}

}

2.浏览器访问: http://127.0.0.1:8080/druid/

5.SpringBoot使用druid数据源(引入官方starter),并配置监控页面

项目工程二:

项目依赖:

pom.xml

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
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.0</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>13_springboot_druid</artifactId>
<version>1.0</version>
<name>13_springboot_druid</name>
<description>Spring Boot druid</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>

<!-- druid-spring-boot-starter -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.9</version>
</dependency>

</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

</project>

application.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# 数据源配置信息
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo_filesystem
username: root
password: 123456
initial-size: 3
min-idle: 2
max-active: 10
max-wait: 15000

stat-view-servlet:
login-username: admin
login-password: 123456
# allow和deny可以为空
allow: 192.168.10.25,127.0.0.1,192.168.30.23,192.168.5.26
deny:
url-pattern: /druid/*
# 默认为false,不开启
enabled: true

访问监控页面

1.运行Application.java启动类

1
2
3
4
5
6
7
8
9
10
11
12
13
package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}

}

2.浏览器访问: http://127.0.0.1:8080/druid/