Spring动态数据源切换
如图所示,软件初始化时通过第一个ip连接数据库,当软件正常运行过程中,监控器判断第一个网段出现故障,自动切换到后续网段,不影响软件正常工作
1、动态数据源配置
Spring boot提供了AbstractRoutingDataSource 根据用户定义的规则选择当前的数据源,这样我们可以在执行查询之前,设置使用的数据源。实现可动态路由的数据源,在每次数据库查询操作前执行。它的抽象方法 determineCurrentLookupKey() 决定使用哪个数据源。
@Slf4j
@Configuration
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class DynamicDataSourceConfig {
@Resource
private DynamicDataSourceProperties dynamicDataSourceProperties;
@Bean
public AbstractRoutingDataSource dataSource(Environment env) {
// 实现AbstractRoutingDataSource的determineCurrentLookupKey方法,该方法会返回当前要使用的数据源对应的dsKey
AbstractRoutingDataSource abstractRoutingDataSource = new AbstractRoutingDataSource() {
@Override
protected Object determineCurrentLookupKey() {
if (!BasicCommonCacheUtils.exist(com.tct.itd.common.cache.Cache.HUB_DATASOURCE_KEY)) {
BasicCommonCacheUtils.set(com.tct.itd.common.cache.Cache.HUB_DATASOURCE_KEY, "0");
return "0";
}
return BasicCommonCacheUtils.get(com.tct.itd.common.cache.Cache.HUB_DATASOURCE_KEY);
}
};
// 设置所有数据源
Map<Object, Object> dataSourceMap = new HashMap<>();
//获取yml数据源配置对象
HikariDataSource datasources = DataSourceConfigUtil.setDataSourceEnvConfig("spring.datasource.", "spring.datasource.hikari.", env);
//从excel中读取数据库服务器ip存入map缓存;
...
//替换数据源ip
if (datasources.getDriverClassName().contains("kingbase")) {
if (configDtos.size()==2){
for (int j = 0; j < deviceIpPortDtos.size(); j++) {
HikariDataSource datasources2 = new HikariDataSource();
BeanUtils.copyProperties(datasources, datasources2);
String newUrl2 = datasources2.getJdbcUrl().replace(datasources2.getJdbcUrl().substring(datasources2.getJdbcUrl().indexOf("//") + 2, datasources2.getJdbcUrl().lastIndexOf("/")),
deviceIpPortDtos.get(j).getAddress() + ":" + deviceIpPortDtos.get(j).getPort());
datasources2.setJdbcUrl(newUrl2);
dataSourceMap.put(j + "", datasources2);
DataSourceHashMapCache.add(j, datasources2);
}
}else {
//ip转,,,
List<String> ipList = deviceIpPortDtos.stream().map(DeviceIpPortDto::getAddress).collect(Collectors.toList());
String ipString = ipList.stream().collect(Collectors.joining(","));
//port转,,,
List<Integer> portList = deviceIpPortDtos.stream().map(DeviceIpPortDto::getPort).collect(Collectors.toList());
String portString = portList.stream().map(String::valueOf).collect(Collectors.joining(","));
//替换url
HikariDataSource datasources2 = new HikariDataSource();
BeanUtils.copyProperties(datasources, datasources2);
String newUrl2 = datasources2.getJdbcUrl().replace(datasources2.getJdbcUrl().substring(datasources2.getJdbcUrl().indexOf("//") + 2, datasources2.getJdbcUrl().lastIndexOf("/")),
ipString + ":" + portString);
datasources2.setJdbcUrl(newUrl2);
dataSourceMap.put("0", datasources2);
DataSourceHashMapCache.add(0, datasources2);
}
}
//替换数据源ip
if (datasources.getDriverClassName().contains("oracle")) {
for (int j = 0; j < deviceIpPortDtos.size(); j++) {
//数据源存入map
HikariDataSource datasources2 = new HikariDataSource();
BeanUtils.copyProperties(datasources, datasources2);
String newUrl2 = "";
newUrl2 = datasources2.getJdbcUrl().replace(datasources2.getJdbcUrl().substring(datasources2.getJdbcUrl().indexOf("@") + 1, datasources2.getJdbcUrl().lastIndexOf(":")),
deviceIpPortDtos.get(j).getAddress() + ":" + deviceIpPortDtos.get(j).getPort());
datasources2.setJdbcUrl(newUrl2);
//将配置的ip按顺序以key-value的方式存入Map
dataSourceMap.put(j + "", datasources2);
DataSourceHashMapCache.add(j, datasources2);
}
}
//将所有数据源配置按key-value键值对存入,determineCurrentLookupKey()方法根据key来分配数据源连接
abstractRoutingDataSource.setTargetDataSources(dataSourceMap);
// 设置默认数据源 当dsKey找不到对应的数据源或没有设置数据源时, 使用默认数据源
abstractRoutingDataSource.setDefaultTargetDataSource(dataSourceMap.get("0"));
// afterPropertiesSet()方法调用时用来将targetDataSources的属性写入resolvedDataSources中的
abstractRoutingDataSource.afterPropertiesSet();
return abstractRoutingDataSource;
}
}
2、获取yml配置
从Environment中获取数据源配置,并存入HikariDataSource中
public class DataSourceConfigUtil {
/**
* 从Environment中获取数据源配置,并存入HikariDataSource中
* (直接用在ConfigurationProperties中用HikariDataSource接收时,无法获取到连接池的信息)
* @param prefix1
* @param prefix
* @param env
* @return
*/
public static HikariDataSource setDataSourceEnvConfig(String prefix1, String prefix, Environment env) {
HikariDataSource config = new HikariDataSource();
String driver=env.getProperty(prefix1 + "driver-class-name");
String dataSourceUrl= env.getProperty(prefix1 + "jdbc-url");
String user=env.getProperty(prefix1 + "username");
String password=env.getProperty(prefix1 + "password");
String minimumIdle = env.getProperty(prefix + "minimum-idle");
String maximumPoolSize = env.getProperty(prefix + "maximum-pool-size");
String autoCommit = env.getProperty(prefix + "auto-commit");
String idleTimeout = env.getProperty(prefix + "idle-timeout");
String poolName = env.getProperty(prefix + "pool-name");
String maxLifetime = env.getProperty(prefix + "max-lifetime");
String connectionTimeout = env.getProperty(prefix + "connection-timeout");
String dataSourceClassName= env.getProperty(prefix1+ "type");
String connectionTestQuery= env.getProperty(prefix+ "connection-test-query");
String validationTimeout= env.getProperty(prefix+ "validation-timeout");
String initializationFailtimeout= env.getProperty(prefix+ "initialization-fail-timeout");
if (StringUtils.isNotBlank(initializationFailtimeout)){
config.setInitializationFailTimeout(Integer.parseInt(initializationFailtimeout));
}
if (StringUtils.isNotBlank(validationTimeout)){
config.setValidationTimeout(Integer.parseInt(validationTimeout));
}
if (StringUtils.isNotBlank(connectionTestQuery)){
if (driver.contains("kingbase")){
config.setConnectionTestQuery("SELECT 1");
}
if (driver.contains("oracle")){
config.setConnectionTestQuery("SELECT 1 FROM DUAL");
}
}
if (StringUtils.isNotBlank(dataSourceUrl)){
config.setJdbcUrl(dataSourceUrl);
}
if (StringUtils.isNotBlank(user)){
config.setUsername(user);
}
if (StringUtils.isNotBlank(password)){
config.setPassword(password);
}
if (StringUtils.isNotBlank(driver)){
config.setDriverClassName(driver);
}
if (StringUtils.isNotBlank(minimumIdle)){
config.setMinimumIdle(Integer.parseInt(minimumIdle));
}
if (StringUtils.isNotBlank(maximumPoolSize)) {
config.setMaximumPoolSize(Integer.parseInt(maximumPoolSize));
}
if (StringUtils.isNotBlank(autoCommit)) {
config.setAutoCommit(Boolean.parseBoolean(autoCommit));
}
if (StringUtils.isNotBlank(idleTimeout)) {
config.setIdleTimeout(Integer.parseInt(idleTimeout));
}
if (StringUtils.isNotBlank(poolName)){
config.setPoolName(poolName);
}
if (StringUtils.isNotBlank(maxLifetime)) {
config.setMaxLifetime(Integer.parseInt(maxLifetime));
}
if (StringUtils.isNotBlank(connectionTimeout)) {
config.setConnectionTimeout(Integer.parseInt(connectionTimeout));
}
return config;
}
}
3、数据库连接监控
监控器监控当前数据源连接是否可用(implements ApplicationRunner,启动时调用该方法)
@Component
@Slf4j
@EnableConfigurationProperties(DynamicDataSourceProperties.class)
public class DatasourceActuator {
private final static String TIME_OUT = "&connectTimeout=500";
@Resource
private DynamicDataSourceProperties dynamicDataSourceProperties;
public static Integer ATS_HEART_LOSS = 300;
@Async
public void datasourceCycleActuator() {
// 默认可用则优先使用第一个数据源
while (true) {
int j = 0;
for (int i = 0; i < DataSourceHashMapCache.DATASOURCE_MAP.size(); i++) {
//当监测到数据库可以连接时
if (testDatasource(DataSourceHashMapCache.get(i))) {
//存在数据库断开标识时,提醒客户端已恢复,删除标识,
if (BasicCommonCacheUtils.exist(Cache.DATASOURCE_ACTUATOR)) {
AppPushUtil.sendWebNoticeMessageToAny(new WebNoticeDto(ATS_HEART_LOSS, "0",
"数据库连接恢复"));
BasicCommonCacheUtils.delKey(Cache.DATASOURCE_ACTUATOR);
}
//设置可用数据源对应的key,存入缓存,determineCurrentLookupKey()方法返回值便是该缓存值
BasicCommonCacheUtils.set(Cache.HUB_DATASOURCE_KEY, i + "");
break;
} else {
++j;
}
}
//当所有ip都连接不上时,推送数据库连接断开,提醒客户端,并添加缓存标识
if (j == DataSourceHashMapCache.DATASOURCE_MAP.size()) {
AppPushUtil.sendWebNoticeMessageToAny(new WebNoticeDto(ATS_HEART_LOSS, "0",
"数据库连接断开"));
BasicCommonCacheUtils.set(Cache.DATASOURCE_ACTUATOR, 1);
}
try {
Thread.sleep(10);
} catch (InterruptedException e) {
log.error("数据源监控异常!", e);
}
}
}
/**
* 检测数据源连接可用
*
* @param dataSource 数据源
* @return 是否可用
*/
private Boolean testDatasource(HikariDataSource dataSource) {
Connection con = null;
try {
con = DriverManager.getConnection(dataSource.getJdbcUrl(), dataSource.getUsername(), dataSource.getPassword());
} catch (SQLException e) {
log.info("数据源连接异常,切换数据源", e);
return Boolean.FALSE;
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return Boolean.TRUE;
}
}