当前位置： 首页 > ds >正文

多数据源动态切换

ds 2025/5/4 6:43:11
核心思路

通过自定义实现官方的AbstractJdbcDataSourceProvider类，自定义数据源的加载逻辑，本文是从数据库中查询数据源的信息

通过官方的DynamicDataSourceContextHolder类在代码中进行数据源的切换

通过官方的DynamicRoutingDataSource类在代码中动态注册数据源和注销数据源

POM依赖
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-3-starter --><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-3-starter</artifactId><version>1.2.23</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-spring-boot3-starter</artifactId><version>3.5.7</version></dependency><!-- https://mvnrepository.com/artifact/com.github.yulichang/mybatis-plus-join-boot-starter --><dependency><groupId>com.github.yulichang</groupId><artifactId>mybatis-plus-join-boot-starter</artifactId><version>1.4.13</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot3-starter</artifactId><version>4.3.0</version></dependency></dependencies>
配置文件
spring:datasource:driver-class-name: org.postgresql.Driver#pg地址url: jdbc:postgresql://creatar-postgres:5432/creatar?currentSchema=public&oracle_compile=true&clob_as_string=true&binaryTransfer=false&loadBalanceHosts=true&targetServerType=primary&stringtype=unspecifiedusername: creatarpassword: Creatar_Yyds@9436type: com.zaxxer.hikari.HikariDataSourcehikari:connection-timeout: 30000          # 连接超时（毫秒）maximum-pool-size: 20              # 最大连接池大小minimum-idle: 5                    # 最小空闲连接数idle-timeout: 300000               # 空闲连接超时时间（5分钟）max-lifetime: 900000               # 连接最大生命周期（15分钟）validation-timeout: 5000           # 验证连接超时时间（毫秒）leak-detection-threshold: 2000     # 漏连接检测阈值（毫秒）pool-name: HikariPool              # 连接池名称auto-commit: true                  # 自动提交connection-test-query: SELECT 1    # 连接验证查询dynamic:#用于解决启动时出现dynamic-datasource can not find primary datasourceprimary: masterdatasource:master:driver-class-name: org.postgresql.Driver#pg地址url: jdbc:postgresql://creatar-postgres:5432/creatar?currentSchema=public&oracle_compile=true&clob_as_string=true&binaryTransfer=false&loadBalanceHosts=true&targetServerType=primary&stringtype=unspecifiedusername: creatarpassword: Creatar_Yyds@9436type: com.zaxxer.hikari.HikariDataSource
数据源创建工具

用于在创建数据源时使用连接池

package com.creatar.util;import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.creator.hikaricp.HikariCpConfig;
import com.creatar.common.Assert;
import com.creatar.entitys.dict.dynamic.DatasourceType;
import com.creatar.exception.CustomException;
import com.creatar.exception.enums.DynamicTableExceptionEnum;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;import javax.sql.DataSource;
import java.sql.Connection;/*** 数据源工具** @author: creatar* @date: 2025-04-02 14:28* @description: 数据源工具*/
@Slf4j
public class DatasourceUtil {private DatasourceUtil() {}/*** 根据自定义数据源配置构造官方数据源配置类** @param datasourceProperty 自定义数据源配置* @return 官方数据源配置*/public static DataSourceProperty getHikariDataSourceProperty(MyDatasourceProperty datasourceProperty) {DataSourceProperty dataSourceProperty = new DataSourceProperty();dataSourceProperty.setUrl(datasourceProperty.jdbc);dataSourceProperty.setUsername(datasourceProperty.getConnectionUsername());dataSourceProperty.setPassword(datasourceProperty.getConnectionPassword());DatasourceType datasourceType = datasourceProperty.getType();String driverClassName = getDriverClassName(datasourceType);dataSourceProperty.setDriverClassName(driverClassName);HikariCpConfig hikariCpConfig = getHikariCpConfig();dataSourceProperty.setHikari(hikariCpConfig);dataSourceProperty.setLazy(true);dataSourceProperty.setType(HikariDataSource.class);return dataSourceProperty;}/*** 从DataSourceProperties构造DatasourceProperty*/public static DataSourceProperty getDatasourcePropertyFromProperties(DataSourceProperties dataSourceProperties) {DataSourceProperty property = new DataSourceProperty();HikariCpConfig hikariCpConfig = getHikariCpConfig();property.setDriverClassName(dataSourceProperties.getDriverClassName());property.setUrl(dataSourceProperties.getUrl());property.setUsername(dataSourceProperties.getUsername());property.setPassword(dataSourceProperties.getPassword());property.setHikari(hikariCpConfig);return property;}/*** 构造默认hikariCp配置*/private static HikariCpConfig getHikariCpConfig() {HikariCpConfig hikariConfig = new HikariCpConfig();//配置Hikari连接池hikariConfig.setIsAutoCommit(true);//自动提交hikariConfig.setConnectionTestQuery("select 1");//连接查询语句设置hikariConfig.setConnectionTimeout(3000L);//连接超时时间设置hikariConfig.setIdleTimeout(300000L);//连接空闲生命周期设置hikariConfig.setIsIsolateInternalQueries(false);hikariConfig.setMaximumPoolSize(20);//连接池允许的最大连接数量hikariConfig.setMaxLifetime(300000L);//检查空余连接优化连接池设置时间,单位毫秒hikariConfig.setMinimumIdle(10);//连接池保持最小空余连接数量return hikariConfig;}/*** 构造默认hikari配置*/private static HikariConfig getHikariConfig() {HikariConfig hikariConfig = new HikariConfig();//配置Hikari连接池hikariConfig.setAutoCommit(true);//自动提交hikariConfig.setConnectionTestQuery("select 1");//连接查询语句设置hikariConfig.setConnectionTimeout(3000L);//连接超时时间设置hikariConfig.setIdleTimeout(300000L);//连接空闲生命周期设置hikariConfig.setIsolateInternalQueries(true);hikariConfig.setMaximumPoolSize(20);//连接池允许的最大连接数量hikariConfig.setMaxLifetime(300000L);//检查空余连接优化连接池设置时间,单位毫秒hikariConfig.setMinimumIdle(10);//连接池保持最小空余连接数量return hikariConfig;}/*** 自定义数据源配置构造hikari配置** @param datasourceProperty 自定义数据源配置* @return hikari配置实例*/public static HikariConfig getHikariConfig(MyDatasourceProperty datasourceProperty) {HikariConfig hikariConfig = getHikariConfig();DatasourceType datasourceType = datasourceProperty.getType();String driverClassName = getDriverClassName(datasourceType);hikariConfig.setDriverClassName(driverClassName);hikariConfig.setJdbcUrl(datasourceProperty.getJdbc());hikariConfig.setUsername(datasourceProperty.getConnectionUsername());hikariConfig.setPassword(datasourceProperty.getConnectionPassword());return hikariConfig;}/*** 获取数据源** @param datasourceProperty 自定义数据源配置* @param testConnection     是否测试连接，true：测试；false：不测试* @return 返回数据源实例*/public static DataSource getDataSource(MyDatasourceProperty datasourceProperty, boolean testConnection) {HikariConfig hikariCpConfig = DatasourceUtil.getHikariConfig(datasourceProperty);DataSource dataSource = null;if (testConnection) {//数据源连接测试try {dataSource = new HikariDataSource(hikariCpConfig);Connection connection = dataSource.getConnection();Assert.nonNull(connection, DynamicTableExceptionEnum.DATA_SOURCE_CONNECTION_ERROR);log.info("数据源连接成功,数据源配置:{}", datasourceProperty);} catch (Exception e) {log.info("数据源连接失败,配置信息:{},失败详情:", datasourceProperty, e);throw new CustomException(DynamicTableExceptionEnum.DATA_SOURCE_CONNECTION_ERROR);}}return dataSource;}/*** 根据数据源类型获取驱动类名称** @param datasourceType 数据源类型* @return 驱动类名称*/private static String getDriverClassName(DatasourceType datasourceType) {switch (datasourceType) {case POSTGRES_SOURCE -> {return "org.postgresql.Driver";}case MYSQL_SOURCE -> {return "com.mysql.cj.jdbc.Driver";}case ORACLE_SOURCE -> {return "oracle.jdbc.OracleDriver";}default -> throw new CustomException(DynamicTableExceptionEnum.DATA_SOURCE_TYPE_NOT_NULL);}}@Data@AllArgsConstructor@NoArgsConstructor@Builder@Accessors(chain = true)public static class MyDatasourceProperty {/*** jdbc连接url*/private String jdbc;/*** 连接用户名*/private String connectionUsername;/*** 连接密码*/private String connectionPassword;/*** 数据源类型*/private DatasourceType type;}
}
自定义多数据源加载接口
package com.creatar.service.common.dynamic.datasource;import cn.hutool.core.text.CharSequenceUtil;
import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;
import com.baomidou.dynamic.datasource.provider.AbstractJdbcDataSourceProvider;
import com.creatar.entitys.po.BizDatasourceEntity;
import com.creatar.util.DatasourceUtil;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.SneakyThrows;
import org.springframework.beans.BeanUtils;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.stereotype.Component;import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** 自定义数据源加载接口** @author: 张定辉* @date: 2025-04-02 11:29* @description: 自定义数据源加载接口*/
@Component
public class CustomDynamicDataSourceProvider extends AbstractJdbcDataSourceProvider {private final DataSourceProperties dataSourceProperty;public CustomDynamicDataSourceProvider(DefaultDataSourceCreator defaultDataSourceCreator, DataSourceProperties dataSourceProperty) {super(defaultDataSourceCreator, dataSourceProperty.getUrl(), dataSourceProperty.getUsername(), dataSourceProperty.getPassword());this.dataSourceProperty = dataSourceProperty;}@SneakyThrows@Overrideprotected Map<String, DataSourceProperty> executeStmt(Statement statement) throws SQLException {Map<String, DataSourceProperty> result = new HashMap<>();//查询数据源表构建数据源String sql = """select id,name,url,jdbc,port,connection_username,connection_password,connection_secret_file_id,type,status,description,create_time,update_time,create_by,update_byfrom biz_datasourcewhere status='1'""";ResultSet resultSet = statement.executeQuery(sql);ResultSetMetaData metaData = resultSet.getMetaData();int columnCount = metaData.getColumnCount();List<Map<String, Object>> list = new ArrayList<>();while (resultSet.next()) {Map<String, Object> rowMap = new HashMap<>();for (int i = 1; i <= columnCount; i++) {String columnName = metaData.getColumnName(i);Object columnValue = resultSet.getObject(i); // 获取列值rowMap.put(CharSequenceUtil.toCamelCase(columnName), columnValue);}list.add(rowMap);}ObjectMapper objectMapper = new ObjectMapper();// 测试序列化String json = objectMapper.writeValueAsString(list);List<BizDatasourceEntity> bizDatasourceEntityList = objectMapper.readValue(json, new TypeReference<>() {});bizDatasourceEntityList.forEach(datasourceEntity -> {DatasourceUtil.MyDatasourceProperty myDatasourceProperty = new DatasourceUtil.MyDatasourceProperty();BeanUtils.copyProperties(datasourceEntity, myDatasourceProperty);DataSourceProperty sourceProperty = DatasourceUtil.getHikariDataSourceProperty(myDatasourceProperty);result.put(datasourceEntity.getId(), sourceProperty);});//设置主数据源result.put("master", DatasourceUtil.getDatasourcePropertyFromProperties(this.dataSourceProperty));statement.close();return result;}}
切面类实现方法执行前根据切换数据源

由于我这里是使用的动态表结构的功能，所以是根据表的ID查询表所在数据源之后在切换的，可以在这里修改为自己的逻辑

package com.creatar.service.common.dynamic.aspect;import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.creatar.common.Assert;
import com.creatar.entitys.po.BizTableEntity;
import com.creatar.entitys.po.BizTableFieldEntity;
import com.creatar.exception.enums.DynamicTableExceptionEnum;
import com.creatar.service.common.cache.TableFieldCacheService;
import com.creatar.service.common.dynamic.datasource.DynamicHolderContext;
import com.creatar.service.mapper.mp.BizTableEntityService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;/*** 数据操作切面类** @author: 张定辉* @date: 2025-03-09 15:16* @description: 数据操作切面类*/
@Aspect
@Component
@RequiredArgsConstructor
@Slf4j
public class DataAspect {private final TableFieldCacheService tableFieldCacheService;private final BizTableEntityService bizTableEntityService;@Pointcut("execution(* com.creatar.service.common.dynamic.data.DataService.*(..))")public void dataServiceMethods() {}@Before("dataServiceMethods()")public void beforeAdvice(JoinPoint joinPoint) {//我这里是根据表的ID获取表对应的数据源，可以自行修改获取数据源的逻辑String tableId = joinPoint.getArgs()[0].toString();Assert.isNotBlank(tableId, DynamicTableExceptionEnum.TABLE_ID_NOT_BLANK);List<BizTableFieldEntity> tableFields = tableFieldCacheService.findByTableId(tableId);Map<String, String> fieldNameMapId = tableFields.stream().collect(Collectors.toMap(BizTableFieldEntity::getName, BizTableFieldEntity::getId));Map<String, String> fieldIdMapName = tableFields.stream().collect(Collectors.toMap(BizTableFieldEntity::getId, BizTableFieldEntity::getName));Map<String, BizTableFieldEntity> fieldIdMap = tableFields.stream().collect(Collectors.toMap(BizTableFieldEntity::getId, Function.identity()));//切换到表所属数据源执行BizTableEntity table = bizTableEntityService.getById(tableId);String databaseId = table.getDatabaseId();String currentDatasource = DynamicDataSourceContextHolder.peek();DynamicHolderContext.setTable(tableId);DynamicHolderContext.setFieldIdMapName(fieldIdMapName);DynamicHolderContext.setFieldIdMap(fieldIdMap);DynamicHolderContext.setFieldNameMapId(fieldNameMapId);log.info("当前切换为数据源:{}", currentDatasource);//核心方法，切换数据源DynamicDataSourceContextHolder.push(databaseId);}@After("dataServiceMethods()")public void afterAdvice(JoinPoint joinPoint) {String tableId = joinPoint.getArgs()[0].toString();Assert.isNotBlank(tableId, DynamicTableExceptionEnum.TABLE_ID_NOT_BLANK);DynamicHolderContext.removeTable();//清除当前线程数据源DynamicDataSourceContextHolder.clear();}
}
数据源表业务类

实现在保存数据源时注册数据源，以及删除数据源表时注销数据源

package com.creatar.service.common.dynamic.datasource;import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.creatar.common.Assert;
import com.creatar.common.ParamQuery;
import com.creatar.entitys.dict.EnableStatus;
import com.creatar.entitys.po.BizDatasourceEntity;
import com.creatar.entitys.po.BizTableEntity;
import com.creatar.exception.enums.DynamicTableExceptionEnum;
import com.creatar.service.common.dynamic.table.TableService;
import com.creatar.service.mapper.mp.BizDatasourceEntityService;
import com.creatar.service.mapper.mp.BizTableEntityService;
import com.creatar.util.DatasourceUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;import javax.sql.DataSource;
import java.util.List;
import java.util.Objects;/*** 数据源业务类** @author: creatar* @date: 2025-03-30 11:14* @description: 数据源业务类*/
@Service
@RequiredArgsConstructor
@Slf4j
public class DataSourceService {private final BizDatasourceEntityService bizDatasourceService;private final TableService tableService;private final BizTableEntityService bizTableEntityService;private final DynamicRoutingDataSource dynamicRoutingDataSource;@Transactional(rollbackFor = Exception.class)public boolean saveDatasource(final BizDatasourceEntity bizDatasourceEntity) {BizDatasourceEntity dbDatasource = bizDatasourceService.findByName(bizDatasourceEntity.getName());Assert.isNull(dbDatasource, DynamicTableExceptionEnum.DATA_SOURCE_EXISTS);bizDatasourceService.save(bizDatasourceEntity);//如果数据源启用则动态添加数据源if (Objects.equals(bizDatasourceEntity.getStatus(), EnableStatus.ENABLE)) {DatasourceUtil.MyDatasourceProperty property = new DatasourceUtil.MyDatasourceProperty();BeanUtils.copyProperties(bizDatasourceEntity, property);DataSource dataSource = DatasourceUtil.getDataSource(property, true);//核心方法dynamicRoutingDataSource.addDataSource(bizDatasourceEntity.getId(), dataSource);}return true;}@Transactional(rollbackFor = Exception.class)public BizDatasourceEntity updateDatasource(final BizDatasourceEntity bizDatasourceEntity) {bizDatasourceService.updateById(bizDatasourceEntity);BizDatasourceEntity newBizDatasource = bizDatasourceService.getById(bizDatasourceEntity.getId());//如果数据源是启用状态则删除已注册的旧数据源在重新添加if (Objects.equals(newBizDatasource.getStatus(), EnableStatus.ENABLE)) {DatasourceUtil.MyDatasourceProperty property = new DatasourceUtil.MyDatasourceProperty();BeanUtils.copyProperties(bizDatasourceEntity, property);DataSource dataSource = DatasourceUtil.getDataSource(property, true);//移除久的数据源在注册新数据源dynamicRoutingDataSource.removeDataSource(bizDatasourceEntity.getId());dynamicRoutingDataSource.addDataSource(bizDatasourceEntity.getId(), dataSource);}return newBizDatasource;}@Transactional(rollbackFor = Exception.class)public boolean batchRemoveDatasource(List<String> idList) {List<BizTableEntity> tableList = bizTableEntityService.findByDatasourceIdList(idList);List<String> tableIdList = tableList.stream().map(BizTableEntity::getId).toList();//删除数据源下表相关的所有数据tableService.batchRemoveTableByIds(tableIdList);//移除已注册数据源//核心方法idList.forEach(dynamicRoutingDataSource::removeDataSource);return bizDatasourceService.removeByIds(idList);}public Page<BizDatasourceEntity> pages(ParamQuery<BizDatasourceEntity> queryParam) {return bizDatasourceService.pages(queryParam);}}
http://www.xdnf.cn/news/3827.html

相关文章：

  • 算法每日一题 | 入门-顺序结构-数字反转
  • （38）VTK C++开发示例 ---纹理裁剪
  • C++负载均衡远程调用学习之异步消息任务功能与连接属性
  • CVPR2021 | 重新思考视觉Transformer中的自注意力机制
  • Java学习手册：Spring 生态其他组件介绍
  • 单细胞测序试验设计赏析（一）
  • AWS在跨境电商中的全场景实践与未来生态构建
  • D. 例题3.2.2 整数划分问题
  • 二种MVCC对比分析
  • 学习黑客风险Risk
  • iOS启动优化：从原理到实践
  • 2025年渗透测试面试题总结-拷打题库35（题目+回答）
  • 【C++】：C++17新特性
  • Vivado FPGA 开发 | 创建工程 / 仿真 / 烧录
  • 2845. 统计趣味子数组的数目
  • 【LLaMA-Factory实战】Web UI快速上手：可视化大模型微调全流程
  • The Sims 4 模拟人生 4 [DLC 解锁] [Steam Epic EA] [Windows SteamOS]
  • 《操作系统真象还原》第十二章（2）——进一步完善内核
  • 影刀RPA中新增自己的自定义指令
  • UDP网络编程
  • Xilinx FPGA | 管脚约束 / 时序约束 / 问题解析
  • 安卓基础（悬浮窗）
  • Java中深拷贝与浅拷贝的深入探讨
  • C++类_虚基类
  • IDEA快速上手Maven项目：模板选择 + 多模块拆分
  • Spring Boot 微服务打包为 Docker 镜像并部署到镜像仓库实战案例
  • 合成复用原则（CRP）
  • IDEA回滚代码操作
  • Windows下调试WebRTC源码
  • BOSS的收入 - 华为OD机试（A卷，C++题解）