前言
mysql双主复制已经搞完了,干点实事。在代码里面使用多个数据源,并实现自动检测数据源的可用性,实现代码层面的数据库热切换。
原理
Spring为我们提供了一个AbstractRoutingDataSource,可以注入多个数据源(内部存储为一个map键值对,key为数据源的别名,value为数据源的实例),并且通过钩子函数determineCurrentLookupKey来选择要切换的数据源。通过这种方式,可以实现读写分离,以及数据库热切换。
实现过程
实现mysql的双主复制
修改application.yml,注入多个数据源
spring:
application:
name: bootDemo #应用名称
datasource:
# 主库数据源配置
master:
# 配置数据源类型
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3307/boot?serverTimezone=GMT%2B8
username: root
password: 123$%^
# 初始化,最小,最大连接数
initialSize: 3
minidle: 3
maxActive: 18
# 获取数据库连接等待的超时时间
maxWait: 5000
# 关闭失败重试
# connectionErrorRetryAttempts: 0
# breakAfterAcquireFailure: true
# 配置多久进行一次检测,检测需要关闭的空闲连接 单位毫秒
# timeBetweenEvictionRunsMillis: 60000
# validationQuery: SELECT 1 FROM dual
# 配置监控统计拦截的filters,去掉后,监控界面的sql无法统计
#filters: stat,wall,log4j
# 从库数据源配置
slave:
# 配置数据源类型
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3308/boot?serverTimezone=GMT%2B8
username: root
password: 123$%^
# 初始化,最小,最大连接数
initialSize: 3
minidle: 3
maxActive: 18
# 获取数据库连接等待的超时时间
maxWait: 5000
# 关闭失败重试
# connectionErrorRetryAttempts: 0
# breakAfterAcquireFailure: true
# 配置多久进行一次检测,检测需要关闭的空闲连接 单位毫秒
# timeBetweenEvictionRunsMillis: 60000
# validationQuery: SELECT 1 FROM dual
# 配置监控统计拦截的filters,去掉后,监控界面的sql无法统计
#filters: stat,wall,log4j
自定义一个AbstractRoutingDataSource的实现类
package com.zyu.boot.demo.utils.datasource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.Map;
/**
* 自定义混合数据源
*/
public class CustomRoutingDataSource extends AbstractRoutingDataSource {
/**
* 当前使用的数据源对应的key
*/
private String currentDataSourceKey;
/**
* 当前混合数据源持有的所有数据源集合
*/
private Map<Object, Object> datasources;
@Override
protected Object determineCurrentLookupKey() {
return currentDataSourceKey;
}
@Override
public void setDefaultTargetDataSource(Object defaultTargetDataSource) {
super.setDefaultTargetDataSource(defaultTargetDataSource);
datasources.forEach((key, dataSource) -> {
if(defaultTargetDataSource == dataSource){
this.currentDataSourceKey = (String) key;
}
});
}
@Override
public void setTargetDataSources(Map<Object, Object> targetDataSources) {
super.setTargetDataSources(targetDataSources);
datasources = targetDataSources;
}
/**
* 获取当前默认的数据源对应的key
*
* @return
*/
public String getCurrentDataSourceKey() {
return currentDataSourceKey;
}
/**
* 设置混合数据源使用的key
* @param key
*/
public void setCurrentDataSourceKey(String key){
this.currentDataSourceKey = key;
}
/**
* 获取当前混合数据源中的所有数据源
*
* @return
*/
public Map<Object, Object> getDataSources() {
return datasources;
}
}
定义数据源切换的监听类
package com.zyu.boot.demo.utils.datasource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
/**
* 混合数据源存活监听类
*/
public class RoutingDBActiveListener {
private CustomRoutingDataSource routingDataSource;
public RoutingDBActiveListener(CustomRoutingDataSource routingDataSource) {
this.routingDataSource = routingDataSource;
Thread checkDBThread = new Thread(new DBCheckRunnable());
checkDBThread.setDaemon(true);
checkDBThread.start();
}
/**
* 检测数据源有效性的监听类
*/
private class DBCheckRunnable implements Runnable {
final Logger logger = LoggerFactory.getLogger(DBCheckRunnable.class);
/**
* 返回数据源的状态,true表示正常,false表示异常
*
* @param dataSource
* @return
*/
private boolean checkDataSourceStatus(DataSource dataSource) {
try {
/**
* 正常情况下,执行该语句不会抛出异常
*/
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.execute("select 1");
return true;
} catch (SQLException e) {
logger.debug(e.getMessage());
return false;
}
}
@Override
public void run() {
logger.info("检测数据源守护线程已启动。。。");
while (true) {
try {
//获取当前混合数据源中的所有数据源
Map<Object, Object> dataSources = routingDataSource.getDataSources();
//循环检测混合数据源中的每个数据源的状态,并将数据源的状态记录在容器中
Map<String, Boolean> statusMap = new HashMap<>();
//遍历数据源,检查数据源的状态
dataSources.forEach((key, dataSource) -> {
statusMap.put(((String) key), checkDataSourceStatus(((DataSource) dataSource)));
});
//获取当前混合数据源中默认的数据源对应的key
String currentDataSourceKey = routingDataSource.getCurrentDataSourceKey();
//当前数据源状态为异常
if (currentDataSourceKey != null && !statusMap.get(currentDataSourceKey)) {
//找一个正常的数据源
String activeKey = null;
for (Map.Entry<String, Boolean> entry : statusMap.entrySet()) {
if (entry.getValue()) {
activeKey = entry.getKey();
break;
}
}
if (activeKey == null) {
logger.info("当前所有数据源都为不可用状态,请及时查看");
} else {
//切换数据源
routingDataSource.setCurrentDataSourceKey(activeKey);
logger.info("当前【" + currentDataSourceKey + "】不可用,已自动切换到【" + activeKey + "】");
}
}
Thread.sleep(1000);
} catch (Exception e) {
logger.error(e.getMessage());
}
}
}
}
}
修改数据源的配置类
package com.zyu.boot.demo.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.zyu.boot.demo.utils.datasource.CustomRoutingDataSource;
import com.zyu.boot.demo.utils.datasource.RoutingDBActiveListener;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
import java.util.HashMap;
@Configuration
@Order(2)
public class DataSourceConfig {
@Value("${mybatis.mapper-locations}")
private String mapperLocations;
/**
* 数据源1的配置
* @return
*/
@Bean(name = "db1")
@ConfigurationProperties(prefix = "spring.datasource.master")
public DataSource masterDataSource() {
return new DruidDataSource();
}
/**
* 数据源2的配置
* @return
*/
@Bean(name = "db2")
@ConfigurationProperties(prefix = "spring.datasource.slave")
public DataSource slaveDataSource() {
return new DruidDataSource();
}
/**
* 自定义混合数据源的配置,将上面两个数据源注入到混合数据源中
* @param master
* @param slave
* @return
*/
@Bean(name = "routingDataSource")
public DataSource routingDataSource(@Qualifier("db1") DataSource master,
@Qualifier("db2") DataSource slave) {
CustomRoutingDataSource routingDataSource = new CustomRoutingDataSource();
HashMap<Object, Object> datasources = new HashMap<>();
datasources.put("db1", master);
datasources.put("db2", slave);
routingDataSource.setTargetDataSources(datasources);
routingDataSource.setDefaultTargetDataSource(master);
return routingDataSource;
}
/**
* 数据源监听类的配置
* @param dataSource
* @return
*/
@Bean(name = "dbListener")
public RoutingDBActiveListener dbListener(@Qualifier("routingDataSource") DataSource dataSource) {
return new RoutingDBActiveListener(((CustomRoutingDataSource) dataSource));
}
/**
* mybatis的sqlSessionFactory配置
* @param dataSource
* @return
* @throws Exception
*/
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory cluster1SqlSessionFactory(@Qualifier("routingDataSource") DataSource dataSource)
throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
// 配置mapper文件位置
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocations));
return sqlSessionFactoryBean.getObject();
}
}
结束语
路漫漫其修远兮,诸君共勉