使用场景
假设需要做一个工具,由于保密需要只描述场景。 A下面有 B、C、D三家或者更多分公司,人员信息统一在总公司的A数据库,分公司各自使用各自的业务数据库,用户登陆访问A数据库校验成功后将用户所属分公司的KEY保存session中(此文章以存放session为例,其他存放方式参考即可),当然本文重点在与还可以动态增加EFG。。。。分公司,无需重启应用。
设计思路
因为系统登录离不开A数据库,我成A为主数据库。
B、C、D、E…并非系统运行必须数据库,所以简称自定义数据库。
B、C、D都为分公司,数据库结构完全一致,支持BCD与A库表结构不一致。案例以SpringBoot项目为例,Spring按照这个思路,更简单。
新建项目依赖的主数据库A 配置主数据源,以及公共数据源属性,application.properties
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.url=
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.username=root
spring.datasource.druid.password=root
spring.datasource.druid.maxWait=5000
spring.datasource.druid.。。。。。。
配置自定义数据源jdbc.properties(也可以不在配置文件中配置,项目有提供页面配置,保存数据库或者配置文件)
*数据源的key–可以理解为分公司代码
spring.datasource.keys=dev,stg,prd
spring.datasource.names=开发,测试,生产
*各数据源前缀为key
dev.spring.datasource.druid.driver-class-name=
dev.spring.datasource.druid.url=
dev.spring.datasource.druid.username=
dev.spring.datasource.druid.password=
stg.spring.datasource.druid.driver-class-name=
stg.spring.datasource.druid.url=
stg.spring.datasource.druid.username=
stg.spring.datasource.druid.password=
支持动态切换数据源的思路
包装一个DataSource 从当前线程中拿到需要访问的key,根据key得到需要使用哪个数据源,参考目前spring已经提供的AbstractRoutingDataSource,因此只需要自己写个类继承AbstractRoutingDataSource实现determineCurrentLookupKey()方法,这个方法就是获取当前线程中的key。如下:
public class DynamicDataSource extends AbstractRoutingDataSource{
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
/**
* 动态更新自定义数据源
* @param defaultDataSource
* @param customDataSources
*/
public void updateTargetDataSource(Map<String,DataSource> customDataSources){
Map<Object,Object> customDS=new HashMap<Object, Object>();
customDS.putAll(customDataSources);
setTargetDataSources(customDS);
afterPropertiesSet();
}
}
由于要支持动态添加数据源所以又添加updateTargetDataSource()方法。
现在配置主和自定义DataSource,SpringBoot方式
/**
* @ClassName DynamicDataSourceRegister
* @Description 动态数据源注册
*/
public class DynamicDataSourceRegister implements EnvironmentAware {
/**
* 加载多数据源配置
*/
@Override
public void setEnvironment(Environment env) {
DynamicDataSourceRegisterUtil.initAndRegisterDataSource(env);
}
}
DynamicDataSourceRegisterUtil中的方法
public static void initAndRegisterDataSource(Environment env){
//将自定义的数据源的信息加载到spring 的Environment中
try {
initCustomEnvironment(env);
} catch (IOException e) {
logger.error("初始化自定义数据库配置文件出错",e);
}
//产生主数据源,默认数据源配置在application.properties中的数据源
initDefaultDataSource(env);
//产生主自定义的数据源,默认数据源配置在jdbc.properties中的数据源
initCustomDataSources(env);
//将主数据员,自定义数据源注入到动态数据源DynamicDataSource ,
//再将DynamicDataSource 以名称“dataSource”注入到spring中
registerDataSource(DATA_SOURCE);
}
DynamicDataSourceRegisterUtil具体代码如下(各个方法作用见注释):
/**
* @ClassName DynamicDataSourceRegister
* @Description 动态数据源注册
*/
public class DynamicDataSourceRegisterUtil{
public final static String DATASOURCE_PARAM_PREF ="spring.datasource.";
public final static String DATASOURCE_PARAM_PRPO_KEY_DRIVER_CLASS_NAME ="driver-class-name";
public final static String DRUID_DATASOURCE_PARAM_PREF ="spring.datasource.druid.";
public final static String DATASOURCE_PARAM_TYPE ="druid.";
public final static String DATASOURCE_PARAM_SPLIT =".";
public final static String DEFAULT_TARGET_DATA_SOURCE ="defaultTargetDataSource";
public final static String TARGET_DATA_SOURCES ="targetDataSources";
public final static String DATA_SOURCE ="dataSource";
private static final Logger logger = LoggerFactory.getLogger(DynamicDataSourceRegisterUtil.class);
private static final ConversionService conversionService = new DefaultConversionService();
private static final String JDBC_FILE_NAME="jdbc.properties";
private static final String CUSTOM_DATA_SOURCES_MAP_NAME="customDataSourcesMap";
private static PropertyValues dataSourcePropertyValues;
// 数据源
private static DataSource defaultDataSource;
private static Map<String,DataSource> customDataSources = new HashMap<String,DataSource>();
/**
* 创建DataSource
*
* @param type
* @param driverClassName
* @param url
* @param username
* @param password
* @return
*/
private static DataSource buildDataSource(Map<String,Object> dsMap) {
DataSource dataSource=null;
try {
dataSource=DruidDataSourceFactory.createDataSource(dsMap);
if(dataSource instanceof DruidDataSource){
//注意:这一设置是为解决Druid 在获取连接时由于连接配置出错会一直等待获取连接,比较重要
((DruidDataSource) dataSource) .setBreakAfterAcquireFailure(true);
//((DruidDataSource) dataSource).init();
}
return dataSource;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void initAndRegisterDataSource(Environment env){
//将自定义的数据源的信息加载到spring 的Environment中
try {
initCustomEnvironment(env);
} catch (IOException e) {
logger.error("初始化自定义数据库配置文件出错",e);
}
//产生主数据源,默认数据源配置在application.properties中的数据源
initDefaultDataSource(env);
//产生主自定义的数据源,默认数据源配置在jdbc.properties中的数据源
initCustomDataSources(env);
//将主数据员,自定义数据源注入到动态数据源DynamicDataSource ,再将DynamicDataSource 以名称“dataSource”注入到spring中
registerDataSource(DATA_SOURCE);
}
/**
* 更新配置文件,并热加载到Environment中
* @param properties
* @throws IOException
*/
public synchronized static void refreshDataSoureProperties(Properties properties) throws IOException {
//将属性持久化到配置文件
OutputStream out=new FileOutputStream(URLEncoder.encode(DynamicDataSourceRegisterUtil.class.getClassLoader().getResource(JDBC_FILE_NAME).getPath(),"utf-8"));
properties.store(out,"更新数据库");
out.flush();
out.close();
//将属性热加载到环境中去
Environment env =EnvironmentUtils.getEnvironment();
PropertySource<?> source = new PropertiesPropertySource(CUSTOM_DATA_SOURCES_MAP_NAME, properties);
((StandardEnvironment) env).getPropertySources().addLast(source);
refreshDataSource(env);
}
/**
* 初始化主数据源
*/
private static void initDefaultDataSource(Environment env) {
// 读取主数据源
RelaxedPropertyResolver propertyResolver = new RelaxedPropertyResolver(env, DATASOURCE_PARAM_PREF);
Map<String,Object> dsMap = propertyResolver.getSubProperties(DATASOURCE_PARAM_TYPE);
defaultDataSource = buildDataSource(dsMap);
dataBinder(defaultDataSource,env);
}
/**
* 关闭老的数据源
*/
private static void closeOldCustomDataSources(){
if(customDataSources!=null&&customDataSources.size()>0){
for (String key:customDataSources.keySet()){
DataSource dataSource =customDataSources.get(key);
if(dataSource instanceof DruidDataSource){
((DruidDataSource)dataSource).close();
logger.info("closed datasource "+key);
}
}
}
if(customDataSources!=null){
customDataSources.clear();
}
}
public synchronized static void initCustomEnvironment(Environment env) throws IOException {
Properties properties = new Properties();
InputStream in =null;
try {
in = new FileInputStream(URLEncoder.encode(DynamicDataSourceRegisterUtil.class.getClassLoader().getResource(JDBC_FILE_NAME).getPath(), "utf-8"));
properties.load(in);
PropertySource<?> source = new PropertiesPropertySource(CUSTOM_DATA_SOURCES_MAP_NAME, properties);
((StandardEnvironment) env).getPropertySources().addLast(source);
}finally {
in.close();
}
}
/**
* 初始化更多数据源
*
*/
private static void initCustomDataSources(Environment env) {
//初始化之前要先将老的数据源关闭
closeOldCustomDataSources();
// 读取配置文件获取更多数据源,也可以通过defaultDataSource读取数据库获取更多数据源
RelaxedPropertyResolver propertyResolver = new RelaxedPropertyResolver(env, DATASOURCE_PARAM_PREF);
String dsPrefixs = propertyResolver.getProperty("keys");
if(dsPrefixs==null){
return;
}
for (String dsPrefix : dsPrefixs.split(",")) {// 多个数据源
RelaxedPropertyResolver propertys = new RelaxedPropertyResolver(env, dsPrefix.trim()+DATASOURCE_PARAM_SPLIT+DATASOURCE_PARAM_PREF);
Map<String,Object> dsMap = propertys.getSubProperties(DATASOURCE_PARAM_TYPE);
DataSource dataSource= buildDataSource(dsMap);
dataBinder(dataSource,env);
customDataSources.put(dsPrefix, dataSource);
try{
//向库中插入信息
String password = (String) dsMap.get(DruidDataSourceFactory.PROP_PASSWORD);
String url = (String) dsMap.get(DruidDataSourceFactory.PROP_URL);
String username = (String) dsMap.get(DruidDataSourceFactory.PROP_USERNAME);
String strs[]=url.split(";");
String dbName="";
for(String s: strs){
if(s!=null&&s.trim()!=null){
if(s.toUpperCase().contains("DATABASENAME")){
dbName=s.split("=")[1];
}
}
}
//Data Source=192.168.5.92;Initial Catalog=MS_CUBE_TEST;User ID=sa;Password=root;
String connectionStr="Data Source="+strs[0].replaceAll(".*(\\d{3}(\\.\\d{1,3}){3}).*","$1")
+";Initial Catalog="+dbName+";User ID="+username+";Password="+password+";";
updateConnectionStr2Db(dsMap,connectionStr);
} catch (Exception e) {
logger.error("将数据源配置信息写入到数据库失败!",e);
}
}
}
private static void updateConnectionStr2Db(Map<String, Object> dataSource, String connectionStr) throws SQLException {
Connection connection=null;
PreparedStatement preparedStatement=null;
try {
Class.forName((String)dataSource.get(DATASOURCE_PARAM_PRPO_KEY_DRIVER_CLASS_NAME));
connection =DriverManager.getConnection((String)dataSource.get(DruidDataSourceFactory.PROP_URL),
(String)dataSource.get(DruidDataSourceFactory.PROP_USERNAME),
(String)dataSource.get(DruidDataSourceFactory.PROP_PASSWORD));
preparedStatement=connection.prepareStatement("merge into ssas_base_config_prop p using(\n" +
"\tselect 'clrSSASConfigurationConnectionString' as properties_key\n" +
") t on (t.properties_key=p.properties_key)\n" +
"when matched then\n" +
"update set properties_value=?\n" +
"when not matched then\n" +
"insert (properties_key,properties_value,properties_name,display_type)values('clrSSASConfigurationConnectionString',?,'当前环境数据库','text');\n");
preparedStatement.setString(1,connectionStr);
preparedStatement.setString(2,connectionStr);
preparedStatement.executeUpdate();
connection.commit();
}catch (Exception e){
logger.error("保存数据库连接信息失败",e);
}finally {
preparedStatement.close();
connection.close();
}
}
/**
* 更新配置之后要更新DynamicDataSource
* @param dataSourceName
*/
private static void refreshDataSource(Environment environment) {
initCustomDataSources(environment);
DynamicDataSource dynamicDataSource =ApplicationContextUtil.getBean(DATA_SOURCE);
dynamicDataSource.updateTargetDataSource(defaultDataSource,customDataSources);
DynamicDataSourceContextHolder.dataSourceIds.clear();
DynamicDataSourceContextHolder.dataSourceIds.addAll(customDataSources.keySet());
}
/**
* 将动态数据源注册到spring中
* @param dataSourceName
*/
private static void registerDataSource(String dataSourceName) {
Map<String,Object> targetDataSources = new HashMap<String,Object>();
// 将主数据源添加到更多数据源中
targetDataSources.put(DEFAULT_TARGET_DATA_SOURCE, defaultDataSource);
// 添加更多数据源
targetDataSources.putAll(customDataSources);
DynamicDataSourceContextHolder.dataSourceIds.addAll(customDataSources.keySet());
Map<String,Object> paramValues=new HashMap<String, Object>();
paramValues.put(DEFAULT_TARGET_DATA_SOURCE, defaultDataSource);
paramValues.put(TARGET_DATA_SOURCES, targetDataSources);
ApplicationContextUtil.registerSingletonBean(dataSourceName,DynamicDataSource.class,paramValues);
logger.info("Dynamic DataSource Registry");
}
/**
* 为DataSource绑定更多数据
* @param dataSource
* @param env
*/
private static void dataBinder(DataSource dataSource, Environment env) {
RelaxedDataBinder dataBinder = new RelaxedDataBinder(dataSource);
dataBinder.setConversionService(conversionService);
dataBinder.setIgnoreNestedProperties(false);//false
dataBinder.setIgnoreInvalidFields(false);//false
dataBinder.setIgnoreUnknownFields(true);//true
if (dataSourcePropertyValues == null) {
Map<String, Object> rpr = new RelaxedPropertyResolver(env, DATASOURCE_PARAM_PREF).getSubProperties(DATASOURCE_PARAM_TYPE);
Map<String, Object> values = new HashMap<>(rpr);
// 排除已经设置的属性
values.remove("type");
values.remove(DATASOURCE_PARAM_PRPO_KEY_DRIVER_CLASS_NAME);
values.remove("url");
values.remove("username");
values.remove("password");
dataSourcePropertyValues = new MutablePropertyValues(values);
}
dataBinder.bind(dataSourcePropertyValues);
}
}
相关工具类ApplicationContextUtil、EnvironmentUtil
@Component
public class ApplicationContextUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
ApplicationContextUtil.applicationContext = applicationContext;
}
/**
* 取得存储在静态变量中的ApplicationContext.
*/
public static ApplicationContext getApplicationContext() {
checkApplicationContext();
return applicationContext;
}
/**
* 从静态变量ApplicationContext中取得Bean, 自动转型为所赋值对象的类型.
*/
public static <T> T getBean(String name) {
checkApplicationContext();
if (applicationContext.containsBean(name)) {
return (T) applicationContext.getBean(name);
}
return null;
}
/**
* 从静态变量ApplicationContext中取得Bean, 自动转型为所赋值对象的类型.
*/
public static <T> T getBean(Class<T> clazz) {
checkApplicationContext();
return (T) applicationContext.getBeansOfType(clazz);
}
private static void checkApplicationContext() {
if (applicationContext == null)
throw new IllegalStateException("applicaitonContext未注入,请在applicationContext.xml中定义SpringContextUtil");
}
public synchronized static void registerSingletonBean(String beanName,Class clzz,Map<String,Object> original) {
checkApplicationContext();
DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) ApplicationContextUtil.getApplicationContext().getAutowireCapableBeanFactory();
if(beanFactory.containsBean(beanName)){
removeBean(beanName);
}
GenericBeanDefinition definition = new GenericBeanDefinition();
//类class
definition.setBeanClass(clzz);
//属性赋值
definition.setPropertyValues(new MutablePropertyValues(original));
//注册到spring上下文
beanFactory.registerBeanDefinition(beanName, definition);
}
public synchronized static void registerSingletonBean(String beanName,Object obj,Map<String,Object> original) {
checkApplicationContext();
DefaultListableBeanFactory beanFactory = (DefaultListableBeanFactory) ApplicationContextUtil.getApplicationContext().getAutowireCapableBeanFactory();
if(beanFactory.containsBean(beanName)){
removeBean(beanName);
}
GenericBeanDefinition definition = new GenericBeanDefinition();
//类class
definition.setBeanClass(obj.getClass());
//属性赋值
definition.setPropertyValues(new MutablePropertyValues(original));
//注册到spring上下文
beanFactory.registerBeanDefinition(beanName, definition);
}
public synchronized static void registerSingletonBean(String beanName,Object obj) {
registerSingletonBean(beanName,obj,BeanUtils.transBean2Map(obj));
}
/**
* 删除spring中管理的bean
* @param beanName
*/
public static void removeBean(String beanName){
ApplicationContext ctx = ApplicationContextUtil.getApplicationContext();
DefaultListableBeanFactory acf = (DefaultListableBeanFactory) ctx.getAutowireCapableBeanFactory();
if(acf.containsBean(beanName)) {
acf.removeBeanDefinition(beanName);
}
}
}
@Component
public class EnvironmentUtils implements EnvironmentAware {
private static Environment environment ;
@Override
public void setEnvironment(Environment environment) {
EnvironmentUtils.environment=environment;
}
public static Environment getEnvironment(){
return EnvironmentUtils.environment;
}
}
动态数据源注入完成,配置事务之类的照旧 数据源一律使用DynamicDataSource。
在springBoot启动类加上注解@Import(DynamicDataSourceRegister.class)
这样数据源加载就完成。
接下来配置切面实现动态切换数据源,原理:将要访问的数据源放到session中(不一定是session,可以根据自定义业务需要变化)。每次切面都从session中拿到数据源的key (dev、 stg、prd)将这个key设置到当前线程
用ThreadLocal来实现(见DynamicDataSourceContextHolder)
/**
* @ClassName DynamicDataSourceContextHolder
* @Description 判断当前数据源是否存在(上下文)
*/
public class DynamicDataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static List<String> dataSourceIds = new ArrayList<>();
//使用setDataSourceType设置当前的
public static void setDataSourceType(String dataSourceType) {
contextHolder.set(dataSourceType);
}
public static String getDataSourceType() {
return contextHolder.get();
}
public static void clearDataSourceType() {
contextHolder.remove();
}
//判断指定DataSrouce当前是否存在
public static boolean containsDataSource(String dataSourceId){
return dataSourceIds.contains(dataSourceId);
}
}
这也是DynamicDataSource的方法determineCurrentLookupKey()中从当前线程中取数据源的key
@Override
protected Object determineCurrentLookupKey() {
return DynamicDataSourceContextHolder.getDataSourceType();
}
因此接下来的切面要做的事就是在业务方法之前将数据源key设置到当前线程中,在访问结束后再清楚。理论上可以配置环绕切面,为避免很多不必要麻烦这里采用前置、和后置配合使用
项目结构 Controller层 service层 dao(mapper)层
所以切面直接切service层
@Component
@Aspect
public class MultipleDataSourceAspectAdvice implements Ordered {
private static final Logger logger = Logger.getLogger(MultipleDataSourceAspectAdvice.class);
@Before("execution(* com..mapper..*.*(..))")
public void changeDataSource(JoinPoint point) throws Throwable {
String environmentCode = SessionUtil.getSessionAttr(SessionUtil.ENVIRONMENT_CODE,"dev").toString();
logger.info("Use DataSource : "+ environmentCode+"-"+ point.getSignature());
DynamicDataSourceContextHolder.setDataSourceType(environmentCode);
}
@After("execution(* com..service..*.*(..))")
public void clearDataSource2(JoinPoint point) {
//方法执行完毕之后,销毁当前数据源信息,进行垃圾回收。
DynamicDataSourceContextHolder.clearDataSourceType();
}
@After("execution(* com..mapper..*.*(..))")
public void clearDataSource(JoinPoint point) {
//方法执行完毕之后,销毁当前数据源信息,进行垃圾回收。
DynamicDataSourceContextHolder.clearDataSourceType();
}
@Before("execution(* com..service..*.*(..))")
public void switchDataSource(JoinPoint point) throws Throwable {
String environmentCode = SessionUtil.getSessionAttr(SessionUtil.ENVIRONMENT_CODE,"dev").toString();
logger.info("Use DataSource : "+ environmentCode+"-"+ point.getSignature());
DynamicDataSourceContextHolder.setDataSourceType(environmentCode);
}
@Override
public int getOrder() {
return 1;
}
}
但是如果只切service层会有问题存在,如果在service层调用service 就会导致里面的service调用完成之后将当前线程中的key清除了,最终导致接下来的dao获取不到key而走默认库
因此这里又对dao层也切入一次做同样操作,就避免刚才情况。
实际上到这里并不完美,因为如果事务切面先执行,即使现在切换了数据源也没有用了,事务会缓存数据源。所以必须要让切面在事务切面之前执行因此实现Ordered 接口的getOrder()方法。
在启动类添加注解@EnableTransactionManagement(order = 2,proxyTargetClass=true)将事务切面的order设置为2或者更大。
到此动态切换以及动态添加完成。
这是动态添加数据源的接口的实现,非常简单
@ResponseBody
@RequestMapping(method = RequestMethod.POST,value="submitEnvironmentConfig")
public ResponseData submitEnvironmentConfig(@RequestBody List<EnvironmentConfig> environmentConfigs) throws Exception{
if(environmentConfigs==null||environmentConfigs.size()==0){
return new ResponseData.Builder().error("没有数据!");
}
//参数校验
String codes="";
String names="";
//生成prop
Properties properties=new Properties();
for (EnvironmentConfig e:environmentConfigs){
if(StringUtils.isEmpty(e.getCode())||
StringUtils.isEmpty(e.getName())||
StringUtils.isEmpty(e.getDriverClassName())||
StringUtils.isEmpty(e.getPassword())||
StringUtils.isEmpty(e.getUsername())||
StringUtils.isEmpty(e.getUrl())){
return new ResponseData.Builder().error("所有参数不能为空!");
}
codes=codes+","+e.getCode().trim();
names=names+","+e.getName().trim();
properties.setProperty(e.getCode().trim()+DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_SPLIT+DynamicDataSourceRegisterUtil.DRUID_DATASOURCE_PARAM_PREF+DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_PRPO_KEY_DRIVER_CLASS_NAME,e.getDriverClassName());
properties.setProperty(e.getCode().trim()+DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_SPLIT+DynamicDataSourceRegisterUtil.DRUID_DATASOURCE_PARAM_PREF+DruidDataSourceFactory.PROP_URL,e.getUrl());
properties.setProperty(e.getCode().trim()+DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_SPLIT+DynamicDataSourceRegisterUtil.DRUID_DATASOURCE_PARAM_PREF+DruidDataSourceFactory.PROP_USERNAME,e.getUsername());
properties.setProperty(e.getCode().trim()+DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_SPLIT+DynamicDataSourceRegisterUtil.DRUID_DATASOURCE_PARAM_PREF+DruidDataSourceFactory.PROP_PASSWORD,e.getPassword());
}
codes=codes.substring(1);
names=names.substring(1);
properties.setProperty(DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_PREF+"keys",codes);
properties.setProperty(DynamicDataSourceRegisterUtil.DATASOURCE_PARAM_PREF+"names",names);
DynamicDataSourceRegisterUtil.refreshDataSoureProperties(properties);
return new ResponseData.Builder(null).success();
}
谢谢大家,由于时间仓促可能比较混乱,希望给大家带来思路就可以了。欢迎交流。