How to Manually Switch Multiple Data Sources (MySQL, SQL Server) in Spring Boot with Dynamic‑Datasource

Scenario: The article demonstrates how to use Spring Boot together with MyBatis‑Plus and the dynamic‑datasource starter to connect to multiple databases such as MySQL, SQL Server and PostgreSQL.

Dependencies

Add the following Maven dependencies (Spring Boot 2.6.13, dynamic‑datasource 3.2.1):

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>3.2.1</version>
</dependency>

<!-- MySQL driver -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
</dependency>

<!-- SQL Server driver -->
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>7.4.1.jre8</version>
</dependency>

<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.1</version>
</dependency>

<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
  <groupId>org.projectlombok</groupId>
  <artifactId>lombok</artifactId>
  <optional>true</optional>
</dependency>
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-test</artifactId>
  <scope>test</scope>
</dependency>

YAML Configuration

Configure the primary and secondary data sources in application.yml:

spring:
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          dbcp2:
            min-idle: 5
            initial-size: 5
            max-total: 5
            max-wait-millis: 150
        pg:
          url: jdbc:postgresql://127.0.0.1:5432/test
          username: postgres
          password: 123456
          driver-class-name: org.postgresql.Driver
          dbcp2:
            min-idle: 5
            initial-size: 5
            max-total: 5
            max-wait-millis: 150

Getting All Data Sources

Use DynamicRoutingDataSource to list current data sources:

@SpringBootTest
class DynamicDataSourceTest {
    @Autowired
    private DataSource dataSource;

    /**
     * Retrieve all configured data sources
     */
    @Test
    void getAllDataSource() {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        System.out.println(ds.getCurrentDataSources().keySet());
    }
}

Running the test prints the set of data‑source names (see screenshot).

Adding and Removing a SQL Server Data Source

Create a DTO to hold connection parameters:

@Data
@Builder
public class DataSourceDTO {
    private String dataSourceName;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
}

Test code that adds the SQL Server datasource, prints the set, then removes it:

@SpringBootTest
class DynamicDataSourceTest {
    @Autowired
    private DataSource dataSource;
    @Autowired(required = false)
    private HikariDataSourceCreator hikariDataSourceCreator;

    @Test
    void addHcpDataSource() {
        DataSourceDTO sqlserver = DataSourceDTO.builder()
            .dataSourceName("sqlserver")
            .driverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
            .url("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test")
            .username("sa")
            .password("123456")
            .build();
        DataSourceProperty prop = new DataSourceProperty();
        BeanUtils.copyProperties(sqlserver, prop);
        DataSource sqlserverDs = hikariDataSourceCreator.createDataSource(prop);
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        ds.addDataSource(sqlserver.getDataSourceName(), sqlserverDs);
        System.out.println(ds.getCurrentDataSources().keySet());
        ds.removeDataSource(sqlserver.getDataSourceName());
        System.out.println(ds.getCurrentDataSources().keySet());
    }
}

Checking Data‑Source Availability

Before switching, verify that the target datasource can obtain a connection and execute a simple query:

private boolean checkDataBase(DataSourceDTO dto) {
    Connection conn = null;
    try {
        DataSource ds = creatHcpDataSource(dto);
        conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute("select * from s_user");
        return true;
    } catch (Exception e) {
        return false;
    } finally {
        if (conn != null) {
            try { conn.close(); } catch (SQLException ignored) {}
        }
    }
}

The helper that builds a HikariCP datasource:

private DataSource creatHcpDataSource(DataSourceDTO dto) {
    HikariDataSource ds = new HikariDataSource();
    ds.setConnectionTimeout(3000L);
    ds.setJdbcUrl(dto.getUrl());
    ds.setUsername(dto.getUsername());
    ds.setPassword(dto.getPassword());
    ds.setPoolName(dto.getDataSourceName());
    return ds;
}

Manual Switching with DynamicDataSourceContextHolder

The core class DynamicDataSourceContextHolder stores the current datasource name in a thread‑local variable. The important methods are: push(String dsName) – set the current thread datasource (use sparingly). poll() – remove the most recent datasource name. clear() – clear the thread‑local to avoid memory leaks.

Example test that adds the datasource, checks it, switches, runs a query, then cleans up:

@Test
void changeDataSource() {
    DataSourceDTO sqlserver = DataSourceDTO.builder()
        .dataSourceName("sqlserver")
        .driverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
        .url("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=test")
        .username("sa")
        .password("123456")
        .build();
    if (checkDataBase(sqlserver)) {
        DataSourceProperty prop = new DataSourceProperty();
        BeanUtils.copyProperties(sqlserver, prop);
        DataSource ds = hikariDataSourceCreator.createDataSource(prop);
        DynamicRoutingDataSource dr = (DynamicRoutingDataSource) dataSource;
        if (!dr.getCurrentDataSources().keySet().contains(sqlserver.getDataSourceName())) {
            dr.addDataSource(sqlserver.getDataSourceName(), ds);
        }
        DynamicDataSourceContextHolder.push(sqlserver.getDataSourceName());
        List<SUser> users = sUserMapper.selectList(new LambdaQueryWrapper<>());
        System.out.println(users);
        DynamicDataSourceContextHolder.clear();
        dr.removeDataSource(sqlserver.getDataSourceName());
    } else {
        System.out.println("Data source connection error");
    }
}

Utility Class for Scoped Execution

Wrap the push/clear pattern in a reusable method that accepts a Supplier<T>:

public class DynamicDSExecute {
    public static <T> T execute(String dsName, Supplier<T> executor) {
        try {
            DynamicDataSourceContextHolder.push(dsName);
            return executor.get();
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }
}

Usage example with a lambda that runs a MyBatis‑Plus query:

Supplier<List<SUser>> supplier = () -> sUserMapper.selectList(new LambdaQueryWrapper<>());
List<SUser> users = DynamicDSExecute.execute(sqlserver.getDataSourceName(), supplier);
System.out.println(users);

The full source code can be downloaded from the provided link.