springboot多数据源,DruidDataSource多数据源切换,不重启项目,修改数据库名解决方案
springboot多数据源,DruidDataSource多数据源切换,不重启项目,修改数据库名解决方案
不要急于复制代码,先看清业务思路是否符合你要的,以下:
项目背景:
- Springboot项目
- 一个主数据源,数据库名称不变。
- 一个副数据源,数据库名称每天都在变,比如今天1号,是db_01; 明天2号,是db_02。
难点:
-
需要项目持续运行,不能切换数据源就重启项目。
-
副数据源在切换数据源时要考虑到还有sql在运行,切换数据源后要做防错。简而言之,就是要定时扫描一下之前的数据库是否存在没有操作完的数据做一下补救。
-
这部分根据实际项目而定,做好了这部分,实际上有关多数据源的事务一致性问题,你完全可以根据我上面的思路进行设计,而不是专注于一定要在代码层面实现多数据源操作的一致性,而是通过业务思路设计实现。
-
多数据源如何配置。
吐槽:
以前的写老项目程序员不知道怎么想的,一个月变一个数据库名称,一天变一个表。他是省事了,后期数据对接的人头大。
示例代码设计思路:
我项目的原始逻辑相当复杂,下面我们模拟一个业务需求来展开。
- 一个springboot项目,主数据源main_db,副数据源change_db_* ,没看错,它就是*。副数据源名称可变的。副数据源change_db_* 向主数据源main_db同步导入数据。
- 当调用接口1,我们查看主数据源main_db里的导入的数据;
- 当调用接口2,我们使用当前已连接的副数据源change_db_*插入数据;
- 当调用接口3,我们使用当前已连接的副数据源change_db_*向主数据源main_db里导入数据;
- 当调用接口4,我们切换副数据源change_db_202111为副数据源change_db_202112,当再调用接口3时数据源已经变了。
- 数据库如下:
代码实现
-
新建springboot项目通过idea或者官网生成springboot项目都可以,不再赘述。
目录结构如下
-
pom文件配置
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.2.RELEASE</version>
<relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>dume</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>dume</name>
<description>Multiple data sources</description>
<properties>
<java.version>1.8</java.version>
<mybatis.version>1.3.2</mybatis.version>
<log4j2.version>2.16.0</log4j2.version>
<druid.version>1.1.22</druid.version>
<fastjson.version>1.2.49</fastjson.version>
<druid.version>1.1.22</druid.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>${druid.version}</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.yml文件配置
#数据库配置
spring:
datasource1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/main_db?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
initialSize: 5
minIdle: 5
maxActive: 200
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: false
maxPoolPreparedStatementPerConnectionSize: 20
filters: stat,wall
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
useGlobalDataSourceStat: true
datasource2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/change_db_202111?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
initialSize: 5
minIdle: 5
maxActive: 200
maxWait: 60000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 'x'
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: false
maxPoolPreparedStatementPerConnectionSize: 20
filters: stat,wall
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
useGlobalDataSourceStat: true
#mybatis配置
mybatis:
type-aliases-package: com.example.dume.**.model
mapper-locations: classpath:/mapper1/*.xml,classpath:/mapper2/*.xml
configuration:
mapUnderscoreToCamelCase: true
#端口号
server:
port: 8186
servlet:
context-path: /dume
session:
timeout: 30m #30分钟,测试的话时间不能太短,否
4.数据源配置
4.1数据源1
package com.example.dume.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* @author dume
* @create 2021-12-15 14:41
**/
@Configuration
public class DataSource1Value {
@Value("${spring.datasource1.url}")
private String url;
@Value("${spring.datasource1.username}")
private String username;
@Value("${spring.datasource1.password}")
private String password;
@Value("${spring.datasource1.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource1.initialSize}")
private int initialSize;
@Value("${spring.datasource1.minIdle}")
private int minIdle;
@Value("${spring.datasource1.maxActive}")
private int maxActive;
@Value("${spring.datasource1.maxWait}")
private int maxWait;
@Value("${spring.datasource1.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource1.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource1.validationQuery}")
private String validationQuery;
@Value("${spring.datasource1.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource1.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource1.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource1.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource1.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource1.filters}")
private String filters;
@Value("${spring.datasource1.connectionProperties}")
private String connectionProperties;
@Value("${spring.datasource1.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
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 getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public int getInitialSize() {
return initialSize;
}
public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}
public int getMinIdle() {
return minIdle;
}
public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}
public int getMaxActive() {
return maxActive;
}
public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}
public int getMaxWait() {
return maxWait;
}
public void setMaxWait(int maxWait) {
this.maxWait = maxWait;
}
public int getTimeBetweenEvictionRunsMillis() {
return timeBetweenEvictionRunsMillis;
}
public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public int getMinEvictableIdleTimeMillis() {
return minEvictableIdleTimeMillis;
}
public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public String getValidationQuery() {
return validationQuery;
}
public void setValidationQuery(String validationQuery) {
this.validationQuery = validationQuery;
}
public boolean isTestWhileIdle() {
return testWhileIdle;
}
public void setTestWhileIdle(boolean testWhileIdle) {
this.testWhileIdle = testWhileIdle;
}
public boolean isTestOnBorrow() {
return testOnBorrow;
}
public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
public boolean isTestOnReturn() {
return testOnReturn;
}
public void setTestOnReturn(boolean testOnReturn) {
this.testOnReturn = testOnReturn;
}
public boolean isPoolPreparedStatements() {
return poolPreparedStatements;
}
public void setPoolPreparedStatements(boolean poolPreparedStatements) {
this.poolPreparedStatements = poolPreparedStatements;
}
public int getMaxPoolPreparedStatementPerConnectionSize() {
return maxPoolPreparedStatementPerConnectionSize;
}
public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public String getConnectionProperties() {
return connectionProperties;
}
public void setConnectionProperties(String connectionProperties) {
this.connectionProperties = connectionProperties;
}
public boolean isUseGlobalDataSourceStat() {
return useGlobalDataSourceStat;
}
public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
this.useGlobalDataSourceStat = useGlobalDataSourceStat;
}
@Primary
@Bean(name = "db1")
public DataSource masterDataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
4.2数据源1配置,注意主数据源使用 @Primary注解;使用@DependsOn注解解决循环依赖问题; sessionFactoryBean.setVfs(SpringBootVFS.class)这句代码解决加载springboot虚拟文件,防止jar包启动时找不到类;
package com.example.dume.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
/**
* @author dume
* @create 2021-12-14 16:27
**/
@Configuration
@MapperScan(basePackages = "com.example.dume.dataserver2.dao",sqlSessionTemplateRef="SqlSessionTemplate2")
public class DataSource2Config {
/**
* 创建session工厂
* @param dataSource2
* @return
* @throws Exception
* //解决数据库循环依赖问题
*/
@DependsOn({ "db2"})
@Bean(name="SqlSessionFactory2")
public SqlSessionFactory SqlSessionFactory2(@Qualifier("db2") DataSource dataSource2) throws Exception{
SqlSessionFactoryBean sessionFactoryBean=new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource2);
//加载springboot虚拟文件,防止jar包启动时找不到类
sessionFactoryBean.setVfs(SpringBootVFS.class);
// 设置mybatis的xml所在位置
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper2/*.xml"));
sessionFactoryBean.setTypeAliasesPackage("com.example.dume.dataserver2.model");
return sessionFactoryBean.getObject();
}
/**
* 创建SqlSession订单模板
* @param sessionFactory
* @return
*/
@Bean(name="SqlSessionTemplate2")
public SqlSessionTemplate SqlSessionTemplate2(@Qualifier("SqlSessionFactory2") SqlSessionFactory sessionFactory){
return new SqlSessionTemplate(sessionFactory);
}
/**
* 从数据源事务
*/
@Bean("TransactionManager2")
@DependsOn({ "db2"})
public PlatformTransactionManager platformTransactionManager(@Qualifier("db2") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
4.3数据源2
package com.example.dume.config;
import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* @author dume
* @create 2021-12-15 14:41
**/
@Configuration
public class DataSource2Value {
@Value("${spring.datasource2.url}")
private String url;
@Value("${spring.datasource2.username}")
private String username;
@Value("${spring.datasource2.password}")
private String password;
@Value("${spring.datasource2.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource2.initialSize}")
private int initialSize;
@Value("${spring.datasource2.minIdle}")
private int minIdle;
@Value("${spring.datasource2.maxActive}")
private int maxActive;
@Value("${spring.datasource2.maxWait}")
private int maxWait;
@Value("${spring.datasource2.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource2.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource2.validationQuery}")
private String validationQuery;
@Value("${spring.datasource2.testWhileIdle}")
private boolean testWhileIdle;
@Value("${spring.datasource2.testOnBorrow}")
private boolean testOnBorrow;
@Value("${spring.datasource2.testOnReturn}")
private boolean testOnReturn;
@Value("${spring.datasource2.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${spring.datasource2.maxPoolPreparedStatementPerConnectionSize}")
private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource2.filters}")
private String filters;
@Value("${spring.datasource2.connectionProperties}")
private String connectionProperties;
@Value("${spring.datasource2.useGlobalDataSourceStat}")
private boolean useGlobalDataSourceStat;
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 getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public int getInitialSize() {
return initialSize;
}
public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}
public int getMinIdle() {
return minIdle;
}
public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}
public int getMaxActive() {
return maxActive;
}
public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}
public int getMaxWait() {
return maxWait;
}
public void setMaxWait(int maxWait) {
this.maxWait = maxWait;
}
public int getTimeBetweenEvictionRunsMillis() {
return timeBetweenEvictionRunsMillis;
}
public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public int getMinEvictableIdleTimeMillis() {
return minEvictableIdleTimeMillis;
}
public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public String getValidationQuery() {
return validationQuery;
}
public void setValidationQuery(String validationQuery) {
this.validationQuery = validationQuery;
}
public boolean isTestWhileIdle() {
return testWhileIdle;
}
public void setTestWhileIdle(boolean testWhileIdle) {
this.testWhileIdle = testWhileIdle;
}
public boolean isTestOnBorrow() {
return testOnBorrow;
}
public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
public boolean isTestOnReturn() {
return testOnReturn;
}
public void setTestOnReturn(boolean testOnReturn) {
this.testOnReturn = testOnReturn;
}
public boolean isPoolPreparedStatements() {
return poolPreparedStatements;
}
public void setPoolPreparedStatements(boolean poolPreparedStatements) {
this.poolPreparedStatements = poolPreparedStatements;
}
public int getMaxPoolPreparedStatementPerConnectionSize() {
return maxPoolPreparedStatementPerConnectionSize;
}
public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public String getConnectionProperties() {
return connectionProperties;
}
public void setConnectionProperties(String connectionProperties) {
this.connectionProperties = connectionProperties;
}
public boolean isUseGlobalDataSourceStat() {
return useGlobalDataSourceStat;
}
public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
this.useGlobalDataSourceStat = useGlobalDataSourceStat;
}
@Bean(name = "db2")
public DataSource db2DataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
datasource.setDriverClassName(driverClassName);
datasource.setInitialSize(initialSize);
datasource.setMinIdle(minIdle);
datasource.setMaxActive(maxActive);
datasource.setMaxWait(maxWait);
datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
datasource.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
System.err.println("druid configuration initialization filter: " + e);
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
}
4.4数据源2配置
package com.example.dume.config;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
/**
* @author dume
* @create 2021-12-14 16:27
**/
@Configuration
@MapperScan(basePackages = "com.example.dume.dataserver2.dao",sqlSessionTemplateRef="SqlSessionTemplate2")
public class DataSource2Config {
/**
* 创建session工厂
* @param dataSource2
* @return
* @throws Exception
* //解决数据库循环依赖问题
*/
@DependsOn({ "db2"})
@Bean(name="SqlSessionFactory2")
public SqlSessionFactory SqlSessionFactory2(@Qualifier("db2") DataSource dataSource2) throws Exception{
SqlSessionFactoryBean sessionFactoryBean=new SqlSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource2);
//加载springboot虚拟文件,防止jar包启动时找不到类
sessionFactoryBean.setVfs(SpringBootVFS.class);
// 设置mybatis的xml所在位置
sessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper2/*.xml"));
sessionFactoryBean.setTypeAliasesPackage("com.example.dume.dataserver2.model");
return sessionFactoryBean.getObject();
}
/**
* 创建SqlSession订单模板
* @param sessionFactory
* @return
*/
@Bean(name="SqlSessionTemplate2")
public SqlSessionTemplate SqlSessionTemplate2(@Qualifier("SqlSessionFactory2") SqlSessionFactory sessionFactory){
return new SqlSessionTemplate(sessionFactory);
}
/**
* 从数据源事务
*/
@Bean("TransactionManager2")
@DependsOn({ "db2"})
public PlatformTransactionManager platformTransactionManager(@Qualifier("db2") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
5.SpringUtil工具类。实现ApplicationContextAware,在项目启动后使用ApplicationContext获取数据源bean。
package com.example.dume.util;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
/**
* @author dume
* @create 2021-12-24 17:23
**/
@Component
public class SpringUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext = null;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringUtil.applicationContext = applicationContext;
}
public static <T> T getBean(Class<T> cla) {
return applicationContext.getBean(cla);
}
/**
* 通过BeanName获取
* @param beanName
* @param <T>
* @return
*/
public static <T> T getBean(String beanName) {
return (T) applicationContext.getBean(beanName);
}
public static <T> T getBean(String name, Class<T> cal) {
return applicationContext.getBean(name, cal);
}
public static String getProperty(String key) {
return applicationContext.getBean(Environment.class).getProperty(key);
}
}
6.核心方法:
调用接口时切换数据源。启动时副数据源是change_db_202111,调用接口后副数据源切换为change_db_202112。
我演示用的是写死的,实际使用中可以把根据时间变化的数据源放在数据库或者配置文件中,来获取。
重点:
许多博客都写错了。我们并不能使用.close()方法直接关闭数据源,然后使用.restart()重新加载数据源配置,因为此时数据源往往已经调用过.init()方法,.isInited()为true,此时使用.setXX会报错。
正确的使用:
先判断.isInited()是否为true,如果为true,则调用.restart()方法,此时.isInited()为false,我们调用.setXX可以修改数据源属性,然后再调用.init()方法重新加载数据源。
@PostMapping("/interface4")
public JSONObject reSetDataSource() throws SQLException {
String urlnew2 = "jdbc:mysql://localhost:3306/change_db_202112?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8";
DruidDataSource datasource = SpringUtil.getBean("db2");
if(datasource.isInited()){
datasource.restart();
datasource.setUrl(urlnew2);
datasource.init();
logger.info("副数据源重置为:"+urlnew2);
}else{
datasource.setUrl(urlnew2);
datasource.init();
logger.info("副数据源重置为:"+urlnew2);
}
JSONObject object = new JSONObject();
object.put("message","副数据源重置成功!");
return object;
}
测试
1.查看主数据源数据
package com.example.dume.dataserver1.controller;
import com.example.dume.dataserver1.service.impl.MainTableServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* @author dume
* @create 2021-12-24 15:59
**/
@RestController
@RequestMapping("/data")
public class MainTableController {
@Autowired
private MainTableServiceImpl mainTableServiceImpl;
/**
* 查看主数据源数据
* @return
*/
@PostMapping("/interface1")
public List getMainTableList(){
return mainTableServiceImpl.selectList();
}
}
此时为空。
2.向当前副数据源插入数据。
/**
* 副数据库插入
* @return
*/
@PostMapping("/interface2")
public JSONObject insertChangeTableList(@RequestBody List<ChangeTable> changeTableList){
changeTableServiceImpl.insertList(changeTableList);
JSONObject object = new JSONObject();
object.put("message","副数据源插入成功!");
return object;
}
3.副数据库向主数据库插入数据
/**
* 副数据库向主数据库插入数据
*/
@PostMapping("/interface3")
public JSONObject getMainTableList(){
List<ChangeTable> changeTableList = changeTableServiceImpl.selectList();
if(!CollectionUtils.isEmpty(changeTableList)){
List<MainTable> mainTableList = JSONArray.parseArray(JSONArray.toJSONString(changeTableList),MainTable.class);
mainTableServiceImpl.insertList(mainTableList);
}
JSONObject object = new JSONObject();
object.put("message","主数据源插入成功!");
return object;
}
4.查看当前主数据源数据
5.调用接口切换副数据源change_db_202111→change_db_202112。
/**
* 切换副数据源
*/
@PostMapping("/interface4")
public JSONObject reSetDataSource() throws SQLException {
String urlnew2 = "jdbc:mysql://localhost:3306/change_db_202112?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8";
DruidDataSource datasource = SpringUtil.getBean("db2");
if(datasource.isInited()){
datasource.restart();
datasource.setUrl(urlnew2);
datasource.init();
logger.info("副数据源重置为:"+urlnew2);
}else{
datasource.setUrl(urlnew2);
datasource.init();
logger.info("副数据源重置为:"+urlnew2);
}
JSONObject object = new JSONObject();
object.put("message","副数据源重置成功!");
return object;
}
2022-01-04 11:08:48.335 INFO 10404 --- [nio-8186-exec-3] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
2022-01-04 11:13:57.766 INFO 10404 --- [nio-8186-exec-6] com.alibaba.druid.pool.DruidDataSource : {dataSource-2} inited
2022-01-04 11:17:23.478 INFO 10404 --- [io-8186-exec-10] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} restart
2022-01-04 11:17:23.478 INFO 10404 --- [io-8186-exec-10] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closing ...
2022-01-04 11:17:23.485 INFO 10404 --- [io-8186-exec-10] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} closed
2022-01-04 11:17:23.521 INFO 10404 --- [io-8186-exec-10] com.alibaba.druid.pool.DruidDataSource : {dataSource-3} inited
2022-01-04 11:17:23.521 INFO 10404 --- [io-8186-exec-10] c.e.d.d.c.ChangeTableController : 副数据源重置为:jdbc:mysql://localhost:3306/change_db_202112?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2B8
6.使用新副数据源插入数据。
6.新副数据库向主数据库插入数据
7.查看此时主数据源数据