背景
在开发过程中,很多时候都会有垮数据库操作数据的情况,需要同时配置多套数据源,即多个数据库,保证不同的业务在不同的数据库执行操作,通过mapper来灵活的切换数据源。
本文以sqlserver和mysql混合数据源配置为例。
配置多数据源方案
1、通过mapper配置数据源
2、配置动态数据源
具体实现
1)、 通过mapper配置数据源
(1)maven配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
(2)服务配置文件,application.yml
server:
port: 9900
spring:
datasource:
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://localhost:10009;DatabaseName=test
username: sa
password: 654321
db2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
username: root
password: 123456
(3)添加数据库配置
@Configuration
public class DataSourceConfig {
@Bean(name = "maindb")
@Primary
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource businessDbDataSource() {
return new HikariDataSource();
}
@Bean(name = "seconddb")
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource newhomeDbDataSource() {
return new HikariDataSource();
}
}
(4)单独配置每个数据源信息
注意:@Primary该注解理解为默认数据源
包路径配置可对比参考后面的项目结构截图
@Configuration
@MapperScan(basePackages = {"com.gxin.datasource.dao.maindb"}, sqlSessionFactoryRef = "sqlSessionFactoryMaindb")
public class DatasourceMainConfig {
@Autowired
@Qualifier("maindb")
private DataSource dataSourceMaindb;
@Bean
@Primary
public SqlSessionFactory sqlSessionFactoryMaindb() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSourceMaindb);
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/maindb/*.xml"));
// 打印sql日志
// org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
// configuration.setLogImpl(StdOutImpl.class);
// factoryBean.setConfiguration(configuration);
return factoryBean.getObject();
}
@Bean
@Primary
public SqlSessionTemplate sqlSessionTemplateMaindb() throws Exception {
return new SqlSessionTemplate(sqlSessionFactoryMaindb());
}
}
@Configuration
@MapperScan(basePackages = {"com.gxin.datasource.dao.seconddb"}, sqlSessionFactoryRef = "sqlSessionFactorySeconddb")
public class DatasourceSecondConfig {
@Autowired
@Qualifier("seconddb")
private DataSource dataSourceSeconddb;
@Bean
public SqlSessionFactory sqlSessionFactorySeconddb() throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSourceSeconddb);
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/seconddb/*.xml"));
// 打印sql日志
// org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
// configuration.setLogImpl(StdOutImpl.class);
// factoryBean.setConfiguration(configuration);
return factoryBean.getObject();
}
@Bean
public SqlSessionTemplate sqlSessionTemplateSeconddb() throws Exception {
return new SqlSessionTemplate(sqlSessionFactorySeconddb());
}
}
(5)根据每个单独的数据源的配置信息搭建mapper接口和mapper.xml文件
![](https://i-blog.csdnimg.cn/blog_migrate/2fad1d13691f4b0ece7d0d249c51f3d7.png)
![](https://i-blog.csdnimg.cn/blog_migrate/07ee8e0174457593d921c7e72630735f.png)
(6)配置mapper多数据源完成,下图为完整的项目结构
![](https://i-blog.csdnimg.cn/blog_migrate/652d28f15105012abaeadda686fea9e5.png)
2)、 配置动态数据源
(1)maven配置
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
(2)服务配置文件,application.yml
server:
port: 9901
spring:
main:
allow-bean-definition-overriding: true
datasource:
db1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc-url: jdbc:sqlserver://localhost:10009;DatabaseName=test
username: sa
password: 654321
db2:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://localhost:3306/test2?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowMultiQueries=true
username: root
password: 123456
(3)添加数据源列表
public interface DataSourceConstant {
/**
* 默认数据库
*/
String MAIN = "MAIN";
/**
* 第二数据库
*/
String SECOND = "SECOND";
}
(4)动态数据源数据库信息配置
@Configuration
@EnableAutoConfiguration(exclude = { DataSourceAutoConfiguration.class })
public class DynamicDataSourceConfig {
// 核心数据库
@Bean(name = DataSourceConstant.MAIN)
@ConfigurationProperties(prefix = "spring.datasource.db1")
public DataSource getMAINDataSource() {
return new HikariDataSource();
}
// 第二数据库
@Bean(name = DataSourceConstant.SECOND)
@ConfigurationProperties(prefix = "spring.datasource.db2")
public DataSource getSECONDDataSource() {
return new HikariDataSource();
}
@Bean
@Primary
public DataSource dynamicDataSource() {
Map<Object, Object> dataSourceMap = new HashMap<>();
dataSourceMap.put(DataSourceConstant.MAIN, getMAINDataSource());
dataSourceMap.put(DataSourceConstant.SECOND, getSECONDDataSource());
//设置动态数据源
DynamicDataSource dynamicDataSource = new DynamicDataSource();
dynamicDataSource.setTargetDataSources(dataSourceMap);
dynamicDataSource.setDefaultTargetDataSource(getMAINDataSource());
return dynamicDataSource;
}
}
(5)添加动态数据源策略获取配置,继承AbstractRoutingDataSource
@Component
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// 此处暂时返回固定 master 数据源, 后面按动态策略修改
return DynamicDataSourceContextHolder.getContextKey();
}
}
(6)数据源切换策略配置
public class DynamicDataSourceContextHolder {
/**
* 动态数据源名称上下文
*/
private static final ThreadLocal<String> DATASOURCE_CONTEXT_KEY_HOLDER = new ThreadLocal<>();
/**
* 设置/切换数据源
*/
public static void setContextKey(String key) {
DATASOURCE_CONTEXT_KEY_HOLDER.set(key);
}
/**
* 获取数据源名称
*/
public static String getContextKey() {
String key = DATASOURCE_CONTEXT_KEY_HOLDER.get();
return key == null ? DataSourceConstant.MAIN : key;
}
/**
* 删除当前数据源名称
*/
public static void removeContextKey() {
DATASOURCE_CONTEXT_KEY_HOLDER.remove();
}
}
(6.1) 这里写掉了,补充数据库会话配置
@Configuration
public class SqlSessionConfig {
@Autowired
private DynamicDataSource source;
public SqlSessionConfig() {
}
@Bean(name = {"sqlSessionFactoryBean"})
public SqlSessionFactoryBean getSessionFactory() throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(this.source);
factory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
// 打印sql日志
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLogImpl(StdOutImpl.class);
factory.setConfiguration(configuration);
return factory;
}
@Bean(name = {"sqlSession"})
public SqlSessionTemplate getSqlSession() throws Exception {
return new SqlSessionTemplate(Objects.requireNonNull(getSessionFactory().getObject()));
// SqlSessionFactory factory = this.getSessionFactory().getObject();
// return new SqlSessionTemplate(factory, ExecutorType.BATCH);
}
@Bean
public DataSourceTransactionManager getDataSourceTransaction() {
DataSourceTransactionManager manager = new DataSourceTransactionManager();
manager.setDataSource(this.source);
return manager;
}
}
(7) 添加切换数据源标识注解,默认为MAIN数据源
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface DBS {
/**
* 数据源名称
*/
String value() default DataSourceConstant.MAIN;
}
(8)通过配置的数据源标识注解,动态切换数据源
@Aspect
@Component
public class DynamicDataSourceAspect {
@Pointcut("@annotation(com.gxin.dynamicdatasource.config.DBS)")
public void dataSourcePointCut() {
}
@Around("dataSourcePointCut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
String dsKey = getDSAnnotation(joinPoint).value();
DynamicDataSourceContextHolder.setContextKey(dsKey);
try {
return joinPoint.proceed();
} finally {
DynamicDataSourceContextHolder.removeContextKey();
}
}
/**
* 根据类或方法获取数据源注解
*/
private DBS getDSAnnotation(ProceedingJoinPoint joinPoint) {
Class<?> targetClass = joinPoint.getTarget().getClass();
DBS dsAnnotation = targetClass.getAnnotation(DBS.class);
// 先判断类的注解,再判断方法注解
if (Objects.nonNull(dsAnnotation)) {
return dsAnnotation;
} else {
MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
return methodSignature.getMethod().getAnnotation(DBS.class);
}
}
}
(9)到此动态数据源配置完成,一下的为使用情况,需要使用哪个数据源添加注解配置即可
@Repository
public interface TestMapper {
int getModelcount();
@DBS(DataSourceConstant.SECOND)
int getUsercount();
}
(10)service层
@Service
public class ServiceInfo {
@Autowired
private TestMapper mainMapper;
public void datasource() {
int modelcount = mainMapper.getModelcount();
System.out.println(modelcount);
int usercount = mainMapper.getUsercount();
System.out.println(usercount);
}
}
(11)mapper. xml结构目录
![](https://i-blog.csdnimg.cn/blog_migrate/06e6d60796d9f48f190f0125478e3851.png)
(12)完整的项目结构截图
![](https://i-blog.csdnimg.cn/blog_migrate/9ffd4e1b39a15c1a85c1ff6bef36d2da.png)
收工图
![](https://i-blog.csdnimg.cn/blog_migrate/5338d0d418ae2f506507542a213eae6c.png)