在实际开发中,我们一个项目可能会用到多个数据库,通常一个数据库对应一个数据源。
简要原理:
1)DatabaseType列出所有的数据源的key---key
2)DatabaseContextHolder是一个线程安全的DatabaseType容器,并提供了向其中设置和获取DatabaseType的方法
3)DynamicDataSource继承AbstractRoutingDataSource并重写其中的方法determineCurrentLookupKey(),在该方法中使用DatabaseContextHolder获取当前线程的DatabaseType
4)MyBatisConfig中生成2个数据源DataSource的bean---value
5)MyBatisConfig中将1)和4)组成的key-value对写入到DynamicDataSource动态数据源的targetDataSources属性(当然,同时也会设置2个数据源其中的一个为DynamicDataSource的defaultTargetDataSource属性中)
6)将DynamicDataSource作为primary数据源注入到SqlSessionFactory的dataSource属性中去,并且该dataSource作为transactionManager的入参来构造DataSourceTransactionManager
7)使用的时候,在dao层或service层先使用DatabaseContextHolder设置将要使用的数据源key,然后再调用mapper层进行相应的操作,建议放在dao层去做(当然也可以使用spring aop+自定注解去做)
注意:在mapper层进行操作的时候,会先调用determineCurrentLookupKey()方法获取一个数据源(获取数据源:先根据设置去targetDataSources中去找,若没有,则选择defaultTargetDataSource),之后在进行数据库操作。
代码如下:
1. application.properties
jdbc.filters=stat
jdbc.maxActive=20
jdbc.initialSize=1
jdbc.maxWait=60000
jdbc.minIdle=1
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.minEvictableIdleTimeMillis=300000
jdbc.validationQuery=select 'x' from dual
jdbc.testWhileIdle=true
jdbc.testOnBorrow=false
jdbc.testOnReturn=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=300
jdbc.poolPreparedStatements=true
jdbc.maxOpenPreparedStatements=20
jdbc.maxPoolPreparedStatementPerConnectionSize=20
jdbc.logAbandoned=true
mybatis.mapperLocations=classpath:mapper/oracle/*.xml
mybatis.typeAliasesPackage=xxx.model
jdbcyx.driverClassName=oracle.jdbc.driver.OracleDriver
jdbcyx.type=com.alibaba.druid.pool.DruidDataSource
jdbcyx.url=
jdbcyx.username=
jdbcyx.password=
jdbcwx.driverClassName=oracle.jdbc.driver.OracleDriver
jdbcwx.type=
jdbcwx.url=
jdbcwx.username=
jdbcwx.password=
jdbclocal.driverClassName=com.mysql.jdbc.Driver
jdbclocal.type=com.alibaba.druid.pool.DruidDataSource
jdbclocal.url=
jdbclocal.username=
jdbclocal.password=
2. DatabaseContextHolder
public class DatabaseContextHolder {
private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>();
public static DatabaseType getDatabaseType(){
return contextHolder.get();
}
public static void setDatabaseType(DatabaseType type){
contextHolder.set(type);
}
public static void clear() { contextHolder.remove();; }
}
3. DatabaseType
public enum DatabaseType {
localDataSource,
wxDataSource,
yxDataSource,
}
4. DataSourceAspect EcBaseData是server 层请求对象,所有对象extend EcBaseData。
@Aspect
@Order(-10)
@Component
public class DataSourceAspect {
private static final Logger log = LoggerFactory.getLogger(DataSourceAspect.class);
@Before("execution(* com.xx.service.*.*(..))")
public void setDataSourceKey(JoinPoint point){
//连接点所属的类实例是ShopDao point.getTarget() instanceof ShopDao
if(point.getArgs().length>0){
if(point.getArgs()[0] instanceof EcBaseData){
EcBaseData ecBaseData = (EcBaseData)point.getArgs()[0];
log.info("----getTarget: DatabaseType: {}",ecBaseData.getSpaceName());
if(ecBaseData.getSpaceName().equals("local")){
DatabaseContextHolder.setDatabaseType(DatabaseType.localDataSource);
}else if(ecBaseData.getSpaceName().equals("wx")){
DatabaseContextHolder.setDatabaseType(DatabaseType.wxDataSource);
}else if(ecBaseData.getSpaceName().equals("wxdev")){
DatabaseContextHolder.setDatabaseType(DatabaseType.wxDataSource);
}else if(ecBaseData.getSpaceName().equals("yx")){
DatabaseContextHolder.setDatabaseType(DatabaseType.yxDataSource);
}else{
DatabaseContextHolder.setDatabaseType(DatabaseType.localDataSource);
}
}else{
log.info("----getArgs: DatabaseType.localDataSource----");
DatabaseContextHolder.setDatabaseType(DatabaseType.localDataSource);
}
}else{
log.info("----getArgs =0: DatabaseType.localDataSource----");
DatabaseContextHolder.setDatabaseType(DatabaseType.localDataSource);
}
}
/** * 对业务层方法做aop拦截,在业务层方法之后执行 * @author 10148712 * @param point 切点 */ @After("execution(* com.xx.service.*.*(..))") public void after(JoinPoint point){ DatabaseContextHolder.clear(); }
}
setDataSourceKey 方法中如何获取httprequest,然后去request中header部分数据
public void setDataSourceKey(JoinPoint point){
String tenantId ="";
ServletRequestAttributes servletReqAttr = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
if(servletReqAttr!=null)
{
HttpServletRequest request = servletReqAttr.getRequest();
tenantId =request.getHeader(“tenantId”);
}
if(StringHelper.isEmpty(tenantId)){
log.info("---tenantId is null DatabaseType.daasDataSource----");
DatabaseContextHolder.setDatabaseType(DatabaseType.daasDataSource);
}else{
}
5. DynamicDataSource
public class DynamicDataSource extends AbstractRoutingDataSource{
protected Object determineCurrentLookupKey(){
return DatabaseContextHolder.getDatabaseType();
}
}
6.MyBatisConfig
@Configuration
@MapperScan(basePackages="xx.mapper")
public class MyBatisConfig {
private static final Logger log = LoggerFactory.getLogger(MyBatisConfig.class);
@Autowired
private Environment env;
@Bean
public DataSource yxDataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", env.getProperty("jdbcyx.driverClassName"));
props.put("url", env.getProperty("jdbcyx.url"));
props.put("username", env.getProperty("jdbcyx.username"));
props.put("password", env.getProperty("jdbcyx.password"));
props.put("type", env.getProperty("jdbcyx.type"));
this.setCommonJDBCProperties(props);
return DruidDataSourceFactory.createDataSource(props);
}
@Bean
public DataSource wxDataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", env.getProperty("jdbcwx.driverClassName"));
props.put("url", env.getProperty("jdbcwx.url"));
props.put("username", env.getProperty("jdbcwx.username"));
props.put("password", env.getProperty("jdbcwx.password"));
props.put("type", env.getProperty("jdbcwx.type"));
this.setCommonJDBCProperties(props);
return DruidDataSourceFactory.createDataSource(props);
}
/**
* 本地数据库 mysql
*/
@Bean
public DataSource localDataSource() throws Exception {
Properties props = new Properties();
props.put("driverClassName", env.getProperty("jdbclocal.driverClassName"));
props.put("url", env.getProperty("jdbclocal.url"));
props.put("username", env.getProperty("jdbclocal.username"));
props.put("password", env.getProperty("jdbclocal.password"));
props.put("type", env.getProperty("jdbclocal.type"));
this.setCommonJDBCProperties(props);
return DruidDataSourceFactory.createDataSource(props);
}
/**
* @Primary 该注解表示在同一个接口有多个实现类可以注入的时候,默认选择哪一个,而不是让@autowire注解报错
* @Qualifier 根据名称进行注入,通常是在具有相同的多个类型的实例的一个注入(例如有多个DataSource类型的实例)
*/
@Bean
@Primary
public DynamicDataSource dataSource(@Qualifier("yxDataSource") DataSource yxDataSource,
@Qualifier("wxDataSource") DataSource wxDataSource,
@Qualifier("localDataSource") DataSource localDataSource
) {
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put(DatabaseType.wxDataSource, wxDataSource);
targetDataSources.put(DatabaseType.yxDataSource, yxDataSource);
targetDataSources.put(DatabaseType.localDataSource, localDataSource);
DynamicDataSource dataSource = new DynamicDataSource();
dataSource.setTargetDataSources(targetDataSources);// 该方法是AbstractRoutingDataSource的方法
dataSource.setDefaultTargetDataSource(localDataSource);// 默认的datasource设置为localDataSource
return dataSource;
}
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("yxDataSource") DataSource yxDataSource,
@Qualifier("wxDataSource") DataSource wxDataSource,
@Qualifier("localDataSource") DataSource localDataSource) throws Exception{
SqlSessionFactoryBean fb = new SqlSessionFactoryBean();
fb.setDataSource(this.dataSource(yxDataSource, wxDataSource,localDataSource));
fb.setTypeAliasesPackage(env.getProperty("mybatis.typeAliasesPackage"));
fb.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(env.getProperty("mybatis.mapperLocations")));
return fb.getObject();
}
/**
* 配置事务管理器
*/
@Bean
public DataSourceTransactionManager transactionManager(DynamicDataSource dataSource) throws Exception {
return new DataSourceTransactionManager(dataSource);
}
private void setCommonJDBCProperties(Properties props)
{
props.put("initialSize", env.getProperty("jdbc.initialSize"));
props.put("minIdle", env.getProperty("jdbc.minIdle"));
props.put("maxActive", env.getProperty("jdbc.maxActive"));
props.put("maxWait", env.getProperty("jdbc.maxWait"));
props.put("validationQuery", env.getProperty("jdbc.validationQuery"));
props.put("testOnBorrow", env.getProperty("jdbc.testOnBorrow"));
props.put("testOnReturn", env.getProperty("jdbc.testOnReturn"));
props.put("testWhileIdle", env.getProperty("jdbc.testWhileIdle"));
props.put("timeBetweenEvictionRunsMillis", env.getProperty("jdbc.timeBetweenEvictionRunsMillis"));
props.put("minEvictableIdleTimeMillis", env.getProperty("jdbc.minEvictableIdleTimeMillis"));
props.put("removeAbandoned", env.getProperty("jdbc.removeAbandoned"));
props.put("removeAbandonedTimeout", env.getProperty("jdbc.removeAbandonedTimeout"));
props.put("logAbandoned", env.getProperty("jdbc.logAbandoned"));
props.put("poolPreparedStatements", env.getProperty("jdbc.poolPreparedStatements"));
props.put("maxPoolPreparedStatementPerConnectionSize", env.getProperty("jdbc.maxPoolPreparedStatementPerConnectionSize"));
props.put("filters", env.getProperty("jdbc.filters"));
}
}
使用 server层:使用事物 注意注解@Transactional ,表示事物。
其次CreateBusinessRequest 继承 public class CreateBusinessRequest extends EcBaseData。通过EcBaseData中spaceName进入多数据源aop切换。由于aop方式存在事物和数据源的先后顺序,使用了order进行了先后顺序@Order(-10)//保证该AOP在@Transactional之前执行
@Transactional
public Map createBusiness(CreateBusinessRequest createBusinessRequest){
}