Spring Mybatis多数据源

在项目中,有时会出现同一个服务连接多个数据库的情况存在,例如需要同时连接数据库db1和db2。

项目使用spring boot构建,使用mybatis操作数据库,只需要进行如下配置即可。就会把不同目录下的mapper注入连接到不同的数据库。

application.yml文件

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
mymybatis:
datasource:
url: jdbc:mysql://127.0.0.1:3306/db1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: root
password: 12345678
driverClassName: com.mysql.jdbc.Driver

mymybatis2:
datasource:
url: jdbc:mysql://127.0.0.1:3306/db2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username: root
password: 12345678
driverClassName: com.mysql.jdbc.Driver

spring:
datasource:
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
# 打开PSCache,并且指定每个连接上PSCache的大小
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

mybatis配置1,使db1的mapper都使用数据库db1

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

@Configuration
@MapperScan(basePackages = MybatisDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "mybatisSqlSessionFactory")
public class MybatisDataSourceConfig {

static final String PACKAGE = "org.test.dao.db1";
static final String MAPPER_LOCATION = "classpath:mapper/*.xml";

@Value("${mymybatis.datasource.url}")
private String url;

@Value("${mymybatis.datasource.username}")
private String username;

@Value("${mymybatis.datasource.password}")
private String password;

@Value("${mymybatis.datasource.driverClassName}")
private String driverClassName;

@Value("${spring.datasource.initialSize}")
private int initialSize;

@Value("${spring.datasource.minIdle}")
private int minIdle;

@Value("${spring.datasource.maxActive}")
private int maxActive;

@Value("${spring.datasource.maxWait}")
private int maxWait;

@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;

@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;

@Value("${spring.datasource.validationQuery}")
private String validationQuery;

@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;

@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;

@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;

@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;

@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;

@Value("${spring.datasource.filters}")
private String filters;

@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;

@Autowired
WallFilter wallFilter;

@Bean(name = "mybatisDataSource")
@Primary
public DataSource mybatisDataSource() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);

//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);


// filter
List<Filter> filters = dataSource.getProxyFilters();
boolean isExist = false;
for (Filter filter : filters) {
if (filter instanceof WallFilter) {
((WallFilter) filter).setConfig(wallConfig());
isExist = true;
}
}

if (!isExist) {
filters = new ArrayList<>();
filters.add(wallFilter);
dataSource.setProxyFilters(filters);
}

return dataSource;
}

@Bean(name = "mybatisTransactionManager")
@Primary
public DataSourceTransactionManager mybatisTransactionManager() {
return new DataSourceTransactionManager(mybatisDataSource());
}

@Bean(name = "mybatisSqlSessionFactory")
@Primary
public SqlSessionFactory mybatisSqlSessionFactory(@Qualifier("mybatisDataSource") DataSource mybatisDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(mybatisDataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MybatisDataSourceConfig.MAPPER_LOCATION));

return sessionFactory.getObject();
}

@Bean(name = "wallFilter")
@DependsOn("wallConfig")
public WallFilter wallFilter(WallConfig wallConfig) {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig);
return wallFilter;
}

@Bean(name = "wallConfig")
public WallConfig wallConfig() {
WallConfig wallConfig = new WallConfig();
wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句
return wallConfig;
}

}

mybatis配置2,使db1的mapper都使用数据库db2

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

@Configuration
@MapperScan(basePackages = MybatisDataSource2.PACKAGE, sqlSessionFactoryRef = "mybatisSqlSessionFactory2")
public class MybatisDataSource2 {

static final String PACKAGE = "org.test.dao.db2";
static final String MAPPER_LOCATION = "classpath:mapper/*.xml";

@Value("${mymybatis2.datasource.url}")
private String url;

@Value("${mymybatis2.datasource.username}")
private String username;

@Value("${mymybatis2.datasource.password}")
private String password;

@Value("${mymybatis2.datasource.driverClassName}")
private String driverClassName;


@Value("${spring.datasource.initialSize}")
private int initialSize;

@Value("${spring.datasource.minIdle}")
private int minIdle;

@Value("${spring.datasource.maxActive}")
private int maxActive;

@Value("${spring.datasource.maxWait}")
private int maxWait;

@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;

@Value("${spring.datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;

@Value("${spring.datasource.validationQuery}")
private String validationQuery;

@Value("${spring.datasource.testWhileIdle}")
private boolean testWhileIdle;

@Value("${spring.datasource.testOnBorrow}")
private boolean testOnBorrow;

@Value("${spring.datasource.testOnReturn}")
private boolean testOnReturn;

@Value("${spring.datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;

@Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;

@Value("${spring.datasource.filters}")
private String filters;

@Value("{spring.datasource.connectionProperties}")
private String connectionProperties;

@Autowired
WallFilter wallFilter;

@Bean(name = "mybatisDataSourceMonitor")
public DataSource mybatisDataSource2() {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setUrl(url);
dataSource.setUsername(username);
dataSource.setPassword(password);
dataSource.setDriverClassName(driverClassName);

//具体配置
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
dataSource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
dataSource.setConnectionProperties(connectionProperties);


// filter
List<Filter> filters = dataSource.getProxyFilters();
boolean isExist = false;
for (Filter filter : filters) {
if (filter instanceof WallFilter) {
((WallFilter) filter).setConfig(wallConfig2());
isExist = true;
}
}

if (!isExist) {
filters = new ArrayList<>();
filters.add(wallFilter);
dataSource.setProxyFilters(filters);
}

return dataSource;
}

@Bean(name = "mybatisTransactionManager2")
public DataSourceTransactionManager mybatisTransactionManager2() {
return new DataSourceTransactionManager(mybatisDataSource2());
}

@Bean(name = "mybatisSqlSessionFactory2")
public SqlSessionFactory mybatisSqlSessionFactory2()
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(mybatisDataSource2());
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources(MybatisDataSource2.MAPPER_LOCATION));

return sessionFactory.getObject();
}

@Bean(name = "wallFilter")
@DependsOn("wallConfig")
public WallFilter wallFilter2(WallConfig wallConfig) {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig);
return wallFilter;
}

@Bean(name = "wallConfig")
public WallConfig wallConfig2() {
WallConfig wallConfig = new WallConfig();
wallConfig.setMultiStatementAllow(true);//允许一次执行多条语句
wallConfig.setNoneBaseStatementAllow(true);//允许一次执行多条语句
return wallConfig;
}
}

这样配置之后,dao.db1下的mapper都使操作db1,dao.db2下的mapper都会操作db2。