一起来学SpringBoot(十三)Druid数据源

Druid是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。考虑到可能是数据库连接的问题,所以我打算引入其他的数据池,引入数据池的时候找来找去,比较了当前两个最火的数据池,druid和HikariCP,比来比去选了阿里的druid,虽然spring boot默认不支持druid,而是支持HikariCP,而且HikariCP的性能更好,但是阿里功能多,界面友好方便,性价比更高!

配置

首先呢引入依赖

1
2
3
4
5
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>

然后呢在yml中配置

1
2
3
4
5
6
7
8
9
10
druid:
driver-class: com.mysql.jdbc.Driver
min-idle: 1
password: 123456
test-on-borrow: true
timeBetweenEvictionRunsMillis: 9000
max-active: 20
initial-size: 1
url: jdbc:mysql://192.168.8.100:3306/test?useUnicode=true&characterEncoding=utf8
username: root

自定义配置有助于更好的维护

再然后呢是或者配置类

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
package com.maoxs.conf;

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "druid")
public class DruidProperties {
private String url;
private String username;
private String password;
private String driverClass;
private int maxActive;
private int minIdle;
private int initialSize;
private boolean testOnBorrow;

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 getDriverClass() {
return driverClass;
}

public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}

public int getMaxActive() {
return maxActive;
}

public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}

public int getMinIdle() {
return minIdle;
}

public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}

public int getInitialSize() {
return initialSize;
}

public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}

public boolean isTestOnBorrow() {
return testOnBorrow;
}

public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
}

再然后呢就是注入我们的数据源

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
package com.maoxs.conf;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;


@Configuration
@EnableConfigurationProperties(DruidProperties.class)
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(prefix = "druid", name = "url")
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
public class DruidAutoConfiguration {
@Autowired
private DruidProperties properties;

@Bean
public DataSource dataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(properties.getUrl());
dataSource.setUsername(properties.getUsername());
dataSource.setPassword(properties.getPassword());
if (properties.getInitialSize() > 0) {
dataSource.setInitialSize(properties.getInitialSize());
}
if (properties.getMinIdle() > 0) {
dataSource.setMinIdle(properties.getMinIdle());
}
if (properties.getMaxActive() > 0) {
dataSource.setMaxActive(properties.getMaxActive());
}
dataSource.setTestOnBorrow(properties.isTestOnBorrow());
try {
dataSource.init();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return dataSource;
}
}

ok,大功告成,启动项目看下你的数据源

img


监控中心

既然是为监控而生的数据源那肯定就要有监控页面啦

只需要配置如下代码

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
package com.maoxs.conf;

import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
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 java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfiguration {

private static final Logger log = LoggerFactory.getLogger(DruidConfiguration.class);

@Bean
public ServletRegistrationBean druidServlet() {
log.info("init Druid Servlet Configuration ");
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
servletRegistrationBean.setServlet(new StatViewServlet());
servletRegistrationBean.addUrlMappings("/druid/*");
Map<String, String> initParameters = new HashMap<String, String>();
initParameters.put("loginUsername", "admin");// 用户名
initParameters.put("loginPassword", "admin");// 密码
initParameters.put("resetEnable", "false");// 禁用HTML页面上的“Reset All”功能
initParameters.put("allow", ""); // IP白名单 (没有配置或者为空,则允许所有访问)
//initParameters.put("deny", "192.168.20.38");// IP黑名单 (存在共同时,deny优先于allow)
servletRegistrationBean.setInitParameters(initParameters);
return servletRegistrationBean;
}

@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}

}

我的项目端口为8080 启动项目呢访问http://localhost:8080/druid/login.html

可以看到 棒棒哒出来了用户名密码都是我们设置的

img

进去看看, 功能蛮多的。

img


sql监控

有时候我们开发在灰度测试的时候呢,希望看到sql的执行效率时间,以此来作为我们优化的参考,那么怎么进行sql的监控呢?

这边呢我接入了mybatis做了一个列表查询

img

然后呢只需要在注入DataSource的时候呢加入下面一行代码即可 当然你也可以配置到你的yml中,不过呢这个是比较占内存的,项目上线后切记关掉。

img


sql防火墙

配置WallFilter,可以起到拦截作用,从而形成SQL的白名单和黑名单。

配置也非常简单

只需要注入一个wallFilter

1
2
3
4
5
6
7
8
9
10
@Bean(name = "wallFilter")
WallFilter wallFilter() {
WallConfig wallConfig = new WallConfig();
//允许sql批量操作
wallConfig.setMultiStatementAllow(true);
wallConfig.setNoneBaseStatementAllow(true);//允许非基本语句的其他语句
WallFilter wfilter = new WallFilter();
wfilter.setConfig(wallConfig);
return wfilter;
}

然后在注入DataSource的时候添加如下代码就可以了

img

然后我们启动项目看下拦截的效果 棒棒哒 生效了

img

spring监控

Druid提供了Spring和Jdbc的关联监控。

配置也很简单

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
package com.maoxs.conf;

import com.alibaba.druid.support.spring.stat.DruidStatInterceptor;
import org.aspectj.lang.annotation.Aspect;
import org.springframework.aop.Advisor;
import org.springframework.aop.support.DefaultBeanFactoryPointcutAdvisor;
import org.springframework.aop.support.JdkRegexpMethodPointcut;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Scope;

@Aspect // AOP 切面
@Configuration
public class DruidSpringConfiguration {

@Bean
public DruidStatInterceptor druidStatInterceptor() {
return new DruidStatInterceptor();
}

@Bean
@Scope("prototype")
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut jdkRegexpMethodPointcut = new JdkRegexpMethodPointcut();
//指明需要监控的类
jdkRegexpMethodPointcut.setPatterns("com.maoxs.conf.*", "com.maoxs.controller.*");
return jdkRegexpMethodPointcut;
}

@Bean
public Advisor druidAdviceAdvisor() {
DefaultBeanFactoryPointcutAdvisor defaultBeanFactoryPointcutAdvisor = new DefaultBeanFactoryPointcutAdvisor();
defaultBeanFactoryPointcutAdvisor.setAdvice(druidStatInterceptor());
defaultBeanFactoryPointcutAdvisor.setPointcut(druidStatPointcut());
return defaultBeanFactoryPointcutAdvisor;
}

}

只需要一个切面即可,然后呢我们访问下看看 okokok也是没问题的

img

多数据源

首先呢贴出yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
master:
datasource:
password: 123456
driver-class: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.8.100:3306/test?useUnicode=true&characterEncoding=utf8
username: root
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
#从数据源
cluster:
datasource:
password: 123456
driver-class: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.8.100:3306/shiro?useUnicode=true&characterEncoding=utf8
username: root
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true

配置的话首先是配置的获取 主从都是一样的,只需要改变下prefix

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
package com.maoxs.look;

import org.springframework.boot.context.properties.ConfigurationProperties;

@ConfigurationProperties(prefix = "cluster.datasource")
public class DruidClusterProperties {
private String url;
private String username;
private String password;
private String driverClass;
private int maxActive;
private int minIdle;
private int initialSize;
private boolean testOnBorrow;

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 getDriverClass() {
return driverClass;
}

public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}

public int getMaxActive() {
return maxActive;
}

public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}

public int getMinIdle() {
return minIdle;
}

public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}

public int getInitialSize() {
return initialSize;
}

public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}

public boolean isTestOnBorrow() {
return testOnBorrow;
}

public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
}

然后是主库的配置

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
package com.maoxs.look;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.SQLException;


@Configuration
@EnableConfigurationProperties(DruidMasterProperties.class)
@ConditionalOnClass(DruidDataSource.class)
@ConditionalOnProperty(prefix = "master.datasource", name = "url")
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
public class DruidMasterAutoConfiguration {
@Autowired
private DruidMasterProperties properties;

@Bean(name = "masterDataSource")
@Primary
public DataSource masterDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(properties.getUrl());
dataSource.setUsername(properties.getUsername());
dataSource.setPassword(properties.getPassword());
if (properties.getInitialSize() > 0) {
dataSource.setInitialSize(properties.getInitialSize());
}
if (properties.getMinIdle() > 0) {
dataSource.setMinIdle(properties.getMinIdle());
}
if (properties.getMaxActive() > 0) {
dataSource.setMaxActive(properties.getMaxActive());
}
dataSource.setTestOnBorrow(properties.isTestOnBorrow());
try {
dataSource.init();
} catch (SQLException e) {
throw new RuntimeException(e);
}
return dataSource;
}

@Bean(name = "masterTransactionManager")
@Primary
public DataSourceTransactionManager masterTransactionManager() {
return new DataSourceTransactionManager(masterDataSource());
}

}

切记如果不是使用springboot默认yml主库的配置 必须要在注入主库的时候加入 @Primary注解

好啦这就是多数据源的配置,当然druid的功能远不如此,需要的请去girhub wiki查看相关文档

点我你就进来了

本博文是基于springboot2.x 如果有什么不对的请在下方留言。

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×