简介

今天遇到一个需求，我们开发的项目需要同时连接 DB2 和 SQLite 数据库，刚开始认为可以复制一份当前数据源的配置就可以了，结果发现失败了；原因是：SqlSessionFactory 只能是单例模式，所以根本无法通过这种方式实现多数据源的开发

解决方案：手动创建一个动态的数据源，将 DB2 和 SQlite 数据源放入，再将该动态数据源放入 ThreadLocal，然后在代码中指定需要的数据源即可

实现步骤

1. 新建 DynamicDataSource 类，添加如下代码

/*** @description dynamic dataSource:sqlite and db2*/
public class DynamicDataSource extends AbstractRoutingDataSource {public static final ThreadLocal < String > contextHolder = new ThreadLocal < String > ();public static final String DBTYPE_SQLITE = "sqlite";public static final String DBTYPE_DB2 = "db2";public static void setDBType(String customerType) {contextHolder.set(customerType);}public static String getDBType() {return contextHolder.get();}public static void clearDBType() {contextHolder.remove();}// 返回目标数据库@Overrideprotected DataSource determineTargetDataSource() {return super.determineTargetDataSource();}@Overrideprotected Object determineCurrentLookupKey() {return getDBType();}
}

2. ApplicationConfig 配置类关于数据源配置如下

/*** @author cddufu@cn.ibm.com* @date 2018-08-21* @description dynamic dataSource:sqlite and db2*/
public class DynamicDataSource extends AbstractRoutingDataSource {public static final ThreadLocal < String > contextHolder = new ThreadLocal < String > ();public static final String DBTYPE_SQLITE = "sqlite";public static final String DBTYPE_DB2 = "db2";public static void setDBType(String customerType) {contextHolder.set(customerType);}public static String getDBType() {return contextHolder.get();}public static void clearDBType() {contextHolder.remove();}// 返回目标数据库@Overrideprotected DataSource determineTargetDataSource() {return super.determineTargetDataSource();}@Overrideprotected Object determineCurrentLookupKey() {return getDBType();}
}
2. ApplicationConfig 配置类关于数据源配置如下/* 数据源配置开始 */
@Bean(destroyMethod = "close")
public BasicDataSource getSqliteDataSource() {BasicDataSource sqliteDataSource = new BasicDataSource();Application appConfig = getAppConfig();sqliteDataSource.setDriverClassName(appConfig.getSqliteDriver());sqliteDataSource.setUrl(appConfig.getSqliteUrl());return sqliteDataSource;
}@
Bean(destroyMethod = "close")
public BasicDataSource getDB2DataSource() {BasicDataSource db2DataSource = new BasicDataSource();Application appConfig = getAppConfig();db2DataSource.setDriverClassName(appConfig.getDb2Driver());db2DataSource.setUrl(appConfig.getDb2Url());db2DataSource.setUsername(appConfig.getDb2User());db2DataSource.setPassword(appConfig.getDb2Password());return db2DataSource;
}// 配置动态数据源，同时添加 sqlite 和 db2 数据源
@
Bean
public DynamicDataSource getDynamicDataSource() {DynamicDataSource dynamicDataSource = new DynamicDataSource();Map < Object, Object > targetDataSources = new HashMap < Object, Object > ();BasicDataSource sqliteDataSource = getSqliteDataSource();BasicDataSource db2DataSource = getDB2DataSource();targetDataSources.put("sqlite", sqliteDataSource);targetDataSources.put("db2", db2DataSource);dynamicDataSource.setTargetDataSources(targetDataSources);dynamicDataSource.setDefaultTargetDataSource(sqliteDataSource);return dynamicDataSource;
}@
Bean
public SqlSessionFactoryBean getFactoryBean() {SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();DynamicDataSource dynamicDataSource = getDynamicDataSource();factoryBean.setDataSource(dynamicDataSource);return factoryBean;
}@
Bean
public SqlSessionTemplate getSqlSession() throws Exception {SqlSessionFactoryBean factoryBean = getFactoryBean();SqlSessionTemplate sqlSession = new SqlSessionTemplate(factoryBean.getObject());return sqlSession;
}// Spring 事务支持
@
Bean
public DataSourceTransactionManager getTransaction() {DataSourceTransactionManager transaction = new DataSourceTransactionManager();DynamicDataSource dynamicDataSource = getDynamicDataSource();transaction.setDataSource(dynamicDataSource.determineTargetDataSource());return transaction;
}
/* 数据源配置完成 */

3. 使用的时候，在具体执行的方法上添加指定数据源的代码即可

@Repository
public class TestDao implements ITestDao {@ResourceSqlSessionTemplate sqlSession;@
Overridepublic String test() {// 要连接 DB2 的话就需要添加如下这句代码DynamicDataSource.setDBType(DynamicDataSource.DBTYPE_DB2);String result = sqlSession.getMapper(ITestDao.class).test();// 清空当前线程中的数据源DynamicDataSource.clearDBType();return result;}
}

优化：第三步中的代码结构符合典型的 AOP 结构，所以，我们可以添加一个 AspectDataBase 类，控制数据源的指定和清理，并通过注解的方式让代码看起来更加优雅

1. 首先添加一个注解类：DataBase.class，并添加如下代码

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD) // 仅作用于方法上
@Documented
public @interface DataBase {public String value() default "";
}

2. 添加 AspectDataBase.class 做 AOP 控制，指定 DAO 层方法中需要使用的数据库

@Component
@Aspect
public class AspectDataBase {@Pointcut("execution(* com.ibm.oneteam.ah.automation.dao.*.*(..))")public void pointCut() {//nothing to do}public void methodExecuteBefore(JoinPoint jp) {//nothing to do}@Around("pointCut()")public Object methodExecuteAround(ProceedingJoinPoint pjp) throws Throwable {MethodSignature signature = (MethodSignature) pjp.getSignature();Object target = pjp.getTarget();Method method = target.getClass().getMethod(signature.getName(), signature.getParameterTypes());DataBase dataBase = method.getAnnotation(DataBase.class);if (null != dataBase) {// 设置数据源DynamicDataSource.setDBType(dataBase.value());Object result = pjp.proceed();// 清除数据源DynamicDataSource.clearDBType();return result;} else {return pjp.proceed();}}public void methodExecuteAfter(JoinPoint jp) {//nothing to do}
}

4. 在具体需要使用数据库操作的 DAO 层方法上添加 @DataBase 注解即可，如下所示

@DataBase(DynamicDataSource.DBTYPE_SQLITE)
public Admin getById(int id) {return sqliteSqlSession.getMapper(ICommonDao.class).getById(id);
}

注意

1. 本项目使用纯代码进行配置，要使用 AOP 功能，还需要在 ApplicationConfig 类上添加 @EnableAspectJAutoProxy 注解，该注解相当于 aop:aspectj-autoproxy/(打开aop支持)

2. 第 1 步 和第 2 步中的红色代码，如果不这样配置就会导致添加事务后数据源无法切换的问题

3. Service 中不同数据源的方法，不能嵌套在一起，否则会抛出异常，因为两个不同的数据源交叉调用而导致事务无法正常工作；这个已经涉及到分布式事务了，可查看 springCloud 部分的笔记