Contents
开始
项目开发中存在不止一个数据库的情况，需要用到多数据源配置，进行相关业务开发。
实现方式
使用方式与单个druid配置相同
pom
1 <dependency>
2 <groupId>com.alibaba</groupId>
3 <artifactId>druid-spring-boot-starter</artifactId>
4 <version>1.1.14</version>
5</dependency>
配置文件
application配置
1#第一个数据配置
2spring.datasource.username=root
3spring.datasource.password=root
4spring.datasource.driver-class-name=com.mysql.jdbc.Driver
5spring.datasource.url=jdbc:mysql://localhost:3306/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
6
7# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
8spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
9
10## 初始化连接池的连接数量 大小，最小，最大
11spring.datasource.druid.initial-size=1
12spring.datasource.druid.min-idle=1
13spring.datasource.druid.max-active=20
14## 配置获取连接等待超时的时间
15spring.datasource.druid.max-wait=60000
16# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
17spring.datasource.druid.time-between-eviction-runs-millis=60000
18# 配置一个连接在池中最小生存的时间，单位是毫秒
19spring.datasource.druid.min-evictable-idle-time-millis=300000
20spring.datasource.druid.validation-query=SELECT 'x'
21spring.datasource.druid.test-on-borrow=false
22spring.datasource.druid.test-on-return=false
23spring.datasource.druid.filter.stat.log-slow-sql=true
24# 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
25spring.datasource.druid.pool-prepared-statements=false
26spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
27# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
28spring.datasource.druid.filters=stat,wall
29spring.datasource.druid.test-while-idle=true
30#Spring监控，对内部各接口调用的监控
31spring.datasource.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
32
33# 第二个数据库配置
34spring.datasource.felix.username=root
35spring.datasource.felix.password=root
36spring.datasource.felix.driver-class-name=com.mysql.jdbc.Driver
37spring.datasource.felix.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
38# 连接池指定 springboot版本默认使用HikariCP 此处要替换成Druid
39spring.datasource.felix.type=com.alibaba.druid.pool.DruidDataSource
40## 初始化连接池的连接数量 大小，最小，最大
41spring.datasource.felix.druid.initial-size=1
42spring.datasource.felix.druid.min-idle=1
43spring.datasource.felix.druid.max-active=20
44## 配置获取连接等待超时的时间
45spring.datasource.felix.druid.max-wait=60000
46# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
47spring.datasource.felix.druid.time-between-eviction-runs-millis=60000
48# 配置一个连接在池中最小生存的时间，单位是毫秒
49spring.datasource.felix.druid.min-evictable-idle-time-millis=300000
50spring.datasource.felix.druid.validation-query=SELECT 'x'
51spring.datasource.felix.druid.test-on-borrow=false
52spring.datasource.felix.druid.test-on-return=false
53spring.datasource.felix.druid.filter.stat.log-slow-sql=true
54# 是否缓存preparedStatement，也就是PSCache 官方建议MySQL下建议关闭 个人建议如果想用SQL防火墙 建议打开
55spring.datasource.felix.druid.pool-prepared-statements=false
56spring.datasource.felix.druid.max-pool-prepared-statement-per-connection-size=20
57# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
58spring.datasource.felix.druid.filters=stat,wall
59spring.datasource.felix.druid.test-while-idle=true
60#Spring监控，对内部各接口调用的监控
61spring.datasource.felix.druid.aop-patterns=com.felix.project.controller.*,com.felix.project.mapper.*,com.felix.project.service.*
62
63
64mybatis.mapper-locations=classpath:mapper/*.xml,classpath:mapper2/*.xml
65mybatis.type-aliases-package=com.felix.project.model
66mapper.not-empty=false
67mapper.identity=MYSQL
68
yml 配置文件相类似
第一个config类
新建两个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 * @return DataSource
60 */
61 @Bean(name = "masterDataSource")
62 @Primary
63 public DataSource masterDataSource() {
64 DruidDataSource datasource = new DruidDataSource();
65 datasource.setUrl(url);
66 datasource.setUsername(username);
67 datasource.setPassword(password);
68 datasource.setDriverClassName(driverClassName);
69 datasource.setInitialSize(initialSize);
70 datasource.setMinIdle(minIdle);
71 datasource.setMaxActive(maxActive);
72 datasource.setMaxWait(maxWait);
73 datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
74 datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
75 datasource.setValidationQuery(validationQuery);
76 datasource.setTestWhileIdle(testWhileIdle);
77 datasource.setTestOnBorrow(testOnBorrow);
78 datasource.setTestOnReturn(testOnReturn);
79 datasource.setPoolPreparedStatements(poolPreparedStatements);
80 try {
81 datasource.setFilters(filters);
82 } catch (SQLException e) {
83 e.printStackTrace();
84 }
85 return datasource;
86 }
87
88 // 创建该数据源的事务管理
89 @Primary
90 @Bean(name = "primaryTransactionManager")
91 public DataSourceTransactionManager primaryTransactionManager() throws SQLException {
92 return new DataSourceTransactionManager(masterDataSource());
93 }
94
95 // 创建Mybatis的连接会话工厂实例
96 @Primary
97 @Bean(name = "masterSqlSessionFactory")
98 public SqlSessionFactory primarySqlSessionFactory(@Qualifier("masterDataSource") DataSource primaryDataSource) throws Exception {
99 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
100 sessionFactory.setDataSource(primaryDataSource); // 设置数据源bean
101 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
102 .getResources("classpath:mapper/*.xml")); // 设置mapper文件路径
103 return sessionFactory.getObject();
104 }
105}
第二config类
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 * @return
61 */
62 @Bean(name = "secondDataSource")
63 public DataSource secondDataSource() {
64 DruidDataSource datasource = new DruidDataSource();
65 datasource.setUrl(url);
66 datasource.setUsername(username);
67 datasource.setPassword(password);
68 datasource.setDriverClassName(driverClassName);
69 datasource.setInitialSize(initialSize);
70 datasource.setMinIdle(minIdle);
71 datasource.setMaxActive(maxActive);
72 datasource.setMaxWait(maxWait);
73 datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
74 datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
75 datasource.setValidationQuery(validationQuery);
76 datasource.setTestWhileIdle(testWhileIdle);
77 datasource.setTestOnBorrow(testOnBorrow);
78 datasource.setTestOnReturn(testOnReturn);
79 datasource.setPoolPreparedStatements(poolPreparedStatements);
80 try {
81 datasource.setFilters(filters);
82 } catch (SQLException e) {
83 e.printStackTrace();
84 }
85 return datasource;
86 }
87
88 // 创建该数据源的事务管理
89 @Bean(name = "secondTransactionManager")
90 public DataSourceTransactionManager secondTransactionManager() throws SQLException {
91 return new DataSourceTransactionManager(secondDataSource());
92 }
93
94 // 创建Mybatis的连接会话工厂实例
95 @Bean(name = "secondSqlSessionFactory")
96 public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource primaryDataSource) throws Exception {
97 final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
98 sessionFactory.setDataSource(primaryDataSource); // 设置数据源bean
99 sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
100 .getResources("classpath:mapper/*.xml")); // 设置mapper文件路径
101 return sessionFactory.getObject();
102 }
103}
两配置文件基本相同，主要区别在与主配置需要加上@Primary注解
监控
新建一个DruidMoniterConfig配置项用于开启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 DruidMoniterConfig {
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
41}
登录
查询数据库验证
