准备
jdk 1.8
maven 3.0
idea
mysql
springBoot 2.2.7
初始化Mysql:
依赖
1
2
3
4
5
6
7
8
9
10
| <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>
|
配置
1
2
3
4
5
| datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/boot
username: qinnan
password: 123456
|
实体类
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
| package com.qn.eneity;
public class Account {
private int id ;
private String name ;
private double money;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", name='" + name + '\'' +
", money=" + money +
'}';
}
}
|
实现类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| package com.qn.service;
import com.qn.eneity.Account;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class AccountService {
@Autowired
JdbcTemplate jdbcTemplate;
public List<Account> getAll() {
return jdbcTemplate.query("SELECT * FROM account", new Object[]{},new BeanPropertyRowMapper(Account.class));
}
}
|
测试
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| package com.qn.service;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@SpringBootTest
@RunWith(SpringRunner.class)
class AccountServiceTest {
@Autowired
AccountService accountService;
@Test
void getAll() {
accountService.getAll().forEach(account -> System.out.println(account.toString()));
}
}
|
连接池
我们可以看到这里的连接池用的是 HikariPool，这是springBoot适配的连接池，我们也可以用自己的连接池：
1
2
3
4
5
| <dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
|
配置：
1
2
3
4
5
6
7
| Spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/boot
username: qinnan
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
|
启动测试：
监控
用 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
| Spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/boot
username: qinnan
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
filters: stat
maxPoolPreparedStatementPerConnectionSize: 20
useGlobalDataSourceStat: true
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
|
这里很多配置不是springboot已经自动配置的，所以需要我们用配置类加载：
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
| package com.qn.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.SpringBootConfiguration;
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 java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
@SpringBootConfiguration
public class DruidConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource")
public DruidDataSource druid() {
return new DruidDataSource();
}
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet());
Map<String, String> initParams = new HashMap<>();
initParams.put("loginUsername", "admin");
initParams.put("loginPassword", "123");
initParams.put("allow", "127.0.0.1");
bean.setInitParameters(initParams);
bean.setUrlMappings(Arrays.asList("/druid/*"));
return bean;
}
@Bean
public FilterRegistrationBean webStatFilter() {
FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>(new WebStatFilter());
HashMap<String, String> initParams = new HashMap<>();
initParams.put("exclusions", "/css,/druid/*");
bean.setInitParameters(initParams);
bean.setUrlPatterns(Arrays.asList("/*"));
return bean;
}
}
|
上面配置了 Druid 监控访问路径为 /druid、登录用户是 admin、登录密码是123、允许访问的IP是127.0.0.1 本机、不需要监控的请求是 /css 和 /druid 开头的请求。
启动项目，访问请求数据库的服务，
