1、近期项目中有一个需求:用户登录后要做导出功能,但是导出数据前输入另一个数据库的连接信息,从这个数据源导出。网上搜了一些方案,有些是多配置几个数据源,切换过去,但是这个明显不符合这个场景,如果我要新增一个新的数据源是不是要改配置文件,重启项目呢?在网上搜到了一个解决方案,博客地址:https://blog.csdn.net/cs373616511/article/details/82827635
2、这个项目我下载下来运行是可以的,但是在我的项目中也是按照这样的方式是不行的,后来摸了两天终于摸了出来,接下来一步步说明。
3、依赖文件
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.csstj</groupId>
<artifactId>srm</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>srm</name>
<description>interface_zkhz</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--aop-->
<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>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.45</version>
</dependency>
<!-- 阿里连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.2</version>
</dependency>
<!-- 日志 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.7</source>
<target>1.7</target>
</configuration>
</plugin>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<!--配置文件的位置-->
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<executions>
<execution>
<id>Generate MyBatis Artifacts</id>
<goals>
<goal>generate</goal>
</goals>
</execution>
</executions>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.2</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
4、部分配置文件
datasource2:
url: jdbc:mysql://xxxxxx
username: xxxxxx
password: xxxxxx
driver-class-name: com.mysql.jdbc.Driver
filters: mergeStat,wall,logback
initial-size: 5
max-active: 50
min-idle: 5
max-wait: 6000
validation-query: SELECT 'x' FROM DUAL
test-on-borrow: true
test-on-return: false
test-while-idle: true
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
remove-abandoned: true
remove-abandoned-timeout: 1800
log-abandoned: true
#PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
pool-prepared-statements: false
#要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。
# max-open-prepared-statements: 100
5、springboot启动类
public static void main(String[] args) {
ConfigurableApplicationContext applicationContext = SpringApplication.run(App.class, args);
System.out.println("springboot启动成功");
SpringContextUtil.setApplicationContext(applicationContext);
DataSourceUtil.initDataSource();
}
6、SpringContextUtil类
package com.kindo.monitor.dynamic1;
import org.springframework.context.ApplicationContext;
public class SpringContextUtil {
private static ApplicationContext applicationContext;
//获取上下文
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
//设置上下文
public static void setApplicationContext(ApplicationContext applicationContext) {
SpringContextUtil.applicationContext = applicationContext;
}
//通过名字获取上下文中的bean
public static Object getBean(String name) {
return applicationContext.getBean(name);
}
//通过类型获取上下文中的bean
public static Object getBean(Class<?> requiredType) {
return applicationContext.getBean(requiredType);
}
}
7、MyDynamicDataSource类
package com.kindo.monitor.dynamic1;
import com.alibaba.druid.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class MyDynamicDataSource extends AbstractRoutingDataSource {
private final Logger logger = LoggerFactory.getLogger(MyDynamicDataSource.class);
@Override
public Object determineCurrentLookupKey() {
//获取当前线程的数据源,如果不存在使用master数据源
String datasource = DBContextHolder.getDataSource();
if (StringUtils.isEmpty(datasource)) {
datasource = "default";
}
logger.info("datasource=" + datasource);
return datasource;
}
}
8、DBContextHolder类
package com.kindo.monitor.dynamic1;
public class DBContextHolder {
// 对当前线程的操作-线程安全的
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
// 调用此方法,切换数据源
public static void setDataSource(String dataSource) {
if (DataSourceUtil.dataSourceMap.containsKey(dataSource)) {
contextHolder.set(dataSource);
} else {
throw new RuntimeException("数据源:" + dataSource + "不存在");
}
}
// 获取数据源
public static String getDataSource() {
return contextHolder.get();
}
// 删除数据源
public static void clearDataSource() {
contextHolder.remove();
}
}
9、DataSourceUtil类
package com.kindo.monitor.dynamic1;
import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.HashMap;
import java.util.Map;
public class DataSourceUtil {
private static final Logger logger = LoggerFactory.getLogger(DataSourceUtil.class);
public static final Map<Object, Object> dataSourceMap = new HashMap<>();
public static void initDataSource() {
//获取masterDataSource
DruidDataSource masterDataSource = (DruidDataSource) SpringContextUtil.getBean("masterDataSource");
addDataSource("default", masterDataSource);
//初始化其它数据源
// initOthersDataSource();
//刷新数据源
flushDataSource();
}
public static void flushDataSource() {
//获取spring管理的dynamicDataSource
MyDynamicDataSource myDynamicDataSource = (MyDynamicDataSource) SpringContextUtil.getBean("dynamicDataSource");
//将数据源设置到 targetDataSources
myDynamicDataSource.setTargetDataSources(dataSourceMap);
//将 targetDataSources 中的连接信息放入 resolvedDataSources 管理
myDynamicDataSource.afterPropertiesSet();
}
public static void addDataSource(String key, DruidDataSource masterDataSource) {
dataSourceMap.put(key, masterDataSource);
}
private static void initOthersDataSource() {
//在此处可以查询出所有的数据源(例如,配置文件,数据库)然后添加
String key = "slave";
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername("root");
druidDataSource.setPassword("root");
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("jdbc:mysql://localhost:3306/test");
//添加数据源到map
addDataSource(key, druidDataSource);
}
}
10、Config类
package com.kindo.monitor.dynamic1;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.transaction.SpringManagedTransaction;
import org.slf4j.Logger;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
import org.mybatis.spring.transaction.*;
@Configuration
public class Config {
private final Logger logger = org.slf4j.LoggerFactory.getLogger(Config.class);
/**
* @return
* @Bean 防止数据监控报错,无法查看数据源
* @ConfigurationProperties 会把配置文件的参数自动赋值到dataSource里。
* @Primary 用于标识默认使用的 DataSource Bean
*/
@Bean(destroyMethod = "close", initMethod = "init", name = "masterDataSource")
@ConfigurationProperties(prefix = "datasource2")
@Primary
public DataSource masterDataSource() {
logger.info("创建masterDataSource");
//DruidDataSource druidDataSource = new DruidDataSource();
DruidDataSource druidDataSource = DruidDataSourceBuilder.create().build();
return druidDataSource;
}
@Bean(name = "dynamicDataSource")
public DataSource dynamicDataSource() {
MyDynamicDataSource myDynamicDataSource = new MyDynamicDataSource();
// 配置多数据源
Map<Object, Object> targetDataSources = new HashMap<>();
targetDataSources.put("default", masterDataSource());
//targetDataSources.put("slave", slaveDataSource());
myDynamicDataSource.setTargetDataSources(targetDataSources);
//设置默认数据源,在动态添加数据源的时候,就可以不再添加此数据源了
// myDynamicDataSource.setDefaultTargetDataSource(masterDataSource());
//dynamicDataSource.setDefaultTargetDataSource(slaveDataSource());
return myDynamicDataSource;
}
/**
* 配置 SqlSessionFactoryBean
*/
@Bean(value = "sqlSessionFactoryBean222")
public SqlSessionFactory sqlSessionFactoryBean() throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
// 配置数据源,此处配置为关键配置,如果没有将 dynamicDataSource 作为数据源则不能实现切换
sqlSessionFactoryBean.setDataSource(dynamicDataSource());
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dynamicDataSource());
return bean.getObject();
}
/**
* 注入 DataSourceTransactionManager 用于事务管理
*/
@Bean
public PlatformTransactionManager transactionManager() {
return new DataSourceTransactionManager(dynamicDataSource());
}
11、创建数据库表,在两个不同的数据库中创建相同的表结构
CREATE TABLE `auto_user` (
`id` int(6) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
分别创建一条不同的记录
12、实体类AutoUser
package com.kindo.monitor.dynamic1;
import lombok.Data;
/**
* @author liangliang
* @date 2021/4/28 11:31
*/
@Data
public class AutoUser {
private Integer id;
private String name;
private String phone;
}
13、业务代码
public Map<String, List<AutoUser>> queryLit() {
Map<String, List<AutoUser>> map = new HashMap<>();
try {
List<AutoUser> autoUsers = planMapper.queryList();
map.put("默认数据源查询", autoUsers);
// 切换数据源
String key = "zjjx";
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUsername("xxx");
druidDataSource.setPassword("xxx");
druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
druidDataSource.setUrl("xxx");
//添加数据源到map
DataSourceUtil.addDataSource(key, druidDataSource);
//刷新
DataSourceUtil.flushDataSource();
// 切换数据源后查询
DBContextHolder.setDataSource("zjjx");
List<AutoUser> autoUsers2 = planMapper.queryList();
map.put("切换数据源查询", autoUsers2);
// 清除动态数据源使用默认数据源
DBContextHolder.clearDataSource();
List<AutoUser> autoUsers3 = planMapper.queryList();
map.put("清空动态数据源后查询", autoUsers3);
} catch (Exception e) {
e.printStackTrace();
} finally {
DBContextHolder.clearDataSource();
}
return map;
}
运行后没有出现我想要的结果,后来一步步debug进入到数据库连接发现数据源还是配置文件里面默认的。仔细找了一下问题,发现公司自己加在配置的时候数据源默认就是从配置文件取的
因此我们要把这里数据源换成动态数据源也就是Config文件里面的
@Bean(“dynamicDataSource”)
然后运行的时候就是正确的了:
{
"切换数据源查询": [
{
"id": 2,
"name": "动态数据库",
"phone": "222222"
}
],
"清空动态数据源后查询": [
{
"id": 1,
"name": "默认数据库",
"phone": "11111111"
}
],
"默认数据源查询": [
{
"id": 1,
"name": "默认数据库",
"phone": "11111111"
}
]
}