mysql雙資料源動态切換_Spring boot+Mybatis多資料源動态切換

通過重寫AbstractRoutingDataSource類中方法determineTargetDataSource，determineCurrentLookupKey，afterPropertiesSet實作動态DataSource。

determineTargetDataSource：擷取動态DataSource

determineCurrentLookupKey：擷取動态資料源名稱

afterPropertiesSet：重寫該方法，防止調用父類方法，自己管理多資料源

本例适用場景：多租戶SAAS系統，基于資料庫schema或者資料庫執行個體完全隔離的系統。系統中有一個master資料庫，會配置其他各個租戶資料源的連接配接資訊，這個是由系統配置統一維護。任何request請求都會标記該租戶資訊，然後由背景攔截初始化ThreadLocal變量。DynamicDataSource中根據ThreadLocal切換不同的資料源。

資料源标記ThreadLocal

public class DynamicDataSourceHolder {

private static final ThreadLocal contextHolder = new InheritableThreadLocal<>();

public static void setDataSource(String dataSource) {

contextHolder.set(dataSource);

}

public static String getDataSource() {

return contextHolder.get();

}

public static void clearDataSource() {

contextHolder.remove();

}

}

@Component

public class SpringBeanHelper implements ApplicationContextAware {

private static ApplicationContext context;

public static T getBean(Class requiredClass) {

return context.getBean(requiredClass);

}

@Override

public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {

this.context= applicationContext;

}

}

攔截請求設定ThreadLocal

@Configuration

public class DBInterceptor implements HandlerInterceptor{

private static final Logger logger= LoggerFactory.getLogger(DBInterceptor.class);

@Override

public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object arg2) throws Exception {

//這裡隻是簡單從請求參數中擷取，真實場景可能會從header中租戶id或者域名區分

String db= request.getParameter("db");

if(StringUtils.isNoneEmpty(db)) {

logger.info("db is: {}",db);

DynamicDataSourceHolder.setDataSource(db);

return true;

}

logger.error("db is empty");

response.getWriter().print("no authority.");

return false;

}

@Override

public void afterCompletion(HttpServletRequest arg0, HttpServletResponse arg1, Object arg2, Exception arg3)

throws Exception {

DynamicDataSourceHolder.clearDataSource();

logger.info("clear db holder.");

}

@Override

public void postHandle(HttpServletRequest arg0, HttpServletResponse response, Object arg2, ModelAndView arg3)

throws Exception {}

}

動态資料源實作

@Configuration

public class DynamicDataSource extends AbstractRoutingDataSource{

private static final Logger logger= LoggerFactory.getLogger(DynamicDataSource.class);

@Value("${jdbc.master.database}")

private String masterDataBase;

@Value("${jdbc.driver}")

private String driver;

@Value("${jdbc.url}")

private String url;

@Value("${jdbc.username}")

private String username;

@Value("${jdbc.password}")

private String password;

//儲存動态建立的資料源

private static final Map targetDataSource = new HashMap<>();

//主資料庫初始化

@PostConstruct

private void init() {

DataSource dataSource= (DataSource) DataSourceBuilder.create(Thread.currentThread().getContextClassLoader())

.driverClassName(driver)

.url(url)

.username(username)

.password(password).build();

dataSource.setMaxActive(1000);

dataSource.setMaxIdle(200);

dataSource.setMinIdle(200);

dataSource.setMaxWait(10000);

putDataSource(masterDataBase, dataSource);

}

@Override

protected DataSource determineTargetDataSource() {

// 根據資料庫選擇方案，拿到要通路的資料庫

String dataSourceName = determineCurrentLookupKey();

// 根據資料庫名字，從已建立的資料庫中擷取要通路的資料庫

DataSource dataSource = targetDataSource.get(dataSourceName);

if(null == dataSource) {

//從已建立的資料庫中擷取要通路的資料庫，如果沒有則建立一個

dataSource = this.selectDataSource(dataSourceName);

}

return dataSource;

}

@Override

protected String determineCurrentLookupKey() {

String dataSourceName = DynamicDataSourceHolder.getDataSource();

return dataSourceName;

}

private synchronized DataSource selectDataSource(String dbname) {

// 雙重檢查

DataSource obj = this.targetDataSource.get(dbname);

if (null != obj) {

return obj;

}

// 為空則建立資料庫

DataSource dataSource = this.setDataSource(dbname);

if (null != dataSource) {

// 将新建立的資料庫儲存到map中

putDataSource(dbname, dataSource);

return dataSource;

}

throw new RuntimeException("建立資料源失敗！");

}

private void putDataSource(String dbname, DataSource dataSource) {

this.targetDataSource.put(dbname, dataSource);

}

private DataSource setDataSource(String dbname) {

String oriSource = DynamicDataSourceHolder.getDataSource();

// 先切換回主庫

DynamicDataSourceHolder.setDataSource(masterDataBase);

// 查詢所需資訊

CenterDatabase database = getDataBaseService().getById(dbname);

if(database==null){

throw new RuntimeException("擷取目标資料庫連接配接資訊失敗。");

}

// 切換回目标庫

DynamicDataSourceHolder.setDataSource(oriSource);

DataSource dataSource = (DataSource)DataSourceBuilder.create(Thread.currentThread().getContextClassLoader())

.driverClassName(database.driver)

.url(database.parseMysqlURL())

.username(database.username)

.password(database.password).build();

dataSource.setMaxActive(3000);

dataSource.setMaxIdle(6);

dataSource.setMaxWait(5000);

return dataSource;

}

@Override

public void afterPropertiesSet() {

//do nothing just for override. becauseof targetDataSource management by self.

}

private DatabaseService getDataBaseService(){

return SpringBeanHelper.getBean(DatabaseService.class);

}

}

Mybatis配置

@Configuration

public class MybatisConfig implements TransactionManagementConfigurer {

//mybatis 配置路徑

private static String MYBATIS_CONFIG = "mybatis-config.xml";

//mybatis mapper resource 路徑

private static String MAPPER_PATH = "/mapper/**.xml";

private String typeAliasPackage = "com.test.mapper";

@Autowired

private DynamicDataSource dataSource;

@Override

public PlatformTransactionManager annotationDrivenTransactionManager() {

return new DataSourceTransactionManager(dataSource);

}

@Bean(name = "sqlSessionFactory")

public SqlSessionFactory sqlSessionFactoryBean() throws Exception {

SqlSessionFactoryBean bean = new SqlSessionFactoryBean();

bean.setConfigLocation(new ClassPathResource(MYBATIS_CONFIG));

//添加mapper 掃描路徑

PathMatchingResourcePatternResolver pathMatchingResourcePatternResolver = new PathMatchingResourcePatternResolver();

String packageSearchPath = ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + MAPPER_PATH;

bean.setMapperLocations(pathMatchingResourcePatternResolver.getResources(packageSearchPath));

//設定datasource

bean.setDataSource(dataSource);

//設定typeAlias 包掃描路徑

bean.setTypeAliasesPackage(typeAliasPackage);

return bean.getObject();

}

@Bean

public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {

return new SqlSessionTemplate(sqlSessionFactory);

}

}

說明：以上方案在使用多執行個體資料庫的時候問題不大。但如果考慮使用單執行個體資料庫，多schema的方案需要注意的各個租戶資料庫連接配接問題，防止有的租戶占用大量連接配接，浪費資源。