如何实现多数据源
1.当执行数据库持久化操作 ,只要集成了Spring就一定会通过DataSourceUtils获取Connection
2. 通过Spring注入的DataSource获取Connection 即可执行数据库操作所以思路就是:只需配置一个实现了DataSource的Bean, 然后根据业务动态提供Connection即可
3.其实Spring已经提供一个DataSource实现类用于动态切换数据源—AbstractRoutingDataSource 4.分析AbstractRoutingDataSource即可实现动态数据源切换
通过AbstractRoutingDataSource实现动态数据源
通过这个类可以实现动态数据源切换。如下是这个类的成员变量
private Map<Object, Object> targetDataSources;
private Object defaultTargetDataSource;
private Map<Object, DataSource> resolvedDataSources;
- targetDataSources保存了key和数据库连接的映射关系
- defaultTargetDataSource标识默认的连接
- resolved DataSources这个数据结构是通过targetDataSources构建而来 ,存储结构也是数据库标识和数据源的 映射关系
而AbstractRoutingDataSource实现了InitializingBean接口 ,并实现了afterPropertiesSet方法。afterPropertiesSet方法 是初始化bean的时候执行 ,通常用作数据初始化。
resolved DataSources就是在这里赋值
@Override
public void afterPropertiesSet() {
. . .
this.resolvedDataSources = new HashMap<Object, DataSource> (this.targetDataSources.size()); //初始 化resolvedDataSources
//循环targetDataSources,并添加到resolvedDataSources中
for (Map.Entry<Object, Object> entry : this.targetDataSources.entrySet()) {
Object lookupKey = resolveSpecified LookupKey(entry.getKey());
DataSource dataSource = resolveSpecifiedDataSource(entry.getValue());
this.resolvedDataSources.put(lookupKey, dataSource);
}
. . .
}
5.so! 我们只需创建AbstractRoutingDataSource实现类DynamicDataSource然后 始化targetDataSources和key为 数据源标识(可以是字符串、枚举、都行 ,因为标识是Object)、defaultTargetDataSource即可
6.后续当调用AbstractRoutingDataSource.getConnection 会接着调用提供的模板方法:
determineTargetDataSource
7.通过determineTargetDataSource该方法返回的数据库标识 从resolvedDataSources 中拿到对应的数据源 8.so!我们只需DynamicDataSource中实现determineTargetDataSource为其提供一个数据库标识
总结: 在整个代码中我们只需做4件大事:
1. 定义AbstractRoutingDataSource实现类DynamicDataSource
2. 初始化时为targetDataSources设置 不同数据源的DataSource和标识、及defaultTargetDataSource
3. 在determineTargetDataSource中提供对应的数据源标识即可
4、切换数据源识即可
多数据源切换方式
AOP+自定义注解
application配置
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
datasource1:
url: jdbc:mysql://127.0.0.1:3306/datasource1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
datasource2:
url: jdbc:mysql://127.0.0.1:3306/datasource2?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
username: root
password: 123456
initial-size: 1
min-idle: 1
max-active: 20
test-on-borrow: true
driver-class-name: com.mysql.cj.jdbc.Driver
DataSourceConfig配置
@Configuration
public class DataSourceConfig {
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource1")
public DataSource dataSource1() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
@Bean
@ConfigurationProperties(prefix = "spring.datasource.datasource2")
public DataSource dataSource2() {
// 底层会自动拿到spring.datasource中的配置, 创建一个DruidDataSource
return DruidDataSourceBuilder.create().build();
}
/* @Bean
public Interceptor dynamicDataSourcePlugin(){
return new DynamicDataSourcePlugin();
}
*/
@Bean
public DataSourceTransactionManager transactionManager1(DynamicDataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
@Bean
public DataSourceTransactionManager transactionManager2(DynamicDataSource dataSource){
DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager();
dataSourceTransactionManager.setDataSource(dataSource);
return dataSourceTransactionManager;
}
}
DynamicDataSource配置
@Component
@Primary // 将该Bean设置为主要注入Bean
public class DynamicDataSource extends AbstractRoutingDataSource {
// 当前使用的数据源标识
public static ThreadLocal<String> name=new ThreadLocal<>();
// 写
@Autowired
DataSource dataSource1;
// 读
@Autowired
DataSource dataSource2;
// 返回当前数据源标识
@Override
protected Object determineCurrentLookupKey() {
return name.get();
}
@Override
public void afterPropertiesSet() {
// 为targetDataSources初始化所有数据源
Map<Object, Object> targetDataSources=new HashMap<>();
targetDataSources.put("W",dataSource1);
targetDataSources.put("R",dataSource2);
super.setTargetDataSources(targetDataSources);
// 为defaultTargetDataSource 设置默认的数据源
super.setDefaultTargetDataSource(dataSource1);
super.afterPropertiesSet();
}
}
自定义注解
@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface WR {
String value() default "W";
}
@Component
@Aspect
public class DynamicDataSourceAspect implements Ordered {
// 前置
@Before("within(com.tuling.dynamic.datasource.service.impl.*) && @annotation(wr)")
public void before(JoinPoint point, WR wr){
String name = wr.value();
DynamicDataSource.name.set(name);
System.out.println(name);
}
@Override
public int getOrder() {
return 0;
}
// 环绕通知
}
@Service
public class FrendImplService implements FrendService {
@Autowired
FrendMapper frendMapper;
@Override
@WR("R") // 库2
public List<Frend> list() {
return frendMapper.list();
}
@Override
@WR("W") // 库1
public void save(Frend frend) {
frendMapper.save(frend);
}
}
MyBatis插件
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Ob
ject.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, R
owBounds.class,
ResultHandler.class})})
public class DynamicDataSourcePlugin implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] objects = invocation.getArgs();
MappedStatement ms = (MappedStatement) objects[0];
// 读方法
if (ms.getSqlCommandType().equals(SqlCommandType.SELECT)) {
DynamicDataSource.name.set("R");
} else {
// 写方法
DynamicDataSource.name.set("W");
}
// 修改当前线程要选择的数据源的key
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
if (target instanceof Executor) {
return Plugin.wrap(target, this);
} else {
return target;
}
}
@Override
public void setProperties(Properties properties) {
}
}
Spring集成多个MyBatis框架 实现多数据源
@MapperScan(basePackages = "com.tuling.dynamic.datasource.mapper.w", sqlSessionFactoryRef = "wSqlSessionFactory")
@Bean
@Primary
public SqlSessionFactory wSqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource1)throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource1);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath:mapper/w/*.xml"));
/*主库设置sql控制台打印*/
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLogImpl(StdOutImpl.class);
sessionFactory.setConfiguration(configuration);
return sessionFactory.getObject();
}
@MapperScan(basePackages = "com.tuling.dynamic.datasource.mapper.r", sqlSessionFactoryRef = "rSqlSessionFactory")
@Bean
public SqlSessionFactory rSqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource2)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource2);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/r/*.xml"));
/*从库设置sql控制台打印*/
org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
configuration.setLogImpl(StdOutImpl.class);
sessionFactory.setConfiguration(configuration);
return sessionFactory.getObject();
}