Spring Boot学习笔记（十一）Druid数据源及动态数据源配置

一、普通方式配置

即不使用springboot的自动配置方式配置Druid数据源。
pom文件：

<dependencies>
       
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-aopartifactId>
        dependency>

        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>2.2.2version>
        dependency>

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druidartifactId>
            <version>1.1.12version>
        dependency>

        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>5.1.44version>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-configuration-processorartifactId>
            <optional>trueoptional>
        dependency>
    dependencies>
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

druid.properties文件：

# 数据源配置
spring.datasource.type = com.alibaba.druid.pool.DruidDataSource
# 使用阿里的Druid连接池
spring.datasource.driver-class-name = com.mysql.jdbc.Driver
# 填写你数据库的url、登录名、密码和数据库名
spring.datasource.url = jdbc:mysql://127.0.0.1:3306/test?useSSL=FALSE&useUnicode=TRUE&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
spring.datasource.username = 账号
spring.datasource.password = 密码

# 连接池的配置信息
# 初始化大小，最小，最大
spring.datasource.druid.initial-size = 10
spring.datasource.druid.min-idle = 10
spring.datasource.druid.maxActive = 40
# 配置获取连接等待超时的时间-1min
spring.datasource.druid.maxWait = 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis = 60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.minEvictableIdleTimeMillis = 300000
spring.datasource.druid.validationQuery = SELECT 1 FROM DUAL
spring.datasource.druid.testWhileIdle = true
spring.datasource.druid.testOnBorrow = false
spring.datasource.druid.testOnReturn = false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.druid.poolPreparedStatements = true
spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize = 20

# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters = stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.druid.connectionProperties = druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 配置DruidStatFilter
spring.datasource.druid.web-stat-filter.enabled = true
spring.datasource.druid.web-stat-filter.url-pattern = /druid/*
#过滤器-静态资源放行
spring.datasource.druid.web-stat-filter.exclusions = *.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*

# 配置DruidStatViewServlet
spring.datasource.druid.stat-view-servlet.url-pattern = /druid/*
# IP白名单(没有配置或者为空，则允许所有访问)
spring.datasource.druid.stat-view-servlet.allow = 127.0.0.1,192.168.8.109
# IP黑名单 (存在共同时，deny优先于allow)
spring.datasource.druid.stat-view-servlet.deny = 192.168.1.188
#  禁用HTML页面上的“Reset All”功能
spring.datasource.druid.stat-view-servlet.reset-enable = false
# 登录名
spring.datasource.druid.stat-view-servlet.login-username = root
# 登录密码
spring.datasource.druid.stat-view-servlet.login-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
42
43
44
45
46
47
48
49
50

需要一个配置类来对应druid配置文件。
DruidProperties.java

import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;

import java.util.Properties;

@Configuration
@PropertySource({"classpath:druid.properties"})
@ConfigurationProperties(prefix = "spring.datasource.druid")
public class DruidProperties {
    @Value("${spring.datasource.type}")
    private String type;
    @Value("${spring.datasource.driver-class-name}")
    private String driverClassName;
    @Value("${spring.datasource.url}")
    private String url;
    @Value("${spring.datasource.username}")
    private String username;
    @Value("${spring.datasource.password}")
    private String password;

    //连接池信息
    private Integer initialSize;
    private Integer minIdle;
    private Integer maxActive;
    private Integer maxWait;
    private Integer timeBetweenEvictionRunsMillis;
    private Integer minEvictableIdleTimeMillis;
    private String validationQuery;
    private Boolean testWhileIdle;
    private Boolean testOnBorrow;
    private Boolean testOnReturn;
    private Boolean poolPreparedStatements;
    private Integer maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private Properties connectionProperties;

    //wrap Properties for webStatFilter and statViewServlet
    private Properties webStatFilter;
    private Properties statViewServlet;

    private String webStatFilterEnabled;
    private String webStatFilterUrlPattern;
    private String webStatFilterExclusions;

    private String statViewServletUrlPattern;
    private String statViewServletAllow;
    private String statViewServletDeny;
    private String statViewServletResetEnable;
    private String statViewServletLoginUsername;
    private String statViewServletLoginPassword;

    public String getWebStatFilterEnabled() {
        return webStatFilter.getProperty("enabled");
    }

    public String getWebStatFilterUrlPattern() {
        return webStatFilter.getProperty("url-pattern");
    }

    public String getWebStatFilterExclusions() {
        return webStatFilter.getProperty("exclusions");
    }

    public String getStatViewServletUrlPattern() {
        return statViewServlet.getProperty("url-pattern");
    }

    public String getStatViewServletAllow() {
        return statViewServlet.getProperty("allow");
    }

    public String getStatViewServletDeny() {
        return statViewServlet.getProperty("deny");
    }

    public String getStatViewServletResetEnable() {
        return statViewServlet.getProperty("reset-enable");
    }

    public String getStatViewServletLoginUsername() {
        return statViewServlet.getProperty("login-username");
    }

    public String getStatViewServletLoginPassword() {
        return statViewServlet.getProperty("login-password");
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    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 Integer getInitialSize() {
        return initialSize;
    }

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

    public Integer getMinIdle() {
        return minIdle;
    }

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

    public Integer getMaxActive() {
        return maxActive;
    }

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

    public Integer getMaxWait() {
        return maxWait;
    }

    public void setMaxWait(Integer maxWait) {
        this.maxWait = maxWait;
    }

    public Integer getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public Integer getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public Boolean getTestWhileIdle() {
        return testWhileIdle;
    }

    public void setTestWhileIdle(Boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public Boolean getTestOnBorrow() {
        return testOnBorrow;
    }

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

    public Boolean getTestOnReturn() {
        return testOnReturn;
    }

    public void setTestOnReturn(Boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public Boolean getPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public void setPoolPreparedStatements(Boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public Integer getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public String getFilters() {
        return filters;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }

    public Properties getConnectionProperties() {
        return connectionProperties;
    }

    public void setConnectionProperties(Properties connectionProperties) {
        this.connectionProperties = connectionProperties;
    }

    public Properties getWebStatFilter() {
        return webStatFilter;
    }

    public void setWebStatFilter(Properties webStatFilter) {
        this.webStatFilter = webStatFilter;
    }

    public Properties getStatViewServlet() {
        return statViewServlet;
    }

    public void setStatViewServlet(Properties statViewServlet) {
        this.statViewServlet = statViewServlet;
    }

    public void setWebStatFilterEnabled(String webStatFilterEnabled) {
        this.webStatFilterEnabled = webStatFilterEnabled;
    }

    public void setWebStatFilterUrlPattern(String webStatFilterUrlPattern) {
        this.webStatFilterUrlPattern = webStatFilterUrlPattern;
    }

    public void setWebStatFilterExclusions(String webStatFilterExclusions) {
        this.webStatFilterExclusions = webStatFilterExclusions;
    }

    public void setStatViewServletUrlPattern(String statViewServletUrlPattern) {
        this.statViewServletUrlPattern = statViewServletUrlPattern;
    }

    public void setStatViewServletAllow(String statViewServletAllow) {
        this.statViewServletAllow = statViewServletAllow;
    }

    public void setStatViewServletDeny(String statViewServletDeny) {
        this.statViewServletDeny = statViewServletDeny;
    }

    public void setStatViewServletResetEnable(String statViewServletResetEnable) {
        this.statViewServletResetEnable = statViewServletResetEnable;
    }

    public void setStatViewServletLoginUsername(String statViewServletLoginUsername) {
        this.statViewServletLoginUsername = statViewServletLoginUsername;
    }

    public void setStatViewServletLoginPassword(String statViewServletLoginPassword) {
        this.statViewServletLoginPassword = statViewServletLoginPassword;
    }

    public void setTimeBetweenConnectErrorMillis(Integer timeBetweenEvictionRunsMillis) {
    }
}
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
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296

使用配置类把druid数据源注入到spring中。
DruidDataSourceConfig.java

import java.sql.SQLException;

@Configuration
public class DruidDataSourceConfig {
    @Autowired
    private DruidProperties dataSourceConfig;

    @Bean
    public DruidDataSource druidDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        //数据源连接参数配置
        druidDataSource.setDriverClassName(dataSourceConfig.getDriverClassName());
        druidDataSource.setUrl(dataSourceConfig.getUrl());
        druidDataSource.setUsername(dataSourceConfig.getUsername());
        druidDataSource.setPassword(dataSourceConfig.getPassword());
        //连接池参数设置
        druidDataSource.setInitialSize(dataSourceConfig.getInitialSize());
        druidDataSource.setMinIdle(dataSourceConfig.getMinIdle());
        druidDataSource.setMaxActive(dataSourceConfig.getMaxActive());
        druidDataSource.setTimeBetweenConnectErrorMillis(dataSourceConfig.getTimeBetweenEvictionRunsMillis());
        druidDataSource.setMinEvictableIdleTimeMillis(dataSourceConfig.getMinEvictableIdleTimeMillis());
        druidDataSource.setValidationQuery(dataSourceConfig.getValidationQuery());
        druidDataSource.setTestWhileIdle(dataSourceConfig.getTestWhileIdle());
        druidDataSource.setTestOnBorrow(dataSourceConfig.getTestOnBorrow());
        druidDataSource.setTestOnReturn(dataSourceConfig.getTestOnReturn());
        druidDataSource.setPoolPreparedStatements(dataSourceConfig.getPoolPreparedStatements());
        druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(dataSourceConfig.getMaxPoolPreparedStatementPerConnectionSize());
        try {
            druidDataSource.setFilters(dataSourceConfig.getFilters());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        druidDataSource.setConnectProperties(dataSourceConfig.getConnectionProperties());
        return druidDataSource;
    }

    /**
     * Druid监控servlet配置
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),dataSourceConfig.getStatViewServletUrlPattern());
        //urlPattern-访问路径设置
        //IP配置
        servletRegistrationBean.addInitParameter("allow",dataSourceConfig.getStatViewServletAllow());//IP白名单
        servletRegistrationBean.addInitParameter("deny",dataSourceConfig.getStatViewServletDeny());//IP黑名单
        //登陆账户配置
        servletRegistrationBean.addInitParameter("loginUsername",dataSourceConfig.getStatViewServletLoginUsername());
        servletRegistrationBean.addInitParameter("loginPassword",dataSourceConfig.getStatViewServletLoginPassword());
        //是否允许Reset All-计数器清零操作
        servletRegistrationBean.addInitParameter("resetEnable",dataSourceConfig.getStatViewServletResetEnable());
        return servletRegistrationBean;
    }


    /**
     * Druid监控过滤器配置
     * @return
     */
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        //设置过滤器
        filterRegistrationBean.addUrlPatterns(dataSourceConfig.getWebStatFilterUrlPattern());//url过滤规则
        filterRegistrationBean.addInitParameter("enabled",dataSourceConfig.getWebStatFilterEnabled());//是否开启过滤功能
        filterRegistrationBean.addInitParameter("exclusions",dataSourceConfig.getWebStatFilterExclusions());//忽略
        return filterRegistrationBean;
    }

}
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

写一个查看数据源是否注入的类：

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

@Component
public class DataSourceShow implements ApplicationContextAware {
    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        // Spring Boot 默认已经配置好了数据源，程序员可以直接 DI 注入然后使用即可
        DataSource dataSource = applicationContext.getBean(DataSource.class);
        System.out.println("--------------------------------");
        System.out.println(dataSource.getClass().getName());
        System.out.println("--------------------------------");

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

实体类：

public class User {
    /**
     * 用户ID
     */
    private String userId;

    /**
     * 用户账号
     */
    private String userName;

    private int age;

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId='" + userId + '\'' +
                ", userName='" + userName + '\'' +
                ", age=" + age +
                '}';
    }
}
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

mapper类：

import org.springframework.stereotype.Repository;

@Repository
public interface UserMapper {
    public User getUser(String id);
}
1
2
3
4
5
6

service接口及实现类：

public interface UserService {
    public User getUser(String id);
}
1
2
3

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public User getUser(String id) {
        User user = userMapper.getUser(id);
        return user;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

controller类：

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping("/user")
public class UserController {
    @Autowired
    public UserService userService;

    @RequestMapping("/getUserInfo")
    @ResponseBody
    public User getUserInfo(String id){
        User user = userService.getUser(id);
        return user;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mybatis配置类：

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"mapper类所在包名"}, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MybaticsConfig {

    /**
     * 配置事务管理器，不然事务不起作用
     *
     * @return
     */
    @Bean
    public PlatformTransactionManager transactionManager(DataSource druidDataSource) {
        return new DataSourceTransactionManager(druidDataSource);
    }

	//将@Bean注入的dataSource注入，名称就是注入dataSource方法的方法名
    @Primary
    @Bean(name = "masterSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(DataSource druidDataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(druidDataSource);
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath*:mapper/*.xml"));
        sqlSessionFactoryBean.setTypeAliasesPackage("com.dmf.demo.mybatics");
        sqlSessionFactoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return sqlSessionFactoryBean.getObject();
    }
}
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

mapper文件;

DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="包名.UserMapper">
    <resultMap type="包名.User" id="UserInfo">
        <id property="userId" column="id"/>
        <result  property="userName" column="name"/>
        <result  property="age" column="age"/>
    resultMap>
    <select id="getUser" resultMap="UserInfo">
        select * from user where id = #{id}
    select>
mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

建表语句：

CREATE TABLE `user` (
`id`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`name`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`age`  int(11) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
1
2
3
4
5
6
7
8
9
10

application.yml

server:
  servlet:
    context-path: /demo
  port: 8080



mybatis:
  mapper-locations: classpath*:mapper/**/*Mapper.xml
    # 使用驼峰命名
  # 数据库表列：user_name
  # 实体类属性：userName
  configuration:
    map-underscore-to-camel-case: true
1
2
3
4
5
6
7
8
9
10
11
12
13
14

springboot启动类：

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;


@EnableConfigurationProperties
@SpringBootApplication(scanBasePackages = "包名")
public class DataSourceDemoApplication {

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

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

启动springboot

可以看到druid数据源注入成功。也可以访问controller测试数据源是否配置成功。
在浏览器访问：http://localhost:8080/demo/druid，可以查看druid的控制台页面（账号密码看配置文件：root/123456）。

二、使用SpringBoot的自动配置配置数据源

使用druid的starter包替换druid包：

	
		<dependency>
            <groupId>com.alibabagroupId>
            <artifactId>druid-spring-boot-starterartifactId>
            <version>1.1.10version>
        dependency>
1
2
3
4
5
6
7
8
9
10

在application.yml文件中新增druid配置：

spring:
  datasource:
    # 数据源基本配置
    username: 账号
    password: 密码
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    # driver-class需要注意mysql驱动的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    # Druid的其他属性配置
    druid:
      # 初始化时建立物理连接的个数
      initial-size: 10
      # 连接池的最小空闲数量
      min-idle: 5
      # 连接池最大连接数量
      max-active: 20
      # 获取连接时最大等待时间，单位毫秒
      max-wait: 60000
      # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-while-idle: true
      # 既作为检测的间隔时间又作为testWhileIdel执行的依据
      time-between-eviction-runs-millis: 60000
      # 销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接(配置连接在池中的最小生存时间)
      min-evictable-idle-time-millis: 30000
      # 用来检测数据库连接是否有效的sql 必须是一个查询语句(oracle中为 select 1 from dual)
      validation-query: SELECT 1 FROM DUAL
      # 申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-borrow: false
      # 归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-return: false
      # 是否缓存preparedStatement, 也就是PSCache,PSCache对支持游标的数据库性能提升巨大，比如说oracle,在mysql下建议关闭。
      pool-prepared-statements: false
      # 置监控统计拦截的filters，去掉后监控界面sql无法统计，stat: 监控统计、Slf4j:日志记录、waLL: 防御sqL注入
      filters: stat,wall,slf4j
      # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
      max-pool-prepared-statement-per-connection-size: -1
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      web-stat-filter:
        # 是否启用StatFilter默认值true
        enabled: true
        # 添加过滤规则
        url-pattern: /*
        # 忽略过滤的格式
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico

      stat-view-servlet:
        # 是否启用StatViewServlet默认值true
        enabled: true
        # 访问路径为/druid时，跳转到StatViewServlet
        url-pattern: /druid/*
        # 是否能够重置数据
        reset-enable: false
        # 需要账号密码才能访问控制台，默认为root
        login-username: root
        login-password: 123456
        # IP白名单
        allow: 127.0.0.1
        # IP黑名单（共同存在时，deny优先于allow）
        deny:
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

使用这种方法我们就无需再写配置属性类以及配置类了。自动配置类以及帮我们完成注入，直接使用就可以了。
druid自动配置类DruidDataSourceAutoConfigure.java源码：

package com.alibaba.druid.spring.boot.autoconfigure;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidFilterConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidSpringAopConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidStatViewServletConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidWebStatFilterConfiguration;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;

@Configuration
@ConditionalOnClass({DruidDataSource.class})
@AutoConfigureBefore({DataSourceAutoConfiguration.class})
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class, DruidStatViewServletConfiguration.class, DruidWebStatFilterConfiguration.class, DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {
    private static final Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceAutoConfigure.class);

    public DruidDataSourceAutoConfigure() {
    }

    @Bean(
        initMethod = "init"
    )
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        LOGGER.info("Init DruidDataSource");
        return new DruidDataSourceWrapper();
    }
}
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

通过源码可以看到注入了dataSource，名字为“dataSource”。那我们在配置mybatis的SqlSessionFactory或者事务管理器PlatformTransactionManager时，直接使用名称“dataSource”就可以使用了。

三、动态数据源配置

在实际项目中，难免会碰到使用多个数据源的情况，不使用动态数据源的话，就需要写多套mybatis代码，这样太麻烦了。spring提供了AbstractRoutingDataSource类，我们继承这个类就可以实现动态数据源了。

多数据源枚举类：

public enum DBTypeEnum {
    /**
     * 主库
     */
    MASTER("master"),

    /**
     * 从库
     */
    SLAVE("slave");

    private final String value;

    DBTypeEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

上下文数据源类
DbContextHolder.java

public class DbContextHolder {
	//使用ThreadLocal，防止线程安全问题
    private static final ThreadLocal CONTEXT_HOLDER = new ThreadLocal();

    /**
     * 设置数据源
     *
     * @param dbTypeEnum 数据库类型
     */
    public static void setDbType(DBTypeEnum dbTypeEnum) {
        CONTEXT_HOLDER.set(dbTypeEnum.getValue());
    }

    /**
     * 取得当前数据源
     *
     * @return dbType
     */
    public static String getDbType() {
        return (String) CONTEXT_HOLDER.get();
    }

    /**
     * 清除上下文数据
     */
    public static void clearDbType() {
        CONTEXT_HOLDER.remove();
    }

}
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

动态数据源类：

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.getDbType();
    }
}
1
2
3
4
5
6
7
8

再使用DynamicDataSource时，会调用determineCurrentLookupKey方法拿到具体数据源的key，然后使用对应的数据源，如果没有对应的数据源，会使用DynamicDataSource设置的默认数据源
applicationyml配置：

spring:
  datasource:
    # 数据源基本配置
    username: root
    password: root
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    # driver-class需要注意mysql驱动的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    # Druid的其他属性配置
    druid:
      # 初始化时建立物理连接的个数
      initial-size: 10
      # 连接池的最小空闲数量
      min-idle: 5
      # 连接池最大连接数量
      max-active: 20
      # 获取连接时最大等待时间，单位毫秒
      max-wait: 60000
      # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
      test-while-idle: true
      # 既作为检测的间隔时间又作为testWhileIdel执行的依据
      time-between-eviction-runs-millis: 60000
      # 销毁线程时检测当前连接的最后活动时间和当前时间差大于该值时，关闭当前连接(配置连接在池中的最小生存时间)
      min-evictable-idle-time-millis: 30000
      # 用来检测数据库连接是否有效的sql 必须是一个查询语句(oracle中为 select 1 from dual)
      validation-query: SELECT 1 FROM DUAL
      # 申请连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-borrow: false
      # 归还连接时会执行validationQuery检测连接是否有效,开启会降低性能,默认为true
      test-on-return: false
      # 是否缓存preparedStatement, 也就是PSCache,PSCache对支持游标的数据库性能提升巨大，比如说oracle,在mysql下建议关闭。
      pool-prepared-statements: false
      # 置监控统计拦截的filters，去掉后监控界面sql无法统计，stat: 监控统计、Slf4j:日志记录、waLL: 防御sqL注入
      filters: stat,wall,slf4j
      # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。在Druid中，不会存在Oracle下PSCache占用内存过多的问题，可以把这个数值配置大一些，比如说100
      max-pool-prepared-statement-per-connection-size: -1
      # 合并多个DruidDataSource的监控数据
      use-global-data-source-stat: true
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      web-stat-filter:
        # 是否启用StatFilter默认值true
        enabled: true
        # 添加过滤规则
        url-pattern: /*
        # 忽略过滤的格式
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico

      stat-view-servlet:
        # 是否启用StatViewServlet默认值true
        enabled: true
        # 访问路径为/druid时，跳转到StatViewServlet
        url-pattern: /druid/*
        # 是否能够重置数据
        reset-enable: false
        # 需要账号密码才能访问控制台，默认为root
        login-username: root
        login-password: 123456
        # IP白名单
        allow: 127.0.0.1
        # IP黑名单（共同存在时，deny优先于allow）
        deny:

    master:
      # 数据源基本配置
      username: 账号
      password: 密码
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
      # driver-class需要注意mysql驱动的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
      driver-class-name: com.mysql.jdbc.Driver

    slave:
      # 数据源基本配置
      username: root
      password: root1234
      url: jdbc:mysql://127.0.0.1:3306/test1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
      # driver-class需要注意mysql驱动的版本(com.mysql.cj.jdbc.Driver 或 com.mysql.jdbc.Driver)
      driver-class-name: com.mysql.jdbc.Driver
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

数据源配置类：

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource(){
        return new DruidDataSourceBuilder().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource(){
        return new DruidDataSourceBuilder().build();
    }

    @Bean
    @Primary
    public DataSource multipleDataSource(DataSource masterDataSource,DataSource slaveDataSource){

        DynamicDataSource multipleDataSource = new DynamicDataSource();
        Map<Object, Object> dataSources = new HashMap<>();
        dataSources.put(DBTypeEnum.MASTER.getValue(), masterDataSource);
        dataSources.put(DBTypeEnum.SLAVE.getValue(), slaveDataSource);
        multipleDataSource.setTargetDataSources(dataSources);
        multipleDataSource.setDefaultTargetDataSource(masterDataSource);

        return multipleDataSource;
    }
}
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

数据源注册成功就可以使用“multipleDataSource”注入使用了。将mybatis配置注入的数据源名称改成“multipleDataSource”就可以使用了。
这样还是不够灵活，每次在调用数据库时，还需要设置DbContextHolder的CONTEXT_HOLDER属性到具体的数据源。
可以使用aop的方式实现数据源切换，只需一个注解即可。

自定义注解类：

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)
@Target({
        ElementType.METHOD
})
public @interface SwitchDataSource {
    DBTypeEnum value() default DBTypeEnum.MASTER;
}
1
2
3
4
5
6
7
8
9
10
11
12

aop处理类：

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

/**
 * AOP方式动态切换数据源 (为了保证AOP在事务注解之前生效,Order的值越小,优先级越高)
 */
@Component
@Aspect
@Order(-100)
public class DataSourceSwitchAspect {

    @Pointcut("@annotation(SwitchDataSource)")
    public void pointcut() {
    }

    @Before(value = "pointcut()")
    public void before(JoinPoint joinPoint){

        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
        SwitchDataSource dataSourceSwitch = methodSignature.getMethod().getAnnotation(SwitchDataSource.class);
        System.out.println("使用数据源:"+dataSourceSwitch.value());

        DbContextHolder.setDbType(dataSourceSwitch.value());
    }

    @After(value = "pointcut()")
    public void after(JoinPoint joinPoint){
        DbContextHolder.clearDbType();
    }
}
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

然后在service里使用@SwitchDataSource注解即可实现动态切换数据源。

	@Override
    @SwitchDataSource(DBTypeEnum.MASTER)
    public User getUser(String id) {
        User user = userMapper.getUser(id);
        return user;
    }

    @Override
    @SwitchDataSource(DBTypeEnum.SLAVE)
    public User getUser2(String id){
        User user = userMapper.getUser(id);

        return user;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14