需求
- 实现年结功能
思路
抱歉，流程图上写的是DDL SQL，应该是DML SQL
如上：
- 根据系统数据库存储的数据源信息，生成多个DataSource Bean
- 按BeanName为key，DataSource为value将数据源信息存入Map集合
- 用户发起请求时携带年度参数
- 根据年度生成的BeanName在Map中查找对应的DataSource
- 设置当前数据源为上一步查找到的DataSource
- 获取数据库连接执行请求操作并返回结果
数据库结构
相关源码解析
如不需要看此部分，请下拉查看代码实现
多数据源切换主要依赖于AbstractRoutingDataSource类，源码如下：
// org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource
// 代码顺序与源码有差，部分无关代码已忽略
public abstract class AbstractRoutingDataSource extends AbstractDataSource implements InitializingBean {
// 存储数据源集合
@Nullable
private Map<Object, Object> targetDataSources;
// 获取数据库连接,到底取的哪个数据源由determineTargetDataSource()决定
@Override
public Connection getConnection() throws SQLException {
return determineTargetDataSource().getConnection();
}
// 获取指定的数据源
protected DataSource determineTargetDataSource() {
Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
// 这里决定了DataSource在targetDataSources集合中对应的key值
Object lookupKey = determineCurrentLookupKey();
// 从Map中取数据源,如未取到则使用默认的数据源,默认数据源如果也是null值则报错!
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;
}
// 指定数据源实例在targetDataSources集合中对应的key值
// 通过此方法实现数据源的切换
protected abstract Object determineCurrentLookupKey();
// 这里根据targetDataSources成员Value值的类型查找相应的DataSource，并将其添加到resolvedDataSources成员集合中
@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);
// 根据targetDataSources成员Value值查找相应的DataSource填充到resolvedDataSources成员集合
DataSource dataSource = resolveSpecifiedDataSource(value);
this.resolvedDataSources.put(lookupKey, dataSource);
});
if (this.defaultTargetDataSource != null) {
this.resolvedDefaultDataSource = resolveSpecifiedDataSource(this.defaultTargetDataSource);
}
}
// 其他代码省略...
}
实现
基于如上源码，我们可以创建DynamicDataSource类，实现determineCurrentLookupKey()方法以达到动态数据源切换的功能，代码如下：
- 动态注入Druid DataSource Bean工具类：
/**
* DataSource 年度信息
*/
public class AnnualInfo {
private Integer id;
private String dbName;
private Integer year;
private Integer status;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public Integer getYear() {
return year;
}
public void setYear(Integer year) {
this.year = year;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
}
import cn.apcinfo.pojo.AnnualInfo;
import cn.apcinfo.properties.DbConnProperties;
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.springframework.beans.factory.config.BeanDefinition;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.context.ConfigurableApplicationContext;
import java.util.ArrayList;
import java.util.List;
/**
* Druid DataSource Bean 注册工具类
*/
public class ManualRegisteredDsBeanUtil {
/**
* 主动向Spring容器中注册bean
* @param applicationContext Spring容器
* @param annualInfo DataSource 年度信息(存储了年度、应用数据库名称)
*/
public static void register(
ConfigurableApplicationContext applicationContext,
AnnualInfo annualInfo,
DbConnProperties dbConnProperties) {
// 构建Bean名称
// 获取Bean的前缀(此前缀不允许重复 - 固定标志+年度)
String beanPrefix = getBeanPrefix(annualInfo.getYear());
String wallConfigBeanName = beanPrefix + "WallConfig";
String wallFilterBeanName = beanPrefix + "WallFilter";
String dataSourceBeanName = beanPrefix + "DataSource";
// 检查DataSource Bean是否存在,如果存在则直接推出
if(applicationContext.containsBean(dataSourceBeanName)) {
Object bean = applicationContext.getBean(dataSourceBeanName);
if (bean.getClass().isAssignableFrom(DruidDataSource.class)) {
return;
} else {
throw new RuntimeException("Bean 名称重复,且该Bean不是DataSource Bean!");
}
}
BeanDefinitionRegistry beanFactory = (BeanDefinitionRegistry) applicationContext.getBeanFactory();
// 构造参数Bean wallConfig
BeanDefinitionBuilder beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(WallConfig.class);
BeanDefinition beanDefinition = beanDefinitionBuilder.getRawBeanDefinition();
beanFactory.registerBeanDefinition(wallConfigBeanName, beanDefinition);
WallConfig wallConfig = (WallConfig) applicationContext.getBean(wallConfigBeanName);
// 是否允许一次执行多条SQL脚本，如遇特殊情况，比如局部临时表数据存储后读取需要在此处设置为true
wallConfig.setMultiStatementAllow(true);
// 如遇druid无法支持的语法，如：UNPIVOT、WITH TEMP等，此处设置为false，不做严格语法检查
wallConfig.setStrictSyntaxCheck(false);
// 构造参数Bean wallFilter
beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(WallFilter.class);
beanDefinition = beanDefinitionBuilder.getRawBeanDefinition();
beanFactory.registerBeanDefinition(wallFilterBeanName, beanDefinition);
WallFilter wallFilter = (WallFilter) applicationContext.getBean(wallFilterBeanName);
wallFilter.setConfig(wallConfig);
// 构造DataSource Bean
beanDefinitionBuilder = BeanDefinitionBuilder.genericBeanDefinition(DruidDataSource.class);
beanDefinition = beanDefinitionBuilder.getRawBeanDefinition();
beanFactory.registerBeanDefinition(dataSourceBeanName, beanDefinition);
DruidDataSource druidDataSource = (DruidDataSource) applicationContext.getBean(dataSourceBeanName);
// 设置连接信息，这里默认所有数据库在同一服务器上，直接替换库名即可
druidDataSource.setDriverClassName(dbConnProperties.getDriverClassName());druidDataSource.setUrl(dbConnProperties.getUrl().replaceAll("3306\\/.+\\?","3306\\/"+annualInfo.getDbName()+"\\?"));
druidDataSource.setUsername(dbConnProperties.getUsername());
druidDataSource.setPassword(dbConnProperties.getPassword());
// 设置过滤信息
List<Filter> filterList = new ArrayList<Filter>();
filterList.add(wallFilter);
druidDataSource.setProxyFilters(filterList);
}
private static String getBeanPrefix(Integer year){
return "dj"+year;
}
}
- 自定义动态初始化DataSource Bean的ApplicationRunner
import cn.apcinfo.pojo.AnnualInfo;
import cn.apcinfo.properties.DbConnProperties;
import cn.apcinfo.service.YearService;
import cn.apcinfo.util.ManualRegisteredDsBeanUtil;
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;
@Component
public class DynamicDsRegisterApplicationRunner implements ApplicationRunner {
private static final Logger log = LoggerFactory.getLogger(DynamicDsRegisterApplicationRunner.class);
@Autowired
private ApplicationContext applicationContext;
@Autowired
private DbConnProperties properties;
@Autowired
private YearService yearService;
@Override
public void run(ApplicationArguments args) throws Exception {
// 获取待注册数据源Bean的年度信息
List<AnnualInfo> annualInfoList = yearService.list();
// 注册各年度数据源
for(AnnualInfo annualInfo:annualInfoList){
ManualRegisteredDsBeanUtil.register((ConfigurableApplicationContext) applicationContext,annualInfo,properties);
}
// 验证Bean是否已注册成功
Map<String,DruidDataSource> beans = applicationContext.getBeansOfType(DruidDataSource.class);
if(beans.size() != annualInfoList.size()+1){
// 抛出系统运行错误,中止应用启动
throw new Error("Application Start Failed: DataSource Bean Register Failed,Please Check DataSource Configuration is valid!");
}
}
}
- 创建动态数据源调度类
/**
* 数据源调度类
* 将需要使用的数据源bean名称存储到threadLocal
*/
public class DataSourceScheduling {
private static ThreadLocal<String> dSBeanNameThreadLocal = new ThreadLocal<>();
public static void set(String dSBeanName){
dSBeanNameThreadLocal.set(dSBeanName);
}
public static String get(){
return dSBeanNameThreadLocal.get();
}
public static void clear() {
dSBeanNameThreadLocal.remove();
}
}
- 自定义DataSource集成AbstractRoutingDataSource类并重写determineCurrentLookupKey()
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
/**
* 动态数据源
* 管理系统内的所有数据源,并根据需要对这些数据源进行调度
*/
public class DynamicDataSource extends AbstractRoutingDataSource implements ApplicationContextAware {
private static final Logger log = LoggerFactory.getLogger(DynamicDataSource.class);
/**
* 注入Spring 上下文，从中获取DataSource Bean
*/
private ApplicationContext applicationContext;
/**
* 每一次执行SQL语句之前都会执行此方法,以确定本次访问数据库所对应的数据源的key
* 这里将数据源的bean name作为数据源的key 以方便后续调度数据数据源
* @return
*/
@Override
protected Object determineCurrentLookupKey() {
try {
// 如果没有添加动态数据源，则添加一次。否则不处理
// 注意,由于系统调用过此方法,targetDataSources的size可能为0也可能为1
if(getTargetSource().size() == 0 || getTargetSource().size() == 1) {
Map<Object,Object> dataSources = new HashMap<Object,Object>();
dataSources.putAll(applicationContext.getBeansOfType(DruidDataSource.class));
super.setTargetDataSources(dataSources);
super.afterPropertiesSet();
}
} catch (Exception ex) {
// ignore
}
Object key = DataSourceScheduling.get();
// 如果没有指定数据源,则使用默认的数据源
if(key == null){
key = "baseDataSource";
}
return key;
}
/**
* 通过反射获取AbstractRoutingDataSource的targetDataSources属性
* @return targetDataSources的值
*/
@SuppressWarnings("unchecked")
public Map<Object, Object> getTargetSource() throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException {
Field field = AbstractRoutingDataSource.class.getDeclaredField("targetDataSources");
field.setAccessible(true);
return (Map<Object, Object>) field.get(this);
}
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
this.applicationContext = applicationContext;
}
}
- 注入SqlSessionFactory
import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.alibaba.druid.wall.WallConfig;
import com.alibaba.druid.wall.WallFilter;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Configuration
@EnableConfigurationProperties({MybatisProperties.class})
public class SqlSessionFactoryConfig {
@Value("${mybatis.mapper-locations}")
private String mapperLocation;
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
factory.setDataSource(dataSource);
factory.setVfs(SpringBootVFS.class);
PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
// 能加载多个，所以可以配置通配符(如：classpath*:mapper/**/*.xml)
factory.setMapperLocations(resourcePatternResolver.getResources(mapperLocation));
return factory.getObject();
}
@Bean(name = "dataSource")
public DataSource dataSource(@Qualifier("baseDataSource")DataSource baseDataSource){
// 可调度数据源的父级数据源
DynamicDataSource dataSource = new DynamicDataSource();
// 存储可选择的数据源集合
Map<Object,Object> targetDataSources = new HashMap<Object,Object>();
dataSource.setTargetDataSources(targetDataSources);
// 存储默认数据源
dataSource.setDefaultTargetDataSource(baseDataSource);
dataSource.afterPropertiesSet();
return dataSource;
}
@ConfigurationProperties(prefix = "spring.datasource")
@Bean(name = "baseDataSource")
public DruidDataSource druid(@Qualifier("wallFilter") WallFilter wallFilter) {
DruidDataSource druidDataSource = new DruidDataSource();
List<Filter> filterList = new ArrayList<Filter>();
filterList.add(wallFilter);
druidDataSource.setProxyFilters(filterList);
return druidDataSource;
}
@Bean(name = "wallFilter")
public WallFilter wallFilter(@Qualifier("wallConfig") WallConfig wallConfig) {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig);
return wallFilter;
}
@Bean(name = "wallConfig")
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
// 是否允许一次执行多条SQL脚本，如遇特殊情况，比如局部临时表数据存储后读取需要在此处设置为true
config.setMultiStatementAllow(true);
// 如遇druid无法支持的语法，如：UNPIVOT、WITH TEMP等，此处设置为false，不做严格语法检查
config.setStrictSyntaxCheck(false);
return config;
}
@Bean
public ServletRegistrationBean<StatViewServlet> statViewServlet() {
// 创建servlet注册实体
ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(
new StatViewServlet(), "/druid/*");
// 设置ip白名单
servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
// 设置控制台管理用户
// servletRegistrationBean.addInitParameter("loginUsername", "apcinfo");
// servletRegistrationBean.addInitParameter("loginPassword", "www.apcinfo.cn");
// 是否可以重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean<WebStatFilter> statFilter() {
// 创建过滤器
FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>(
new WebStatFilter());
// 设置过滤器过滤路径
filterRegistrationBean.addUrlPatterns("/*");
// 忽略过滤的形式
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
}
- 解决dataSource循环引用的问题
// 启动类导入自定义的SqlSessionFactory配置类
@Import(SqlSessionFactoryConfig.class)
// 排除原始数据源自动配置类
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
public class MulitpledsdemoApplication {
public static void main(String[] args) {
SpringApplication.run(MulitpledsdemoApplication.class, args);
}
}
至此，代码部分结束
测试
由于后续需要通过Http请求拦截实现动态数据源的切换,此处我添加了自定义拦截器进行测试
- 自定义拦截器
import cn.apcinfo.ds.DataSourceScheduling;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@Component
public class DynamicDsInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
// 获取请求域中的年度参数
String yearStr = request.getParameter("apcAnnualYear");
Integer year = null;
if(yearStr != null && !"".equals(yearStr.trim())){
year = Integer.valueOf(yearStr);
}
String dsBeanName = null;
if(year != null){
dsBeanName = "dj"+year+"DataSource";
}
DataSourceScheduling.set(dsBeanName);
return true;
}
}
- 配置拦截器
import cn.apcinfo.interceptor.DynamicDsInterceptor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;
@Configuration
public class DefineMvcConfig implements WebMvcConfigurer {
@Autowired
private DynamicDsInterceptor dynamicDsInterceptor;
@Bean
public WebMvcConfigurer webMvcConfigurerAdapter(){
return new WebMvcConfigurer(){
//注册拦截器
@Override
public void addInterceptors(InterceptorRegistry registry) {
registry.addInterceptor(dynamicDsInterceptor).addPathPatterns("/**");
}
};
}
}
- 构造数据库数据
- djcloud_2019数据：
- djcloud_2020数据：
- djcloud_2021数据：
- djcloud_2022数据：
- 请求如上表中数据
至此，SpringBoot整合Druid实现多个动态数据源按所选年度切换功能成功实现
踩坑点
- 实例化DynamicDataSource时必须要设置targetDataSources的值，否则报错，异常信息： java.lang.IllegalArgumentException: Property ‘targetDataSources’ is required，相关代码如下：
DynamicDataSourcedataSource = new DynamicDataSource();
// 必须设置targetDataSources，可为空集合
Map<Object,Object> targetDataSources = new HashMap<Object,Object>();
dataSource.setTargetDataSources(targetDataSources);
- 设置targetDataSources属性值后,如果targetDataSources不为空集合，一定要调用afterPropertiesSet()，否则报错，异常信息：java.sql.SQLSyntaxErrorException: Table ‘djcloud_sys.integral_annex’ doesn’t exist，因为数据源最终是从resolvedDataSources成员中获取的，而afterPropertiesSet()就是根据targetDataSources成员的值更新resolvedDataSources成员的值，相关代码如下：
// 注意,由于系统调用过此方法,targetDataSources的size可能为0也可能为1
Map<Object,Object> dataSources = new HashMap<Object,Object>();
dataSources.putAll(applicationContext.getBeansOfType(DruidDataSource.class));
super.setTargetDataSources(dataSources);
super.afterPropertiesSet();
- 启动类务必排除掉 DataSourceAutoConfiguration.class 并引入自定义SqlSessionFactory配置类，否则会出现dataSource循环引用的问题。
感谢
特别感谢如下博客分享的相关知识：
SpringBoot启动自动执行方法
SpringBoot动态注入Bean
动态数据源切换
171
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
