由于公司的业务是分了N多数据库的，不能一次性把所有数据源信息写入配置文件，在启动项目时全部加载不符合业务要求，因为数据库会增加，不可能每次去改配置文件和代码配置再启动程序重载。
因此要做一个在执行业务时，根据不同公司的数据来切换数据库连接信息，这个是前提，采用流行的springboot。
先重写springboot自动数据源配置，设置一个默认数据库信息，取properties文件的数据
@Configuration
public class DataSourceConfig {
// -----------------------------------------default config-------------------------------------
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.druid.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.druid.validation-query}")
private String validationQuery;
@Bean(name="defaultDataSource")
public DataSource dataSourceDefault(){
System.out.println("----------------默认数据源" + dbUrl);
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(dbUrl);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setValidationQuery(validationQuery);
setDruidOptions(datasource); // 设置druid数据源的属性
return datasource;
}
// -----------------------------------------druid config-------------------------------------
@Value("${spring.datasource.druid.initial-size}")
private int initialSize;
@Value("${spring.datasource.druid.min-idle}")
private int minIdle;
@Value("${spring.datasource.druid.max-active}")
private int maxActive;
@Value("${spring.datasource.druid.max-wait}")
private int maxWait;
@Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.test-while-idle}")
private boolean testWhileIdle;
@Value("${spring.datasource.druid.test-on-borrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.druid.test-on-return}")
private boolean testOnReturn;
@Value("${spring.datasource.druid.pool-prepared-statements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.druid.filters}")
private String filters;
@Value("${spring.datasource.druid.connectionProperties}")
private String connectionProperties;
private void setDruidOptions(DruidDataSource datasource){
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
/*
* try { datasource.setFilters(filters); } catch (SQLException e) {
* logger.error("druid configuration initialization filter Exception", e); }
*/
//datasource.setConnectionProperties(connectionProperties);
List<com.alibaba.druid.filter.Filter> filterList = new ArrayList<>();
filterList.add(wallFilter());
datasource.setProxyFilters(filterList);
}
@Bean(name = "druidDynamicDataSource")
@Primary // 优先使用，多数据源
public DataSource dataSource(){
DruidDynamicDataSource dynamicDataSource = new DruidDynamicDataSource();
DataSource defaultSource = dataSourceDefault();
//设置默认数据源
dynamicDataSource.setDefaultTargetDataSource(defaultSource);
//配置多个数据源
Map<Object,Object> map = new HashMap<>();
map.put("defaultDataSource",defaultSource);
dynamicDataSource.setTargetDataSources(map);
return dynamicDataSource;
}
@Bean // 事务管理
public PlatformTransactionManager txManager() {
return new DataSourceTransactionManager(dataSource());
}
@Bean(name="druidServlet")
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("allow", ""); // 白名单
return reg;
}
@Bean(name = "filterRegistrationBean")
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName","USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName","USER_SESSION");
filterRegistrationBean.addInitParameter("DruidWebStatFilter","/*");
return filterRegistrationBean;
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
wallFilter.setConfig(wallConfig());
return wallFilter;
}
@Bean
public WallConfig wallConfig() {
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);//允许一次执行多条语句
config.setNoneBaseStatementAllow(true);//允许非基本语句的其他语句
return config;
}
}
启动类@SpringBootApplication注解加上(exclude = DataSourceAutoConfiguration.class)关掉自动配置
接着建立抽象类AbstractDynamicDataSource继承AbstractRoutingDataSource实现ApplicationContextAware在mybatis访问库前都会查下当前数据库连接信息来判断数据切换
public abstract class AbstractDynamicDataSource<T extends DataSource> extends AbstractRoutingDataSource
implements ApplicationContextAware {
/** 日志 */
protected Logger logger = LoggerFactory.getLogger(getClass());
/** 默认的数据源KEY，和spring配置文件中的id=druidDynamicDataSource的bean中配置的默认数据源key保持一致 */
protected static final String DEFAULT_DATASOURCE_KEY = "defaultDataSource";
/** 数据源KEY-VALUE键值对 */
public Map<Object, Object> targetDataSources;
/** spring容器上下文 */
private static ApplicationContext ctx;
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
ctx = applicationContext;
}
public static ApplicationContext getApplicationContext() {
return ctx;
}
public static Object getBean(String name) {
return ctx.getBean(name);
}
/**
* @param targetDataSources the targetDataSources to set
*/
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
this.targetDataSources = targetDataSources;
super.setTargetDataSources(targetDataSources);
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
super.afterPropertiesSet();
}
/**
* 创建数据源
*
* @param driverClassName 数据库驱动名称
* @param url 连接地址
* @param username 用户名
* @param password 密码
* @return 数据源{@link T}
*/
public abstract T createDataSource(String driverClassName, String url, String username, String password);
/**
* 设置系统当前使用的数据源
* <p>
* 数据源为空或者为0时，自动切换至默认数据源，即在配置文件中定义的默认数据源
*
* @see org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource#determineCurrentLookupKey()
*/
@Override
protected Object determineCurrentLookupKey() {
logger.debug("【设置系统当前使用的数据源】");
Map<String, Object> configMap = DBContextHolder.getDBType();
logger.debug("【当前数据源配置为：{}】", configMap);
if (JudgeUtils.isEmpty(configMap)) {
// 使用默认数据源
return DEFAULT_DATASOURCE_KEY;
}
// 判断数据源是否需要初始化
this.verifyAndInitDataSource();
logger.debug("【切换至数据源：{}】", configMap);
return configMap.get(DBContextHolder.DATASOURCE_KEY);
}
/**
* 判断数据源是否需要初始化
*
*/
private void verifyAndInitDataSource() {
Map<String, Object> configMap = DBContextHolder.getDBType();
Object obj = this.targetDataSources.get(configMap.get(DBContextHolder.DATASOURCE_KEY));
if (obj != null) {
return;
}
logger.debug("【初始化数据源】");
T datasource = this.createDataSource(configMap.get(DBContextHolder.DATASOURCE_DRIVER).toString(),
configMap.get(DBContextHolder.DATASOURCE_URL).toString(),
configMap.get(DBContextHolder.DATASOURCE_USERNAME).toString(),
configMap.get(DBContextHolder.DATASOURCE_PASSWORD).toString());
this.addTargetDataSource(configMap.get(DBContextHolder.DATASOURCE_KEY).toString(), datasource);
}
/**
* 往数据源key-value键值对集合添加新的数据源
*
* @param key 新的数据源键
* @param dataSource 新的数据源
*/
private void addTargetDataSource(String key, T dataSource) {
this.targetDataSources.put(key, dataSource);
super.setTargetDataSources(this.targetDataSources);
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
super.afterPropertiesSet();
}
}
接着写一个AbstractDynamicDataSource的子类DruidDynamicDataSource实现抽象方法和接口
public class DruidDynamicDataSource extends AbstractDynamicDataSource<DruidDataSource> {
private boolean testWhileIdle = true;
private boolean testOnBorrow = false;
private boolean testOnReturn = false;
// 是否打开连接泄露自动检测
private boolean removeAbandoned = false;
// 连接长时间没有使用，被认为发生泄露时长
private long removeAbandonedTimeoutMillis = 300 * 1000;
// 发生泄露时是否需要输出 log，建议在开启连接泄露检测时开启，方便排错
private boolean logAbandoned = false;
// 只要maxPoolPreparedStatementPerConnectionSize>0,poolPreparedStatements就会被自动设定为true，使用oracle时可以设定此值。
// private int maxPoolPreparedStatementPerConnectionSize = -1;
// 配置监控统计拦截的filters
private String filters; // 监控统计："stat" 防SQL注入："wall" 组合使用： "stat,wall"
private List<Filter> filterList;
/*
* 创建数据源，这里创建的数据源是带有连接池属性的
*
* @see
* com.cdelabcare.pubservice.datasource.IDynamicDataSource#createDataSource(java
* .lang.String, java.lang.String, java.lang.String, java.lang.String)
*/
@Override
public DruidDataSource createDataSource(String driverClassName, String url, String username, String password) {
DruidDataSource parent = (DruidDataSource) super.getApplicationContext().getBean(DEFAULT_DATASOURCE_KEY);
DruidDataSource ds = new DruidDataSource();
ds.setUrl(url);
ds.setUsername(username);
ds.setPassword(password);
ds.setDriverClassName(driverClassName);
ds.setInitialSize(parent.getInitialSize());
ds.setMinIdle(parent.getMinIdle());
ds.setMaxActive(parent.getMaxActive());
ds.setMaxWait(parent.getMaxWait());
ds.setTimeBetweenConnectErrorMillis(parent.getTimeBetweenConnectErrorMillis());
ds.setTimeBetweenEvictionRunsMillis(parent.getTimeBetweenEvictionRunsMillis());
ds.setMinEvictableIdleTimeMillis(parent.getMinEvictableIdleTimeMillis());
ds.setValidationQuery(parent.getValidationQuery());
ds.setTestWhileIdle(testWhileIdle);
ds.setTestOnBorrow(testOnBorrow);
ds.setTestOnReturn(testOnReturn);
ds.setRemoveAbandoned(removeAbandoned);
ds.setRemoveAbandonedTimeoutMillis(removeAbandonedTimeoutMillis);
ds.setLogAbandoned(logAbandoned);
// 只要maxPoolPreparedStatementPerConnectionSize>0,poolPreparedStatements就会被自动设定为true，参照druid的源码
ds.setMaxPoolPreparedStatementPerConnectionSize(parent.getMaxPoolPreparedStatementPerConnectionSize());
if (StringUtil.isNotBlank(filters)) {
try {
ds.setFilters(filters);
} catch (SQLException e)
{ throw new RuntimeException(e); }
}
addFilterList(ds);
return ds;
}
private void addFilterList(DruidDataSource ds) {
if (filterList != null) {
List<Filter> targetList = ds.getProxyFilters();
for (Filter add : filterList) {
boolean found = false;
for (Filter target : targetList) {
if (add.getClass().equals(target.getClass())) {
found = true;
break;
}
}
if (!found)
targetList.add(add);
}
}
}
}
建立线程上下文确认线程安全并做切换动作
public class DBContextHolder {
/* 数据源的KEY */
public static final String DATASOURCE_KEY = "DATASOURCE_KEY";
/* 数据源的URL */
public static final String DATASOURCE_URL = "DATASOURCE_URL";
/* 数据源的驱动 */
public static final String DATASOURCE_DRIVER = "DATASOURCE_DRIVER";
/* 数据源的用户名 */
public static final String DATASOURCE_USERNAME = "DATASOURCE_USERNAME";
/* 数据源的密码 */
public static final String DATASOURCE_PASSWORD = "DATASOURCE_PASSWORD";
/* 默认的数据库驱动*/
public static final String DATASOURCE_DRIVECLASS = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final ThreadLocal<Map<String, Object>> contextHolder = new ThreadLocal<Map<String, Object>>();
public static void setDBType(Map<String, Object> dataSourceConfigMap) {
contextHolder.set(dataSourceConfigMap);
}
public static Map<String, Object> getDBType() {
Map<String, Object> dataSourceConfigMap = contextHolder.get();
if (dataSourceConfigMap == null) {
dataSourceConfigMap = new HashMap<String, Object>();
}
return dataSourceConfigMap;
}
public static void clearDBType() {
contextHolder.remove();
}
设置方法在跑业务时设置需要访问的库
Map<String,Object> dataSource = new HashMap<>();
dataSource.put(DBContextHolder.DATASOURCE_KEY, key);
dataSource.put(DBContextHolder.DATASOURCE_URL, url);
dataSource.put(DBContextHolder.DATASOURCE_DRIVER, DBContextHolder.DATASOURCE_DRIVECLASS);
dataSource.put(DBContextHolder.DATASOURCE_USERNAME, username);
dataSource.put(DBContextHolder.DATASOURCE_PASSWORD, password);
DBContextHolder.setDBType(dataSource);
并在业务最后清楚数据源信息，建议try-catch业务逻辑后加finally块再最后清除
DBContextHolder.clearDBType();
以下就是springboot在不知道具体数据源信息的前提下，执行业务时动态切换数据源的方法，亲测可用。
参考了一些博文，如有侵权随时联系我。
扫一扫
6128
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
