Spring Boot + Druid 多数据源配置
项目开发中存在不止一个数据库的情况，需要配置多数据源支持相关业务开发。本文记录 Spring Boot + Druid 多数据源的手动配置方式（与单个 Druid 配置的使用方式相同），按 Mapper 包路径隔离不同数据源。
添加依赖
1<dependency>
2 <groupId>com.alibaba</groupId>
3 <artifactId>druid-spring-boot-starter</artifactId>
4 <version>1.1.14</version>
5</dependency>
配置文件
第一个数据源：
# 第一个数据库配置
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
## 初始化连接池的连接数量 大小，最小，最大
spring.datasource.druid.initial-size=1
spring.datasource.druid.min-idle=1
spring.datasource.druid.max-active=20
## 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
spring.datasource.druid.validation-query=SELECT 'x'
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.filter.stat.log-slow-sql=true
# 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
spring.datasource.druid.test-while-idle=true
# Spring监控，对内部各接口调用的监控
spring.datasource.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
第二个数据源（前缀换成 spring.datasource.felix，其余项类似）：
# 第二个数据库配置
spring.datasource.felix.username=root
spring.datasource.felix.password=root
spring.datasource.felix.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.felix.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
spring.datasource.felix.type=com.alibaba.druid.pool.DruidDataSource
## 初始化连接池的连接数量 大小，最小，最大
spring.datasource.felix.druid.initial-size=1
spring.datasource.felix.druid.min-idle=1
spring.datasource.felix.druid.max-active=20
## 配置获取连接等待超时的时间
spring.datasource.felix.druid.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.felix.druid.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.felix.druid.min-evictable-idle-time-millis=300000
spring.datasource.felix.druid.validation-query=SELECT 'x'
spring.datasource.felix.druid.test-on-borrow=false
spring.datasource.felix.druid.test-on-return=false
spring.datasource.felix.druid.filter.stat.log-slow-sql=true
# 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
spring.datasource.felix.druid.pool-prepared-statements=false
spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.felix.druid.filters=stat,wall
spring.datasource.felix.druid.test-while-idle=true
# Spring监控，对内部各接口调用的监控
spring.datasource.felix.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
mybatis.mapper-locations=classpath:mapper/*.xml,classpath:mapper2/*.xml
mybatis.type-aliases-package=com.felix.project.model
mapper.not-empty=false
mapper.identity=MYSQL
yml 配置文件写法类似。
第一个数据源配置类
新建两个 Druid 配置类，采用手动配置的方式，避免 Druid 监控不到 SQL：
1import com.alibaba.druid.pool.DruidDataSource;
2import org.apache.ibatis.session.SqlSessionFactory;
3import org.mybatis.spring.SqlSessionFactoryBean;
4import org.springframework.beans.factory.annotation.Qualifier;
5import org.springframework.beans.factory.annotation.Value;
6import org.springframework.context.annotation.Bean;
7import org.springframework.context.annotation.Configuration;
8import org.springframework.context.annotation.Primary;
9import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
10import org.springframework.jdbc.datasource.DataSourceTransactionManager;
11import tk.mybatis.spring.annotation.MapperScan;
12
13import javax.sql.DataSource;
14import java.sql.SQLException;
15
16@Configuration
17@MapperScan(basePackages = "com.felix.project.mapper", sqlSessionFactoryRef = "masterSqlSessionFactory")
18public class DruidConfig {
19
20 @Value("${spring.datasource.url}")
21 private String url;
22 @Value("${spring.datasource.username}")
23 private String username;
24 @Value("${spring.datasource.password}")
25 private String password;
26 @Value("${spring.datasource.driver-class-name}")
27 private String driverClassName;
28 @Value("${spring.datasource.druid.initial-size}")
29 private int initialSize;
30 @Value("${spring.datasource.druid.min-idle}")
31 private int minIdle;
32 @Value("${spring.datasource.druid.max-active}")
33 private int maxActive;
34 @Value("${spring.datasource.druid.max-wait}")
35 private int maxWait;
36 @Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
37 private int timeBetweenEvictionRunsMillis;
38 @Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
39 private int minEvictableIdleTimeMillis;
40 @Value("${spring.datasource.druid.validation-query}")
41 private String validationQuery;
42 @Value("${spring.datasource.druid.test-while-idle}")
43 private boolean testWhileIdle;
44 @Value("${spring.datasource.druid.test-on-borrow}")
45 private boolean testOnBorrow;
46 @Value("${spring.datasource.druid.test-on-return}")
47 private boolean testOnReturn;
48 @Value("${spring.datasource.druid.pool-prepared-statements}")
49 private boolean poolPreparedStatements;
50 @Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
51 private int maxPoolPreparedStatementPerConnectionSize;
52 @Value("${spring.datasource.druid.filters}")
53 private String filters;
54 @Value("${spring.datasource.druid.aop-patterns}")
55 private String[] aopPatterns;
56
57 /**
58 * 创建数据源
59 *
60 * @return DataSource
61 */
62 @Bean(name = "masterDataSource")
63 @Primary
64 public DataSource masterDataSource() {
65 DruidDataSource datasource = new DruidDataSource();
66 datasource.setUrl(url);
67 datasource.setUsername(username);
68 datasource.setPassword(password);
69 datasource.setDriverClassName(driverClassName);
70 datasource.setInitialSize(initialSize);
71 datasource.setMinIdle(minIdle);
72 datasource.setMaxActive(maxActive);
73 datasource.setMaxWait(maxWait);
74 datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
75 datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
76 datasource.setValidationQuery(validationQuery);
77 datasource.setTestWhileIdle(testWhileIdle);
78 datasource.setTestOnBorrow(testOnBorrow);
79 datasource.setTestOnReturn(testOnReturn);
80 datasource.setPoolPreparedStatements(poolPreparedStatements);
81 try {
82 datasource.setFilters(filters);
83 } catch (SQLException e) {
84 e.printStackTrace();
85 }
86 return datasource;
87 }
88
89 // 创建该数据源的事务管理
90 @Primary
91 @Bean(name = "primaryTransactionManager")
92 public DataSourceTransactionManager primaryTransactionManager() throws SQLException {
93 return new DataSourceTransactionManager(masterDataSource());
94 }
95
96 // 创建 Mybatis 的连接会话工厂实例
97 @Primary
98 @Bean(name = "masterSqlSessionFactory")
99 public SqlSessionFactory primarySqlSessionFactory(@Qualifier("masterDataSource") DataSource primaryDataSource) throws Exception {
100 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
101 sessionFactory.setDataSource(primaryDataSource); // 设置数据源 bean
102 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
103 .getResources("classpath:mapper/*.xml")); // 设置 mapper 文件路径
104 return sessionFactory.getObject();
105 }
106}
第二个数据源配置类
与第一个基本相同，主要区别在于：主数据源需要加 @Primary 注解，第二个数据源换一套 @Value 前缀和 bean 名称，@MapperScan 指向另一个 mapper 包：
1package com.felix.project.config;
2
3import com.alibaba.druid.pool.DruidDataSource;
4import org.apache.ibatis.session.SqlSessionFactory;
5import org.mybatis.spring.SqlSessionFactoryBean;
6import org.springframework.beans.factory.annotation.Qualifier;
7import org.springframework.beans.factory.annotation.Value;
8import org.springframework.context.annotation.Bean;
9import org.springframework.context.annotation.Configuration;
10import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
11import org.springframework.jdbc.datasource.DataSourceTransactionManager;
12import tk.mybatis.spring.annotation.MapperScan;
13
14import javax.sql.DataSource;
15import java.sql.SQLException;
16
17@Configuration
18@MapperScan(basePackages = "com.felix.project.mapper2", sqlSessionFactoryRef = "secondSqlSessionFactory")
19public class DruidFelicConfig {
20
21 @Value("${spring.datasource.felix.url}")
22 private String url;
23 @Value("${spring.datasource.felix.username}")
24 private String username;
25 @Value("${spring.datasource.felix.password}")
26 private String password;
27 @Value("${spring.datasource.felix.driver-class-name}")
28 private String driverClassName;
29 @Value("${spring.datasource.felix.druid.initial-size}")
30 private int initialSize;
31 @Value("${spring.datasource.felix.druid.min-idle}")
32 private int minIdle;
33 @Value("${spring.datasource.felix.druid.max-active}")
34 private int maxActive;
35 @Value("${spring.datasource.felix.druid.max-wait}")
36 private int maxWait;
37 @Value("${spring.datasource.felix.druid.time-between-eviction-runs-millis}")
38 private int timeBetweenEvictionRunsMillis;
39 @Value("${spring.datasource.felix.druid.min-evictable-idle-time-millis}")
40 private int minEvictableIdleTimeMillis;
41 @Value("${spring.datasource.felix.druid.validation-query}")
42 private String validationQuery;
43 @Value("${spring.datasource.felix.druid.test-while-idle}")
44 private boolean testWhileIdle;
45 @Value("${spring.datasource.felix.druid.test-on-borrow}")
46 private boolean testOnBorrow;
47 @Value("${spring.datasource.felix.druid.test-on-return}")
48 private boolean testOnReturn;
49 @Value("${spring.datasource.felix.druid.pool-prepared-statements}")
50 private boolean poolPreparedStatements;
51 @Value("${spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size}")
52 private int maxPoolPreparedStatementPerConnectionSize;
53 @Value("${spring.datasource.felix.druid.filters}")
54 private String filters;
55 @Value("${spring.datasource.felix.druid.aop-patterns}")
56 private String[] aopPatterns;
57
58 /**
59 * DataSource
60 */
61 @Bean(name = "secondDataSource")
62 public DataSource secondDataSource() {
63 DruidDataSource datasource = new DruidDataSource();
64 datasource.setUrl(url);
65 datasource.setUsername(username);
66 datasource.setPassword(password);
67 datasource.setDriverClassName(driverClassName);
68 datasource.setInitialSize(initialSize);
69 datasource.setMinIdle(minIdle);
70 datasource.setMaxActive(maxActive);
71 datasource.setMaxWait(maxWait);
72 datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
73 datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
74 datasource.setValidationQuery(validationQuery);
75 datasource.setTestWhileIdle(testWhileIdle);
76 datasource.setTestOnBorrow(testOnBorrow);
77 datasource.setTestOnReturn(testOnReturn);
78 datasource.setPoolPreparedStatements(poolPreparedStatements);
79 try {
80 datasource.setFilters(filters);
81 } catch (SQLException e) {
82 e.printStackTrace();
83 }
84 return datasource;
85 }
86
87 // 创建该数据源的事务管理
88 @Bean(name = "secondTransactionManager")
89 public DataSourceTransactionManager secondTransactionManager() throws SQLException {
90 return new DataSourceTransactionManager(secondDataSource());
91 }
92
93 // 创建 Mybatis 的连接会话工厂实例
94 @Bean(name = "secondSqlSessionFactory")
95 public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource primaryDataSource) throws Exception {
96 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
97 sessionFactory.setDataSource(primaryDataSource); // 设置数据源 bean
98 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
99 .getResources("classpath:mapper/*.xml")); // 设置 mapper 文件路径
100 return sessionFactory.getObject();
101 }
102}
com.felix.project.mapper 包下的 Mapper 走第一个数据源，com.felix.project.mapper2 包下的走第二个数据源，互不影响。
开启 Druid 监控
新建一个 DruidMonitorConfig 配置类，用于开启 Druid 监控页面（默认地址 /druid/）：
1package com.felix.project.config;
2
3import com.alibaba.druid.support.http.StatViewServlet;
4import com.alibaba.druid.support.http.WebStatFilter;
5import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
6import org.springframework.boot.web.servlet.FilterRegistrationBean;
7import org.springframework.boot.web.servlet.ServletRegistrationBean;
8import org.springframework.context.annotation.Bean;
9import org.springframework.context.annotation.Configuration;
10
11@Configuration
12public class DruidMonitorConfig {
13 @Bean
14 @ConditionalOnMissingBean
15 public ServletRegistrationBean druidServlet() {
16 ServletRegistrationBean servletRegistrationBean =
17 new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
18 // 白名单
19 // servletRegistrationBean.addInitParameter("allow","192.168.6.195");
20 // IP黑名单(存在共同时，deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
21 // servletRegistrationBean.addInitParameter("deny","192.168.6.73");
22 // 用于登陆的账号密码
23 servletRegistrationBean.addInitParameter("loginUsername", "admin");
24 servletRegistrationBean.addInitParameter("loginPassword", "admin");
25 // 是否能重置数据
26 servletRegistrationBean.addInitParameter("resetEnable", "false");
27 return servletRegistrationBean;
28 }
29
30 @Bean
31 @ConditionalOnMissingBean
32 public FilterRegistrationBean filterRegistrationBean() {
33 FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
34 // 添加过滤规则
35 filterRegistrationBean.addUrlPatterns("/*");
36 // 添加不需要忽略的格式信息
37 filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
38 return filterRegistrationBean;
39 }
40}
监控页登录账号密码即上面的
loginUsername/loginPassword，生产环境务必改掉默认值，并结合白名单限制访问来源。
