背景
最近在做一個(gè)數(shù)據(jù)隔離的需求蜓肆,相同的庫(kù)表結(jié)構(gòu)晦鞋,根據(jù)不同的用戶,數(shù)據(jù)落到不同的庫(kù)艘虎。第一想到的就是多數(shù)據(jù)源的方案唉侄。
以前做過(guò)類(lèi)似的多數(shù)據(jù)源的切換方案,是在項(xiàng)目啟動(dòng)的時(shí)候就知道數(shù)據(jù)源野建,提前加載數(shù)據(jù)源属划,現(xiàn)在是只有用戶過(guò)來(lái)的時(shí)候才去創(chuàng)建數(shù)據(jù)源,同時(shí)后期又增加了不同類(lèi)型的用戶贬墩,不需要修改代碼榴嗅,直接使用。
ps:在網(wǎng)上看了文章陶舞,有許多把多數(shù)據(jù)源切換跟動(dòng)態(tài)數(shù)據(jù)源加載混在一起嗽测,讓很多人迷茫,在這里說(shuō)下
多數(shù)據(jù)源:同一個(gè)項(xiàng)目,用到多個(gè)數(shù)據(jù)源唠粥,在項(xiàng)目啟動(dòng)的時(shí)候就已經(jīng)創(chuàng)建出來(lái)疏魏,比如:庫(kù)存數(shù)據(jù)庫(kù)、訂單數(shù)據(jù)晤愧。
動(dòng)態(tài)加載:項(xiàng)目啟動(dòng)的時(shí)候不知道應(yīng)該創(chuàng)建那個(gè)數(shù)據(jù)庫(kù)大莫,只有根據(jù)請(qǐng)求的用戶信息,動(dòng)態(tài)創(chuàng)建相應(yīng)的數(shù)據(jù)源官份。
技術(shù)實(shí)現(xiàn)
整體實(shí)現(xiàn)流程
動(dòng)態(tài)數(shù)據(jù)源實(shí)現(xiàn)
-
動(dòng)態(tài)數(shù)據(jù)源實(shí)現(xiàn)整體流程
技術(shù)實(shí)現(xiàn)
動(dòng)態(tài)數(shù)據(jù)源-AbstractRoutingDataSource
源碼
/*
* Copyright 2002-2017 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.jdbc.datasource.lookup;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.jdbc.datasource.AbstractDataSource;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;
/**
* Abstract {@link javax.sql.DataSource} implementation that routes {@link #getConnection()}
* calls to one of various target DataSources based on a lookup key. The latter is usually
* (but not necessarily) determined through some thread-bound transaction context.
*
* @author Juergen Hoeller
* @since 2.0.1
* @see #setTargetDataSources
* @see #setDefaultTargetDataSource
* @see #determineCurrentLookupKey()
*/
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
@Nullable
private Map<Object, Object> targetDataSources;
@Nullable
private Object defaultTargetDataSource;
private boolean lenientFallback = true;
private DataSourceLookup dataSourceLookup = new JndiDataSourceLookup();
@Nullable
private Map<Object, DataSource> resolvedDataSources;
@Nullable
private DataSource resolvedDefaultDataSource;
/**
* Specify the map of target DataSources, with the lookup key as key.
* The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>The key can be of arbitrary type; this class implements the
* generic lookup process only. The concrete key representation will
* be handled by {@link #resolveSpecifiedLookupKey(Object)} and
* {@link #determineCurrentLookupKey()}.
*/
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
}
/**
* Specify the default target DataSource, if any.
* <p>The mapped value can either be a corresponding {@link javax.sql.DataSource}
* instance or a data source name String (to be resolved via a
* {@link #setDataSourceLookup DataSourceLookup}).
* <p>This DataSource will be used as target if none of the keyed
* {@link #setTargetDataSources targetDataSources} match the
* {@link #determineCurrentLookupKey()} current lookup key.
*/
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
this.defaultTargetDataSource = defaultTargetDataSource;
}
/**
* Specify whether to apply a lenient fallback to the default DataSource
* if no specific DataSource could be found for the current lookup key.
* <p>Default is "true", accepting lookup keys without a corresponding entry
* in the target DataSource map - simply falling back to the default DataSource
* in that case.
* <p>Switch this flag to "false" if you would prefer the fallback to only apply
* if the lookup key was {@code null}. Lookup keys without a DataSource
* entry will then lead to an IllegalStateException.
* @see #setTargetDataSources
* @see #setDefaultTargetDataSource
* @see #determineCurrentLookupKey()
*/
public void setLenientFallback(boolean lenientFallback) {
this.lenientFallback = lenientFallback;
}
/**
* Set the DataSourceLookup implementation to use for resolving data source
* name Strings in the {@link #setTargetDataSources targetDataSources} map.
* <p>Default is a {@link JndiDataSourceLookup}, allowing the JNDI names
* of application server DataSources to be specified directly.
*/
public void setDataSourceLookup(@Nullable DataSourceLookup dataSourceLookup) {
this.dataSourceLookup = (dataSourceLookup != null ? dataSourceLookup : new JndiDataSourceLookup());
}
@Override
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
this.targetDataSources.forEach((key, value) -> {
Object lookupKey = resolveSpecifiedLookupKey(key);
DataSource dataSource = resolveSpecifiedDataSource(value);
this.resolvedDataSources.put(lookupKey, dataSource);
});
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
/**
* Resolve the given lookup key object, as specified in the
* {@link #setTargetDataSources targetDataSources} map, into
* the actual lookup key to be used for matching with the
* {@link #determineCurrentLookupKey() current lookup key}.
* <p>The default implementation simply returns the given key as-is.
* @param lookupKey the lookup key object as specified by the user
* @return the lookup key as needed for matching
*/
protected Object resolveSpecifiedLookupKey(Object lookupKey) {
return lookupKey;
}
/**
* Resolve the specified data source object into a DataSource instance.
* <p>The default implementation handles DataSource instances and data source
* names (to be resolved via a {@link #setDataSourceLookup DataSourceLookup}).
* @param dataSource the data source value object as specified in the
* {@link #setTargetDataSources targetDataSources} map
* @return the resolved DataSource (never {@code null})
* @throws IllegalArgumentException in case of an unsupported value type
*/
protected DataSource resolveSpecifiedDataSource(Object dataSource) throws IllegalArgumentException {
if (dataSource instanceof DataSource) {
return (DataSource) dataSource;
}
else if (dataSource instanceof String) {
return this.dataSourceLookup.getDataSource((String) dataSource);
}
else {
throw new IllegalArgumentException(
"Illegal data source value - only [javax.sql.DataSource] and String supported: " + dataSource);
}
}
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
@Override
public Connection getConnection(String username, String password) throws SQLException {
return determineTargetDataSource().getConnection(username, password);
}
@Override
@SuppressWarnings("unchecked")
public <T> T unwrap(Class<T> iface) throws SQLException {
if (iface.isInstance(this)) {
return (T) this;
}
return determineTargetDataSource().unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return (iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface));
}
/**
* Retrieve the current target DataSource. Determines the
* {@link #determineCurrentLookupKey() current lookup key}, performs
* a lookup in the {@link #setTargetDataSources targetDataSources} map,
* falls back to the specified
* {@link #setDefaultTargetDataSource default target DataSource} if necessary.
* @see #determineCurrentLookupKey()
*/
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
##
/**
* Determine the current lookup key. This will typically be
* implemented to check a thread-bound transaction context.
* <p>Allows for arbitrary keys. The returned key needs
* to match the stored lookup key type, as resolved by the
* {@link #resolveSpecifiedLookupKey} method.
*/
@Nullable
protected abstract Object determineCurrentLookupKey();
}
從源碼可以看出主要涉及到的變量
Map<Object, Object> targetDataSources;//外部創(chuàng)建的數(shù)據(jù)源都放在這個(gè)集合下
Object defaultTargetDataSource;//指定的默認(rèn)數(shù)據(jù)源
Map<Object, DataSource> resolvedDataSources;//內(nèi)部使用的數(shù)據(jù)源 跟目標(biāo)數(shù)據(jù)源對(duì)應(yīng)
DataSource resolvedDefaultDataSource;//跟默認(rèn)數(shù)據(jù)源對(duì)應(yīng)
他們直接的關(guān)系是:targetDataSources 是外部調(diào)用只厘,resolvedDataSources是內(nèi)部使用,當(dāng)選加載數(shù)據(jù)源的時(shí)候舅巷,targetDataSources數(shù)據(jù)源集合賦值給resolvedDataSources
變量使用的地方
public void afterPropertiesSet() {
if (this.targetDataSources == null) {
throw new IllegalArgumentException("Property 'targetDataSources' is required");
}
//數(shù)據(jù)源集合賦值 默認(rèn)數(shù)據(jù)源默認(rèn)
this.resolvedDataSources = new HashMap<>(this.targetDataSources.size());
this.targetDataSources.forEach((key, value) -> {
Object lookupKey = resolveSpecifiedLookupKey(key);
DataSource dataSource = resolveSpecifiedDataSource(value);
this.resolvedDataSources.put(lookupKey, dataSource);
});
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
在多數(shù)據(jù)源切換中羔味,抽象類(lèi)AbstractRoutingDataSource里面的抽象方法determineCurrentLookupKey必須實(shí)現(xiàn),切換主要是根據(jù)這個(gè)方法進(jìn)行切換钠右,代碼如下
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
//調(diào)用此方法拿到赋元,切換數(shù)據(jù)源的標(biāo)記,進(jìn)行切換
Object lookupKey = determineCurrentLookupKey();
DataSource dataSource = this.resolvedDataSources.get(lookupKey);
//這里說(shuō)明下飒房,默認(rèn)數(shù)據(jù)源必須要設(shè)置搁凸,當(dāng)數(shù)據(jù)源集合獲取不到數(shù)據(jù)源的時(shí)候,默認(rèn)使用默認(rèn)數(shù)據(jù)源進(jìn)行兜底resolvedDefaultDataSource
if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
dataSource = this.resolvedDefaultDataSource;
}
if (dataSource == null) {
throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
}
return dataSource;
}
/**
* Determine the current lookup key. This will typically be
* implemented to check a thread-bound transaction context.
* <p>Allows for arbitrary keys. The returned key needs
* to match the stored lookup key type, as resolved by the
* {@link #resolveSpecifiedLookupKey} method.
*/
@Nullable
protected abstract Object determineCurrentLookupKey();
上面分析了下源碼狠毯,到此 我們大概知道怎么去實(shí)現(xiàn)動(dòng)態(tài)數(shù)據(jù)源的加載以及切換
具體實(shí)現(xiàn)
- 創(chuàng)建一個(gè)全部線程變量护糖,控制數(shù)據(jù)源的切換 DatabaseContextHolder
public class DatabaseContextHolder {
//線程變量,每一個(gè)線程一個(gè)值垃你,相互隔離
private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
public static void setDatabaseType(String databaseType){
contextHolder.set(databaseType);
}
public static String getDataBaseType(){
return contextHolder.get();
}
public static void clearDbKey(){
contextHolder.remove();
}
}
- 繼承AbstractRoutingDataSource創(chuàng)建動(dòng)態(tài)數(shù)據(jù)源椅文,DynamicDataSource
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSource.class);
private static DynamicDataSource instance;
private static byte[] lock=new byte[0];
//數(shù)據(jù)源集合,
private static Map<Object,Object> dataSourceMap=new HashMap<Object, Object>();
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
dataSourceMap.putAll(targetDataSources);
super.afterPropertiesSet();// 必須添加該句惜颇,否則新添加數(shù)據(jù)源無(wú)法識(shí)別到
}
public Map<Object, Object> getDataSourceMap() {
return dataSourceMap;
}
public static synchronized DynamicDataSource getInstance(){
if(instance==null){
synchronized (lock){
if(instance==null){
instance=new DynamicDataSource();
}
}
}
return instance;
}
@Override
protected Object determineCurrentLookupKey() {
logger.info("當(dāng)前使用的數(shù)據(jù)源:{}",DatabaseContextHolder.getDataBaseType());
return DatabaseContextHolder.getDataBaseType();
}
}
此類(lèi)解釋下,單例模式少辣、dataSourceMap凌摄,主要是為了在數(shù)據(jù)源初始化以后,后續(xù)有的數(shù)據(jù)源進(jìn)來(lái)漓帅,能動(dòng)態(tài)加載锨亏,不至于重新應(yīng)用, afterPropertiesSet()這個(gè)方法必須調(diào)用,否則新增加的數(shù)據(jù)源不會(huì)生效忙干,具體看上面的代碼
以上兩步器予,動(dòng)態(tài)數(shù)據(jù)源就創(chuàng)建好了,可以替代DataSource了捐迫,接下來(lái)乾翔,應(yīng)該怎么使用呢?
- 創(chuàng)建數(shù)據(jù)源,DataSourceConfig
@Configuration
public class DataSourceConfig {
@Value("${spring.datasource.master.url}")
private String masterDBUrl;
@Value("${spring.datasource.master.username}")
private String masterDBUser;
@Value("${spring.datasource.master.password}")
private String masterDBPassword;
@Value("${spring.datasource.master.driver-class-name}")
private String masterDBDreiverName;
@Bean
public DynamicDataSource dynamicDataSource() {
DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
DruidDataSource oneDataSouce = new DruidDataSource();
oneDataSouce.setDriverClassName("com.mysql.jdbc.Driver");
oneDataSouce.setUrl("jdbc:mysql://127.0.0.1:3306/kb_master?serverTimezone=Hongkong");
oneDataSouce.setUsername("");
oneDataSouce.setPassword("");
DruidDataSource twoDataSource = new DruidDataSource();
twoDataSource.setDriverClassName("com.mysql.jdbc.Driver");
twoDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/kb_master?serverTimezone=Hongkong");
twoDataSource.setUsername("");
twoDataSource.setPassword("");
Map<Object,Object> map = new HashMap<>();
map.put("oneDataSouce", oneDataSouce);
map.put("twoDataSource", twoDataSource);
//添加數(shù)據(jù)源結(jié)合
dynamicDataSource.setTargetDataSources(map);
//必須設(shè)置一個(gè)默認(rèn)數(shù)據(jù)源兜底
dynamicDataSource.setDefaultTargetDataSource(oneDataSouce);
return dynamicDataSource;
}
@Bean
public SqlSessionFactory sqlSessionFactory(
@Qualifier("dynamicDataSource") DataSource dynamicDataSource)
throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dynamicDataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver()
.getResources("classpath*:mappers/*.xml"));
return bean.getObject();
}
@Bean(name = "sqlSessionTemplate")
public SqlSessionTemplate sqlSessionTemplate(
@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory)
throws Exception {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
//跟mybatis映射mapper的時(shí)候反浓,特別注意萌丈,一定要具體到mapper的包下,不然會(huì)導(dǎo)致雷则,在bean會(huì)被加載兩次辆雾,例如:我就遇到service 被加載了兩次具體大家可以看下MapperScannerConfigurer
mapperScannerConfigurer.setBasePackage("com.cn.kbtest");
return mapperScannerConfigurer;
}
}
我用的是springboot,如果是其他這配置月劈,寫(xiě)在xml里面就行度迂,具體可以自行百度
上面動(dòng)態(tài)數(shù)據(jù)源基本上就寫(xiě)完了,下面看怎么使用
- 通過(guò)AOP的切面進(jìn)行攔截猜揪,可以對(duì)包路徑進(jìn)行攔截英岭,也有對(duì)指定的注解進(jìn)行攔截,然后進(jìn)行相應(yīng)的數(shù)據(jù)庫(kù)操作
@Aspect
@Order
@Component
public class DataSourceViewAspect {
private static final Logger logger = LoggerFactory.getLogger(DataSourceViewAspect.class);
@Pointcut("@within(secondDataSourceMapper)")
public void pointCut(SecondDataSourceMapper secondDataSourceMapper){}
@Before("pointCut(secondDataSourceMapper)")
public void doBefore(JoinPoint point, SecondDataSourceMapper secondDataSourceMapper){
DatabaseContextHolder.setDatabaseType("view");
logger.info("數(shù)據(jù)源切換為:{}","view");
}
@After("pointCut(secondDataSourceMapper)")
public void after(SecondDataSourceMapper secondDataSourceMapper){
logger.info("清除數(shù)據(jù)源標(biāo)記:{}" ,"view");
DatabaseContextHolder.clearDbKey();
}
}
上面是通過(guò)攔截這個(gè)注解湿右,類(lèi)下所有的方法诅妹,進(jìn)行切換,也可以攔截package毅人,具體可以查詢(xún)aop吭狡,里面有個(gè)注意點(diǎn),一定要@before丈莺,之前就進(jìn)行切換划煮,不然出錯(cuò)
這樣就做到了動(dòng)態(tài)數(shù)據(jù)源的切換,這是對(duì)項(xiàng)目啟動(dòng)的時(shí)候缔俄,對(duì)存在的數(shù)據(jù)源加載進(jìn)行切換弛秋,如果后續(xù)新增了一個(gè)數(shù)據(jù)源,怎么辦俐载?
- 通過(guò)攔截器蟹略,對(duì)外部請(qǐng)求進(jìn)行攔截,可以從header遏佣、cookie等里面獲取數(shù)據(jù)源的標(biāo)記挖炬,進(jìn)行數(shù)據(jù)源的創(chuàng)建,然后加載
@Component
public class MyControllerAdvice implements HandlerInterceptor {
@Value("${datasource.global.use-encrypted-password}")
private boolean useEncryptedPassword;
@Value("${datasource.dynamic.url}")
private String url;
@Override
public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
throws Exception {
HospitalContextHolder.remove();
}
@Value("${datasource.dynamic.username}")
private String userName;
@Value("${datasource.dynamic.password}")
private String passWord;
@Value("${datasource.dynamic.driver-class-name}")
private String driverClassName;
@Value("${hospital.db.isolation}")
private boolean isolation;
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
throws IOException {
if (isolation){
String hospitalId = request.getHeader("hospitalId");
if(StringUtils.isNotBlank(hospitalId)){
String dataType="wit120_"+request.getHeader("hospitalId");
DynamicDataSource dynamicDataSource = DynamicDataSource.getInstance();
Map<Object,Object> dataSourceMap = dynamicDataSource.getDataSourceMap();
if (dataSourceMap.get(dataType)==null){
DruidDataSource datasource = new DruidDataSource();
String dbUrl = this.url.replace("wit120",dataType);
datasource.setUrl(dbUrl);
datasource.setUsername(this.userName);
datasource.setPassword(this.passWord);
datasource.setDriverClassName(this.driverClassName);
datasource.setInitialSize(5);
datasource.setMinIdle(5);
datasource.setMaxActive(20);
datasource.setMaxWait(60000);
datasource.setTimeBetweenEvictionRunsMillis(60000);
datasource.setMinEvictableIdleTimeMillis(300000);
datasource.setValidationQuery("SELECT 1 FROM DUAL ");
datasource.setTestWhileIdle(true);
datasource.setTestOnBorrow(false);
datasource.setTestOnReturn(false);
datasource.setPoolPreparedStatements(true);
datasource.setMaxPoolPreparedStatementPerConnectionSize(20);
try {
datasource.setFilters("config,stat,wall,log4j");
datasource.setConnectionProperties("allowMultiQueries=true;druid.stat.mergeSql=true;druid.stat.slowSqlMillis=3000;config.decrypt="+useEncryptedPassword);
dynamicDataSource.setTargetDataSources(dataSourceMap);
datasource.getConnection();
//加載數(shù)據(jù)源
dataSourceMap.put(dataType,datasource);
} catch (Exception e) {
datasource.close();
refuse(response);
return false;
}
}
//存儲(chǔ)醫(yī)院標(biāo)記
HospitalContextHolder.setHospitalId(hospitalId);
}
}
return true;
}
/**
* 拒絕的響應(yīng)處理
*
* @param response
* @throws IOException
*/
private void refuse(HttpServletResponse response) throws IOException {
response.setContentType("application/json;charset=UTF-8");
response.getOutputStream().write(ResultUtil.resultFailed(BaseBizError.HOSPTAIL_Id_NOT_EXIST).toJsonString().getBytes("UTF-8"));
}
}
基本上 就實(shí)現(xiàn)了状婶,動(dòng)態(tài)數(shù)據(jù)源的加載意敛,切換