原来写过一篇关于SSM多数据源配置的博客,为什么今天又要写一篇呢?当然是因为需求的变更(蹭访问量),原来的博客中,多个数据源是配置在xml文件中的,每一个数据源都对应了一个会话管理器dataSource,这样就把数据源的数量给订死了,你有几个会话管理器就有几个数据源,不太方便,所以这次想达到一个动态添加删除数据源的效果.
原来的博文请见 MyBatis 配置多数据源实现多个数据库动态切换_mybitias 多库切换-CSDN博客 写的反正也不咋地
1.创建存放数据源信息的实体类
/**
* 数据源实体类
*/
public class SourceEntity {
private String url;
private String username;
private String password;
private String driver;
private String type;
private String beanKey;
public SourceEntity(String type,String driver,String url, String username, String password,String beanKey ) {
this.url = url;
this.username = username;
this.password = password;
this.driver = driver;
this.type = type;
this.beanKey = beanKey;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getBeanKey() {
return beanKey;
}
public void setBeanKey(String beanKey) {
this.beanKey = beanKey;
}
}
2.创建 CustomerContextHolder ,用于切换数据源信息和清除数据源信息
public class CustomerContextHolder {
public static final String DATASOURCE_DEFAULT = "dataSource";
public static final String DATASOURCE_REGION = "dataSourceRegion";
private static ThreadLocal<String> contentHolder = new ThreadLocal<>();
public static String getCustomerType() {
return contentHolder.get();
}
public static void setCustomerType(String customerType) {
contentHolder.set(customerType);
}
public static void clearCustomerType() {
contentHolder.remove();
}
}
3.在mybatis.xml 文件中添加下面的配置 ,dataSource 为你的主数据库,主数据库还是使用xml配置的方式
<!--动态数据源配置-->
<bean id="dynamicDataSource" class="com.mlkj.common.config.dataSourceConfig.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<!--现有数据源-->
<entry value-ref="dataSource" key="dataSource"/>
</map>
</property>
<!--默认数据源-->
<property name="defaultTargetDataSource" ref="dataSource"/>
</bean>
4.在 sqlSessionFactory 的 dataSource中引入 3 中的数据源配置
<!-- MyBatis begin -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource"/>
<property name="typeAliasesPackage" value="com.mlkj"/>
<property name="typeAliasesSuperType" value="com.mlkj.common.persistence.BaseEntity"/>
<property name="mapperLocations" value="classpath:/mappings/**/*.xml"/>
<property name="configLocation" value="classpath:/mybatis-config.xml"></property>
</bean>
5 .创建 DynamicDataSource 继承 AbstractRoutingDataSource 多数据源支持类,重写 以下方法,当数据在请求数据库之前,会调用 determineCurrentLookupKey 方法获取到要使用的数据源标志,如果没有拿到,则使用默认数据源
public class DynamicDataSource extends AbstractRoutingDataSource {
@Autowired
private ApplicationContext applicationContext;
@Lazy
@Autowired
private DynamicDataSourceSummoner summoner;
@Override
protected String determineCurrentLookupKey() {
System.out.println("当前线程:"+Thread.currentThread().getName()+"正在使用的数据源"+CustomerContextHolder.getCustomerType());
return com.mlkj.common.utils.StringUtils.isNotBlank(CustomerContextHolder.getCustomerType())?CustomerContextHolder.getCustomerType():CustomerContextHolder.DATASOURCE_DEFAULT;
}
@Override
protected DataSource determineTargetDataSource() {
String beanKey = CustomerContextHolder.getCustomerType();
//获取当前线程中所使用的数据源标志,然后去 applicationContext 中查看是否有对应的bean
if (!StringUtils.hasText(beanKey) || applicationContext.containsBean(beanKey)) {
return super.determineTargetDataSource();
}else{
//如果没有对应的bean,则初始化
summoner.registerDynamicDataSources();
}
return super.determineTargetDataSource();
}
}
4. 创建 DynamicDataSourceSummoner 实现ApplicationListener<ContextRefreshedEvent>类,会在Spring 加载完成后执行,这时候我们将会话管理器注册到bean中,Global.getConfig类为读取配置文件类,你们的可能不一样
@Slf4j
@Component
public class DynamicDataSourceSummoner implements ApplicationListener<ContextRefreshedEvent> {
@Autowired
private ConfigurableApplicationContext applicationContext;
@Autowired
private DynamicDataSource dynamicDataSource;
private static boolean loaded = false;
/**
* Spring加载完成后执行
*/
@Override
public void onApplicationEvent(ContextRefreshedEvent event) {
// 防止重复执行
if (!loaded) {
loaded = true;
try {
registerDynamicDataSources();
} catch (Exception e) {
e.printStackTrace();
System.out.println("数据源初始化失败, Exception:" + e.getMessage());
}
}
}
/**
* 从数据库读取租户的DB配置,并动态注入Spring容器
*/
public void registerDynamicDataSources() {
// 把数据源bean注册到容器中
List<SourceEntity> entities = Lists.newArrayList();
//由于这里只有一个数据源,所以就直接从配置文件中读取了,以后如果有多个可以改成从主数据库中读取
SourceEntity entity = new SourceEntity(Global.getConfig("jdbc.typeRegion"),
Global.getConfig("jdbc.driverRegion"), Global.getConfig("jdbc.urlRegion"),
Global.getConfig("jdbc.usernameRegion"), Global.getConfig("jdbc.passwordRegion"), CustomerContextHolder.DATASOURCE_REGION);
entities.add(entity);
addDataSourceBeans(entities);
}
/**
* 根据DataSource创建bean并注册到容器中
*/
private void addDataSourceBeans(List<SourceEntity> sourceEntities) {
Map<Object, Object> targetDataSources = Maps.newLinkedHashMap();
BeanDefinitionRegistry beanFactory = (BeanDefinitionRegistry) applicationContext.getBeanFactory();
for (SourceEntity entity : sourceEntities) {
// 如果该数据源已经在spring里面注册过,则不重新注册
if (applicationContext.containsBean(entity.getBeanKey())) {
DruidDataSource existsDataSource = applicationContext.getBean(entity.getBeanKey(), DruidDataSource.class);
if (isSameDataSource(existsDataSource, entity)) {
continue;
}
}
// 组装bean
AbstractBeanDefinition beanDefinition = getBeanDefinition(entity);
// 注册bean
beanFactory.registerBeanDefinition(entity.getBeanKey(), beanDefinition);
// 放入map中,注意一定是刚才创建bean对象
DruidDataSource bean = null;
try {
bean = applicationContext.getBean(entity.getBeanKey(), DruidDataSource.class);
targetDataSources.put(entity.getBeanKey(), bean);
CacheUtils.put(RealDataUtils.IS_SOURCE_BREAK, false);
} catch (Exception e) {
e.printStackTrace();
//如果报错,删除注册的bean
beanFactory.removeBeanDefinition(entity.getBeanKey());
try {
Boolean isSourceBreak = (Boolean) CacheUtils.get(RealDataUtils.IS_SOURCE_BREAK);
if (isSourceBreak == null || !isSourceBreak) {
RealDataUtils.sendMessage("区域库", e.getMessage(), new Date());
CacheUtils.put(RealDataUtils.IS_SOURCE_BREAK, true);
}
} catch (Exception ex) {
ex.printStackTrace();
}
throw e;
}
}
// 将创建的map对象set到 targetDataSources;
dynamicDataSource.setTargetDataSources(targetDataSources);
// 必须执行此操作,才会重新初始化AbstractRoutingDataSource 中的 resolvedDataSources,也只有这样,动态切换才会起效
dynamicDataSource.afterPropertiesSet();
}
/**
* 组装数据源spring bean
*/
private AbstractBeanDefinition getBeanDefinition(SourceEntity entity) {
BeanDefinitionBuilder builder = BeanDefinitionBuilder.genericBeanDefinition(DruidDataSource.class);
builder.getBeanDefinition().setAttribute("id", entity.getBeanKey());
// 其他配置继承defaultDataSource
//builder.setParentName(CustomerContextHolder.DATASOURCE_REGION);
builder.setInitMethodName("init");
builder.setDestroyMethodName("close");
builder.addPropertyValue("name", entity.getBeanKey());
builder.addPropertyValue("url", entity.getUrl());
builder.addPropertyValue("username", entity.getUsername());
builder.addPropertyValue("password", entity.getPassword());
//配置初始化大小、最小、最大
builder.addPropertyValue("initialSize", Global.getConfig("jdbc.pool.init"));
builder.addPropertyValue("minIdle", Global.getConfig("jdbc.pool.minIdle"));
builder.addPropertyValue("maxActive", Global.getConfig("jdbc.pool.maxActive"));
//配置获取连接等待超时的时间
builder.addPropertyValue("maxWait", "60000");
// 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
builder.addPropertyValue("timeBetweenEvictionRunsMillis", "60000");
//连接重试时间
builder.addPropertyValue("timeBetweenConnectErrorMillis", "5000");
//配置一个连接在池中最小生存的时间,单位是毫秒
builder.addPropertyValue("minEvictableIdleTimeMillis", "300000");
builder.addPropertyValue("validationQuery", Global.getConfig("jdbc.testSql"));
builder.addPropertyValue("testWhileIdle", "true");
builder.addPropertyValue("testOnBorrow", "false");
builder.addPropertyValue("testOnReturn", "false");
//数据库连接错误时关闭连接
/*builder.addPropertyValue("breakAfterAcquireFailure", "true");
builder.addPropertyValue("connectionErrorRetryAttempts", "0");*/
//打开PSCache,并且指定每个连接上PSCache的大小(Oracle使用)
//builder.addPropertyValue("poolPreparedStatements", "true");
//builder.addPropertyValue("maxPoolPreparedStatementPerConnectionSize", "20");
//以下配置建议在调试时使用,线上会影响性能
//超过时间限制是否回收
//builder.addPropertyValue("removeAbandoned", "true");
//超时时间;单位为秒。180秒=3分钟
//builder.addPropertyValue("removeAbandonedTimeout", "180");
//关闭abanded连接时输出错误日志
builder.addPropertyValue("logAbandoned", "true");
//配置监控统计拦截的filters
builder.addPropertyValue("filters", "stat");
//builder.addPropertyValue("connectionProperties", DataSourceUtil.getConnectionProperties(entity.getDbPublicKey()));
return builder.getBeanDefinition();
}
/**
* 判断Spring容器里面的DataSource与数据库的DataSource信息是否一致
* 备注:这里没有判断public_key,因为另外三个信息基本可以确定唯一了
*/
private boolean isSameDataSource(DruidDataSource existsDataSource, SourceEntity entity) {
boolean sameUrl = Objects.equals(existsDataSource.getUrl(), entity.getUrl());
if (!sameUrl) {
return false;
}
boolean sameUser = Objects.equals(existsDataSource.getUsername(), entity.getUsername());
if (!sameUser) {
return false;
}
try {
//String decryptPassword = ConfigTools.decrypt(entity.getDbPublicKey(), entity.getDbPassword());
return Objects.equals(existsDataSource.getPassword(), entity.getPassword());
} catch (Exception e) {
e.printStackTrace();
System.out.println("数据源密码校验失败,Exception:{}" + e.getMessage());
return false;
}
}
5 完整的mybatis.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd"
default-lazy-init="true">
<!-- MyBatis begin -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dynamicDataSource"/>
<property name="typeAliasesPackage" value="com.mlkj"/>
<property name="typeAliasesSuperType" value="com.mlkj.common.persistence.BaseEntity"/>
<property name="mapperLocations" value="classpath:/mappings/**/*.xml"/>
<property name="configLocation" value="classpath:/mybatis-config.xml"></property>
</bean>
<!-- 扫描basePackage下所有以@MyBatisDao注解的接口 -->
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
<property name="basePackage" value="com.mlkj"/>
<property name="annotationClass" value="com.mlkj.common.persistence.annotation.MyBatisDao"/>
</bean>
<!-- 定义事务 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource" />
</bean>
<!-- 配置 Annotation 驱动,扫描@Transactional注解的类定义事务 -->
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
<!-- MyBatis end -->
<!-- 数据源配置, 使用 BoneCP 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass -->
<property name="driverClassName" value="${jdbc.driver}" />
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${jdbc.pool.init}" />
<property name="minIdle" value="${jdbc.pool.minIdle}" />
<property name="maxActive" value="${jdbc.pool.maxActive}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="${jdbc.testSql}" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小(Oracle使用)
<property name="poolPreparedStatements" value="true" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="20" /> -->
<!--以下配置建议在调试时使用,线上会影响性能-->
<!-- 超过时间限制是否回收
<property name="removeAbandoned" value="true" /> -->
<!-- 超时时间;单位为秒。180秒=3分钟
<property name="removeAbandonedTimeout" value="180" /> -->
<!-- 关闭abanded连接时输出错误日志
<property name="logAbandoned" value="true" /> -->
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="stat" />
</bean>
<!--动态数据源配置-->
<bean id="dynamicDataSource" class="com.mlkj.common.config.dataSourceConfig.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<!--现有数据源-->
<entry value-ref="dataSource" key="dataSource"/>
</map>
</property>
<!--默认数据源-->
<property name="defaultTargetDataSource" ref="dataSource"/>
</bean>
</beans>
当我们需要切换数据源的时候,只需要在连接数据库之前调用 CustomerContextHolder.setCustomerType(CustomerContextHolder.DATASOURCE_REGION); 就可以了, CustomerContextHolder.DATASOURCE_REGION为你对应的数据源信息,数据库查询完成后在调用CustomerContextHolder.clearCustomerType(); 就可以切换会默认数据源
例子:
//切换数据源
CustomerContextHolder.setCustomerType(CustomerContextHolder.DATASOURCE_REGION);
syncDataDao.findList(new SyncTable())
//切回到默认数据源
CustomerContextHolder.clearCustomerType();