DynamicRoutingDataSource实现SpringBoot动态数据源

一、在SpringBoot项目基础上增加依赖

        <!-- 多数据源动态数据源依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>

二、修改yml文件数据源为多数据源格式

spring:
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
        master:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: oracle.jdbc.OracleDriver
          url: jdbc:oracle:thin:@//127.0.0.1:1521/crms
          username: xxx
          password: xxxxxx

三、实现

import lombok.Data;

@Data
public class DataSourceDTO {

    /**
     * 连接池名称
     */
    private String poolName;

    /**
     * JDBC driver org.h2.Driver
     */
    private String driverClassName;

    /**
     * JDBC url 地址
     */
    private String url;

    /**
     * JDBC 用户名
     */
    private String username;

    /**
     * JDBC 密码
     */
    private String password;
}

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.*;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.desuo.riskdata.api.common.DataSourceDTO;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.BeanUtils;
import org.springframework.http.ResponseEntity;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

@RestController
@RequestMapping("/datasources")
@Api(tags = "添加删除数据源")
public class DataSourceController {

    private final DataSource dataSource;
    private final DefaultDataSourceCreator dataSourceCreator;

    public DataSourceController(DataSource dataSource, DefaultDataSourceCreator dataSourceCreator) {
        this.dataSource = dataSource;
        this.dataSourceCreator = dataSourceCreator;
    }

    /**
     * 获取当前所有数据源
     */
    @GetMapping
    @ApiOperation("获取当前所有数据源")
    public Set<String> now() {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        return ds.getDataSources().keySet();
    }

    /**
     * 添加数据源
     */
    @PostMapping
    @ApiOperation("添加数据源")
    public Set<String> add(@Validated @RequestBody DataSourceDTO dto) {
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        BeanUtils.copyProperties(dto, dataSourceProperty);
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        ds.addDataSource(dto.getPoolName(), dataSource);
        return ds.getDataSources().keySet();
    }

    /**
     * 删除数据源
     */
    @DeleteMapping
    @ApiOperation("删除数据源")
    public String remove(@RequestParam String name) {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        ds.removeDataSource(name);
        return "删除成功";
    }

    /**
     * 数据源测试
     * 请求{"poolName":"连接池名称master或DataSourceDTO.poolName()","sql":"运行测试的SQL"}
     */
    @PostMapping("/dataTest")
    @ApiOperation("数据源测试")
    public ResponseEntity<List<Map<String,Object>>> dataTest(@RequestBody Map<String,String> map){
        try {
            DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
            Connection connection = ds.getDataSource(map.get("poolName")).getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(map.get("sql"));
            // 创建一个 List 用于存储所有行的 Map
            List<Map<String, Object>> resultList = new ArrayList<>();

            // 获取 ResultSet 的元数据信息
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 遍历 ResultSet 中的每一行
            while (resultSet.next()) {
                // 创建一个 Map 用于存储当前行的数据
                Map<String, Object> row = new HashMap<>();

                // 遍历当前行的每一列
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object columnValue = resultSet.getObject(i);
                    // 将列名和列值存入当前行的 Map 中
                    row.put(columnName, columnValue);
                }

                // 将当前行的 Map 存入结果集合中
                resultList.add(row);
            }
            return ResponseEntity.ok(resultList);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}