SpringBoot+dynamic-datasource实现多数据源(msyql、sqlserver)手动切换示例代码

package com.badao.demo; import com.badao.demo.entity.DataSourceDTO; import com.badao.demo.entity.SUser; import com.badao.demo.mapper.SUserMapper; import com.badao.demo.utils.DynamicDSExecute; import com.baomidou.dynamic.datasource.DynamicRoutingDataSource; import com.baomidou.dynamic.datasource.creator.HikariDataSourceCreator; import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty; import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.zaxxer.hikari.HikariDataSource; import org.junit.jupiter.api.Test; import org.springframework.beans.BeanUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import javax.sql.DataSource; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import java.util.List; import java.util.Set; import java.util.function.Supplier; @SpringBootTest class DynamicDataSourceTest { @Autowired private DataSource dataSource; @Autowired(required = false) private HikariDataSourceCreator hikariDataSourceCreator; @Autowired private SUserMapper sUserMapper; /** * 获取当前所有数据源 */ @Test void getAllDataSource() { DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource; System.out.println(ds.getCurrentDataSources().keySet()); } /** * 添加 与 删除 HikariCP数据源 */ @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 dataSourceProperty = new DataSourceProperty(); BeanUtils.copyProperties(sqlserver,dataSourceProperty); DataSource sqlserverDataSource = hikariDataSourceCreator.createDataSource(dataSourceProperty); DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource; ds.addDataSource(sqlserver.getDataSourceName(),sqlserverDataSource); System.out.println(ds.getCurrentDataSources().keySet()); ds.removeDataSource(sqlserver.getDataSourceName()); System.out.println(ds.getCurrentDataSources().keySet()); } /** * 检测数据源 */ @Test void testDataSource(){ 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(); boolean checkDataBase = checkDataBase(sqlserver); System.out.println(checkDataBase); DataSourceDTO sqlserverWrong = 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(); boolean checkDataBaseWrong = checkDataBase(sqlserverWrong); System.out.println(checkDataBaseWrong); } /** * 手动切换数据源 */ @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(); boolean checkDataBase = checkDataBase(sqlserver); if(checkDataBase){ DataSourceProperty dataSourceProperty = new DataSourceProperty(); BeanUtils.copyProperties(sqlserver,dataSourceProperty); DataSource sqlserverDataSource = hikariDataSourceCreator.createDataSource(dataSourceProperty); DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource; Set<String> dataSourceSet = ds.getCurrentDataSources().keySet(); if (!dataSourceSet.contains(sqlserver.getDataSourceName())) { ds.addDataSource(sqlserver.getDataSourceName(),sqlserverDataSource); } //push 设置当前线程数据源 如非必要不要手动调用，调用后确保最终清除 DynamicDataSourceContextHolder.push(sqlserver.getDataSourceName()); List<SUser> sUsers = sUserMapper.selectList(new LambdaQueryWrapper<>()); System.out.println(sUsers); //DynamicDataSourceContextHolder.poll(); //poll 清空当前线程数据源 如果当前线程是连续切换数据源 只会移除掉当前线程的数据源名称 //clear 强制清空本地线程 防止内存泄漏，如手动调用了push可调用此方法确保清除 DynamicDataSourceContextHolder.clear(); ds.removeDataSource(sqlserver.getDataSourceName()); }else { System.out.println("数据源连接异常"); } } /** * 手动切换数据源-工具类方法 */ @Test void changeDataSourceByUtils() { 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(); boolean checkDataBase = checkDataBase(sqlserver); if(checkDataBase){ DataSourceProperty dataSourceProperty = new DataSourceProperty(); BeanUtils.copyProperties(sqlserver,dataSourceProperty); DataSource sqlserverDataSource = hikariDataSourceCreator.createDataSource(dataSourceProperty); DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource; Set<String> dataSourceSet = ds.getCurrentDataSources().keySet(); if (!dataSourceSet.contains(sqlserver.getDataSourceName())) { ds.addDataSource(sqlserver.getDataSourceName(),sqlserverDataSource); } Supplier<List<SUser>> supplier = () -> sUserMapper.selectList(new LambdaQueryWrapper<>()); List<SUser> sUsers = DynamicDSExecute.execute(sqlserver.getDataSourceName(),supplier); System.out.println(sUsers); ds.removeDataSource(sqlserver.getDataSourceName()); }else { System.out.println("数据源连接异常"); } } /** * 检测数据源 * @param dataSourceDTO * @return */ private boolean checkDataBase(DataSourceDTO dataSourceDTO){ Connection connection = null; try { DataSource dataSource = creatHcpDataSource(dataSourceDTO); connection = dataSource.getConnection(); Statement statement = connection.createStatement(); statement.execute("select * from s_user"); return true; } catch (Exception exception) { return false; }finally { try { if(null!=connection){ connection.close(); } } catch (SQLException throwables) { throwables.printStackTrace(); } } } /** * 创建Hcp datasource * @param dataSourceDTO * @return */ private DataSource creatHcpDataSource(DataSourceDTO dataSourceDTO){ HikariDataSource dataSource = new H