1 背景
业务开发中,后端通常需要操作多个数据库(可能同类型,也可能不同类型)中的数据,比如主、从数据库的切换场景通常就是同类型切换。但实际需求中,也有可能需要不同类型数据库之间的切换。不论是否同类型,其背后原理一致,只需在配置文件中修改数据库驱动即可。
springboot 提供的AbstractRoutingDataSource实现多数据源动态切换的核心逻辑是:通过AOP的方式在程序运行时,把数据源通过 AbstractRoutingDataSource 动态织入到程序中,灵活地进行数据源切换。本文记录了利用AbstractRoutingDataSource实现在service层通过注解的方式对Mysql和Postgresql两种数据库动态切换。
2 基本配置及maven依赖
首先在application.yml文件中添加数据源配置
spring:
profiles:
active: @pom.env@
http:
encoding:
charset: utf-8
force: true
enabled: true
mybatis:
mapper-locations: classpath:/mapper/*.xml
type-aliases-package: com.cetiti.test.model
application-dev.yml开发环境配置:
spring:
datasource:
db1:
driver-class-name: org.postgresql.Driver
jdbc-url: jdbc:postgresql://10.0.40.70:5432/common_account
username: postgres
password: 123456
db2:
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://10.0.30.232:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: 123456
maven依赖:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.68</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.4</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
</dependencies>
3 代码实现
3.1 编写数据源配置类
姑且命名为:MyBatisConfig
@Configuration
public class MyBatisConfig {
/**
* @return
* @throws Exception
* @Primary 必需指定一个且只能有一个主数据源,否则报错
*/
@Primary
@Bean("mysql")
@ConfigurationProperties(prefix = "spring.datasource.db1")//根据数据源前缀到application.yml读取数据源信息
public DataSource masterDataSource() throws Exception {
return DataSourceBuilder.create().build();
}
@Bean("postgresql")
@ConfigurationProperties(prefix = "spring.datasource.db2")//根据数据源前缀到application.yml读取数据源信息//可以配置更多数据源,到前提是application.yml中存在,而且也需要在枚举类中添加枚举类型
public DataSource slaverDataSource() throws Exception {
return DataSourceBuilder.create().build();
}
/**
* @Qualifier 根据名称进行注入,通常是在具有相同的多个类型的实例的一个注入(例如有多个DataSource类型的实例)
* @DataSourceTypeAnno(DataSourceEnum.MASTER)事务方法需要指定数据源
*/
@Bean("dynamicDataSource")
public DynamicDataSource dynamicDataSource(@Qualifier("mysql") DataSource masterDataSource,
@Qualifier("postgresql") DataSource slaverDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(4);
targetDataSources.put(DataSourceEnum.MYSQL, masterDataSource);
targetDataSources.put(DataSourceEnum.POSTGRESQL, slaverDataSource);
DynamicDataSource dataSource = new DynamicDataSource();
// 该方法是AbstractRoutingDataSource的方法
dataSource.setTargetDataSources(targetDataSources);
// 默认的datasource设置为myTestDbDataSource
dataSource.setDefaultTargetDataSource(masterDataSource);
return dataSource;
}
/**
* 根据数据源创建SqlSessionFactory
*/
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dynamicDataSource,
@Value("mybatis.type-aliases-package") String typeAliasesPackage) throws Exception {
SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
// 指定数据源(这个必须有,否则报错)
factoryBean.setDataSource(dynamicDataSource);
// 下边两句仅仅用于*.xml文件,如果整个持久层操作不需要使用到xml文件的话(只用注解就可以搞定),则不加
// 指定实体类所在的包 //扫描mapper.xml文件包
//factoryBean.setTypeAliasesPackage(typeAliasesPackage);
//factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapping/**/*Mapper.xml"));
return factoryBean.getObject();
}
/**
* 配置事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
return new DataSourceTransactionManager(dataSource);
}
}
3.2 数据源标识枚举类:
public enum DataSourceEnum {
// 主
MYSQL,
//备
POSTGRESQL;
}
3.3 添加AbstractRoutingDataSource实现类
后续在获取数据源标识时会回调该类中的determineCurrentLookupKey方法:
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
//自定义数据源标识上下文容器DataSourceContextHolder,用于存放各标识
return DataSourceContextHolder.getDataSourceType();
}
}
通过追踪源码可见,AbstractRoutingDataSource的getConnection() 方法根据查找 lookupkey 键对不同目标数据源的调用,通常是通过某些线程绑定的事务上下文来实现。
实现逻辑:
定义DynamicDataSource类继承抽象类AbstractRoutingDataSource,并实现了determineCurrentLookupKey()方法。
把配置的多个数据源放在AbstractRoutingDataSource的 targetDataSources和defaultTargetDataSource中,然后通过afterPropertiesSet()方法将数据源分别进行复制到resolvedDataSources和resolvedDefaultDataSource中。
调用AbstractRoutingDataSource的getConnection()的方法的时候,先调用determineTargetDataSource()方法返回DataSource,再进行getConnection(),determineTargetDataSource()源码如下:
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
//获取目标数据源键值
Object lookupKey = this.determineCurrentLookupKey();
//根据键值,获取数据源
DataSource dataSource = (DataSource)this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
//如果没有获取到,证明不存在,则获取默认数据源
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
} else {
return dataSource;
}
}
3.4 定义数据源标识上下文容器,
本质上还是利用ThreadLocal实现数据安全
public class DataSourceContextHolder {
private static final ThreadLocal<DataSourceEnum> CONTEXT_HOLDER = ThreadLocal.withInitial(() -> DataSourceEnum.MYSQL);
public static void setDataSourceType(DataSourceEnum type) {
CONTEXT_HOLDER.set(type);
}
public static DataSourceEnum getDataSourceType() {
return CONTEXT_HOLDER.get();
}
public static void resetDataSourceType() {
CONTEXT_HOLDER.set(DataSourceEnum.MYSQL);
}
}
3.5 自定义注解:
@Retention(RetentionPolicy.RUNTIME)
// 注解可以用在方法上
@Target(ElementType.METHOD)
public @interface DataSourceTypeAnno {
//使用方式在service层方法上添加@DataSourceTypeAnno(DataSourceEnum.数据源枚举类型)用于指定所使用的数据源
DataSourceEnum value() default DataSourceEnum.MYSQL;
}
3.6 定义切面类:
@Component
@Aspect
@Order(-100)
public class DataSourceAspect {
//这里扫描service层方法上的自定义注解,去判断所使用的数据源类型,并动态切换数据源
@Pointcut("execution(* com.cetiti.*.*..*(..)) " +
"&& @annotation(com.cetiti.rm.common.annotation.DataSourceTypeAnno)")
public void dataSourcePointcut() {
}
@Around("dataSourcePointcut()")
public Object doAround(ProceedingJoinPoint pjp) {
MethodSignature methodSignature = (MethodSignature) pjp.getSignature();
Method method = methodSignature.getMethod();
DataSourceTypeAnno typeAnno = method.getAnnotation(DataSourceTypeAnno.class);
DataSourceEnum sourceEnum = typeAnno.value();
if (sourceEnum == DataSourceEnum.MYSQL) {
DataSourceContextHolder.setDataSourceType(DataSourceEnum.MYSQL);
} else if (sourceEnum == DataSourceEnum.POSTGRESQL) {
DataSourceContextHolder.setDataSourceType(DataSourceEnum.POSTGRESQL);
}
Object result = null;
try {
result = pjp.proceed();
} catch (Throwable throwable) {
throwable.printStackTrace();
} finally {
DataSourceContextHolder.resetDataSourceType();
}
return result;
}
}
4 测试
4.1 新建表
在不同类型数据库下新建表如下:
MySQL:
PostgreSQL:
4.2 service层代码
@Service
public class StudentServiceImpl implements StudentService {
@Resource
private MyDbTestMapper myDbTestMapper;
@Override
@DataSourceTypeAnno(value = DataSourceEnum.MYSQL)
public List<Student> getMasterStudent() {
return myDbTestMapper.getStudent();
}
@Override
@DataSourceTypeAnno(value = DataSourceEnum.POSTGRESQL)
public List<Student> getSlaveStudent() {
return myDbTestMapper.getStudent();
}
}
4.3 mapper层代码:
@Mapper
public interface MyDbTestMapper {
/**
* 获取任务状态字典表信息
* @return list
*/
@Select("select s_id as id,s_name as enName,t_id as tid,\n"+
"real_name as cnName,sex,address,enroll_score as enrollScore,tel from student order by enroll_score")
List<Student> getStudent();
}
4.4 controller层代码:
@GetMapping("/test/students")
@ApiOperation("2.1 获取学生信息")
public List<Student> getMyStudent() {
List<Student> list = studentService.getMasterStudent();
System.out.println(list);
list = studentService.getSlaveStudent();
System.out.println(list);
return list;
}
在测试之前还需要在应用主类中添加mapper扫描
@SpringBootApplication
@MapperScan("com.cetiti.test.mapper")
public class RedmineBackendApplication {
public static void main(String[] args) {
SpringApplication.run(MyApplication.class, args);
}
}
可能会遇到测试异常情况:
这事由于mysql版本过低所致,更改至5.5.*以上即可。正常测试结果:
注:基于AbstractRoutingDataSource的多数据源动态切换,可以实现主备切换、读写分离,这么做缺点也很明显,无法动态的增加数据源。