Spring Boot + Mybatis 多数据源配置

应用场景

项目需要同时连接两个不同的数据库poi、pos,暂未涉及到主从,仅仅是因为两个数据库相互独立。

多数据源

首先要将spring boot自带的DataSourceAutoConfiguration禁掉,因为它会读取application.properties文件的spring.datasource.*属性并自动配置单数据源。在@SpringBootApplication注解中添加exclude属性即可:

1
2
3
4
5
6
7
8
@SpringBootApplication(exclude = {
DataSourceAutoConfiguration.class
})
public class WebApplication {
public static void main(String[] args) {
SpringApplication.run(WebApplication.class, args);
}
}

然后在application.yml中配置多数据源连接信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
poi:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/poi
username: root
password: root
pos:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/pos
username: root
password: root

由于我们禁掉了自动数据源配置,因些下一步就需要手动将这些数据源创建出来:

poi数据源配置

注意,配置类需要对DataSourceDataSourceTransactionManagerSqlSessionFactorySqlSessionTemplate四个数据项进行配置;DataSource类型需要引入javax.sql.DataSource

@MapperScan对指定dao包建立映射,确保在多个数据源下,自动选择合适的数据源,而在service层里不需要做特殊说明.

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
@Configuration
@MapperScan(basePackages = "cn.caoler.poi.mapper", sqlSessionTemplateRef = "poiSqlSessionTemplate")
public class BaseDataSourceConfig {
@Bean(name = "poiDataSource")
@ConfigurationProperties(prefix = "spring.datasource.poi")
@Primary
public DataSource setDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = "poiTransactionManager")
@Primary
public DataSourceTransactionManager setTransactionManager1(@Qualifier("poiDataSource") DataSource dataSource) {
return new DruidDataSource();
}

@Bean(name = "poiSqlSessionFactory")
@Primary
public SqlSessionFactory setSqlSessionFactory(@Qualifier("poiDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/poi/*.xml"));
return bean.getObject();
}

@Bean(name = "poiSqlSessionTemplate")
@Primary
public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("poiSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}

经过如上配置,cn.caoler.poi.mapper包下的Mapper接口,都会使用poi数据源。同理可配第二个

pos数据源配置

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
@Configuration
@MapperScan(basePackages = "cn.caoler.pos.mapper", sqlSessionTemplateRef = "poiSqlSessionTemplate")
public class BaseDataSourceConfig {
@Bean(name = "posDataSource")
@ConfigurationProperties(prefix = "spring.datasource.pos")
@Primary
public DataSource setDataSource() {
return DataSourceBuilder.create().build();
}

@Bean(name = "posTransactionManager")
@Primary
public DataSourceTransactionManager setTransactionManager2(@Qualifier("posDataSource") DataSource dataSource) {
return new DruidDataSource();
}

@Bean(name = "posSqlSessionFactory")
@Primary
public SqlSessionFactory setSqlSessionFactory(@Qualifier("posDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/pos/*.xml"));
return bean.getObject();
}

@Bean(name = "posSqlSessionTemplate")
@Primary
public SqlSessionTemplate setSqlSessionTemplate(@Qualifier("posSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
}

完成这些配置后,假设有2个Mapper poi.mapper.UserMapperpos.mapper.RoleMapper,使用前者时会自动连接poi库,后者连接pos库。

踩坑

1. jdbcUrl is required with driverClassName报错

解决方法:在配置文件中使用spring.datasource.jdbcUrl,而不是通常使用的spring.datasource.url。

1
2
3
4
5
6
7
datasource:
type: com.alibaba.druid.pool.DruidDataSource
poi:
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl: jdbc:mysql://127.0.0.1:3306/poi #此处必须使用jdbcUrl而不是url
username: root
password: root

2. 访问 接口时提示:DataSourceTransactionManager 重复

1
2
3
4
5
@Bean(name = "posTransactionManager")
@Primary
public DataSourceTransactionManager setTransactionManager2(@Qualifier("posDataSource") DataSource dataSource) {
return new DruidDataSource();
}

在配置DataSourceTransactionManager时,Bean命名为posTransactionManager和poiTransactionManager,已经做了区分,但是调用时还会提示有两个TransactionManager,产生原因暂不明确,但是解决方法为: 修改函数名,保证函数名之间有严格区分即可(例:setTransactionManager1setTransactionManager2

3. 提示mapper/xxx/*.xml未找到

只要保证程序运行之前,上述位置生成好对应的mapping.xml文件即可,若该位置无xml文件,则会报上述错误。

至此,大功告成。

参考

  1. https://blog.csdn.net/acquaintanceship/article/details/75350653
  2. https://blog.csdn.net/newhanzhe/article/details/80763581
  3. https://blog.csdn.net/neosmith/article/details/61202084
0%