多数据源配置-基于Spring JDBC
一、基于注解配置自动切换
实现思路：
- 可以配置多个数据源，给不同的xml文件指定不同的数据源，这种方式只能实现文件级别的固定数据源配置，使用起来不方便
- 使用扩展
org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource实现动态切换的数据源
1.1 Mybatis配置
<bean id="basicDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="initialSize" value="5" />
<property name="maxActive" value="300" />
<property name="maxIdle" value="300" />
<property name="minIdle" value="5" />
<property name="maxWait" value="5000 " />
<property name="validationQuery" value="select 1 from dual" />
<property name="testOnBorrow" value="true" />
<property name="testOnReturn" value="false" />
<property name="testWhileIdle" value="false" />
<property name="timeBetweenEvictionRunsMillis" value="1800000" />
<property name="numTestsPerEvictionRun" value="6" />
<property name="minEvictableIdleTimeMillis" value="1800000" />
</bean>
<!-- ds1 -->
<bean id="ds1" parent="basicDataSource">
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<!-- ds2 -->
<bean id="ds2" parent="basicDataSource">
<property name="url" value="${jdbc.url.test}" />
<property name="username" value="${jdbc.username.test}" />
<property name="password" value="${jdbc.password.test}" />
</bean>
<!-- data source 抽象类 -->
<bean id="dataSource" class="com.worley.stat.wordanalysis.dao.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="ds1" key="ds1"></entry>
<entry value-ref="ds2" key="ds2"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="ds1"></property>
</bean>
<!-- myBatis文件 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
<property name="mapperLocations" value="classpath:/mapping/**/*Mapper.xml"/>
<property name="configLocation" value="classpath:/spring/configuration.xml" />
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.worley.stat.wordanalysis.dao" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 注解方式配置事物 -->
<tx:annotation-driven transaction-manager="transactionManager" />
1.2 相关Java逻辑
public class DataSourceHandle {
private DataSourceHandle() {
throw new IllegalStateException("Utility class");
}
private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static void setDataSourceType(String dataSourceType) {
contextHolder.set(dataSourceType);
}
public static String getDataSourceType() {
return contextHolder.get();
}
public static void clearDataSourceType() {
contextHolder.remove();
}
}
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
// 在进行DAO操作前，通过上下文环境变量，获得数据源的类型
return DataSourceHandle.getDataSourceType();
}
}
@Aspect
@Order(-1)
@Component
public class MultipleDataSourceAspectAdvice {
private static final Logger log = LoggerFactory.getLogger(MultipleDataSourceAspectAdvice.class);
@Around("execution(* com.test.service..*(..))")
public void changeDataSourceAndProcess(ProceedingJoinPoint jp) throws Throwable {
// 暂存当前数据库类型
String beforeDataSourceType = DataSourceHandle.getDataSourceType();
MethodSignature msig = (MethodSignature) jp.getSignature();
try {
Class<?> targetClass = msig.getDeclaringType();
Method targetMethod = targetClass.getDeclaredMethod(msig.getName(), msig.getMethod().getParameterTypes());
TargetDataSource ds = targetMethod.getAnnotation(TargetDataSource.class);
if (ds == null) {
ds = targetClass.getAnnotation(TargetDataSource.class);
}
String dbTypeCode = (ds == null) ? null : ds.value().getCode();
if (log.isDebugEnabled()) {
log.debug("changeDataSource: {} -> {}", msig, dbTypeCode);
}
// 切换数据库类型
DataSourceHandle.setDataSourceType(dbTypeCode);
// 处理当前业务
jp.proceed();
} finally {
if (log.isDebugEnabled()) {
log.debug("restoreDataSource: {} -> {}", msig, beforeDataSourceType);
}
// 切换回当前的数据库类型
DataSourceHandle.setDataSourceType(beforeDataSourceType);
}
}
@Around("@within(ds) || @annotation(ds)")
public void changeDataSourceAndProcess(ProceedingJoinPoint jp, TargetDataSource ds) throws Throwable {
// 暂存当前数据库类型
String beforeDataSourceType = DataSourceHandle.getDataSourceType();
try {
if (ds == null) {
Class<?> targetClass = jp.getSignature().getDeclaringType();
// 方法上没有注解则取类的
ds = targetClass.getAnnotation(TargetDataSource.class);
}
String dbTypeCode = (ds == null) ? null : ds.value().getCode();
if (log.isDebugEnabled()) {
log.debug("changeDataSource: {} -> {}", jp.getSignature(), dbTypeCode);
}
// 切换数据库类型
DataSourceHandle.setDataSourceType(dbTypeCode);
// 处理当前业务
jp.proceed();
} finally {
if (log.isDebugEnabled()) {
log.debug("restoreDataSource: {} -> {}", jp.getSignature(), beforeDataSourceType);
}
// 切换回当前的数据库类型
DataSourceHandle.setDataSourceType(beforeDataSourceType);
}
}
}
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String value();
}
// 在相关service方法上指定需要切换的数据源
@TargetDataSource("ds2")
public void showAllTablesDb2() {
List<String> tables = testMapper.selectAllTables();
log.info("---------------- tables: {}", tables);
}
二、各数据源独立配置
实现思路：
- 按目录固定分配数据源
- 每个数据源都有各自的事务管理器，相当于又重复配置了一遍（只是数据源和dao目录不同）
@Configuration
@MapperScan(basePackages = "com.worley.base.dao.cdm", sqlSessionTemplateRef = "cdmSqlSessionTemplate")
public class CdmDataSourceConfig {
@Bean(name = "cdmDataSourceProperties")
@ConfigurationProperties(prefix = "spring.datasource.cdm")
public DataSourceProperties cdmDataSourceProperties() {
return new DataSourceProperties();
}
@Bean(name = "cdmDataSource")
public DataSource cdmDataSource(@Qualifier("cdmDataSourceProperties") DataSourceProperties dataSourceProperties) {
return dataSourceProperties.initializeDataSourceBuilder().build();
}
@Bean("cdmSqlSessionFactory")
public SqlSessionFactory cdmSqlSessionFactory(@Qualifier("cdmDataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
sqlSessionFactory.setDataSource(dataSource);
sqlSessionFactory.setMapperLocations(
new PathMatchingResourcePatternResolver().getResources("classpath*:mapping/cdm/**/*.xml"));
sqlSessionFactory.setPlugins(new TableSegInterceptor());
// 下划线转驼峰
Objects.requireNonNull(sqlSessionFactory.getObject()).getConfiguration().setMapUnderscoreToCamelCase(true);
return sqlSessionFactory.getObject();
}
@Bean(name = "cdmTransactionManager")
public DataSourceTransactionManager cdmTransactionManager(@Qualifier("cdmDataSource") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "cdmSqlSessionTemplate")
public SqlSessionTemplate cdmSqlSessionTemplate(
@Qualifier("cdmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
}
