Spring动态切换数据源实现数据清洗

1.首先创建两个不同的数据源

spring:
  #数据库配置
  datasource:
    nodes: first,second
    first:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://127.0.0.1:3306/ynjkm?useUnicode=true&useSSL=false&characterEncoding=utf8
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456
    second:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://127.0.0.1:3306/ynjkm2?useUnicode=true&useSSL=false&characterEncoding=utf8
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: root
      password: 123456

2.创建动态数据源并继承AbstractRoutingDataSource

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<String>();

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSourceType();
    }

    public static void setDataSourceType(String dataSourceType) {
        CONTEXT_HOLDER.set(dataSourceType);
    }

    public static String getDataSourceType() {
        return CONTEXT_HOLDER.get();
    }

    public static void clearDataSourceType() {
        CONTEXT_HOLDER.remove();
    }

}

3.添加配置类，继承EnvironmentAware

@Configuration
@EnableTransactionManagement
public class MyBatisConfig implements EnvironmentAware {

    // 默认数据源
    private DruidDataSource defaultDataSource;
    // 动态数据源
    private final Map<String, DruidDataSource> dynamicDataSources = new HashMap<>();

    @Override
    public void setEnvironment(Environment environment) {
        Iterable<ConfigurationPropertySource> sources = ConfigurationPropertySources.get(environment);
        Binder binder = new Binder(sources);
        //读取配置文件
        BindResult<Properties> bindResult = binder.bind("spring.datasource", Properties.class);
        Properties properties = bindResult.get();
        String dataSourceNodes = properties.getProperty("nodes");
        for (String prefix : dataSourceNodes.split(",")) {
            BindResult<Map> temp = binder.bind("spring.datasource." + prefix, Map.class);
            Map map = temp.get();
            if (!map.containsKey("url")) {
                continue;
            }
            DruidDataSource dataSource = intiDataSource(map);
            //设置默认数据源
            if ("first".equals(prefix)) {
                defaultDataSource = dataSource;
            } else {
                dynamicDataSources.put(prefix, dataSource);
            }
        }
    }

    /**
     * 注册dataSource
     *
     * @param map 数据源参数
     * @return 数据源
     */
    public DruidDataSource intiDataSource(Map<String, Object> map) {
        if (map == null || "".equals(map.get("url"))) {
            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(map.get("driver-class-name").toString());
        druidDataSource.setDbType(map.get("type").toString());
        druidDataSource.setUrl(map.get("url").toString());
        druidDataSource.setUsername(map.get("username").toString());
        druidDataSource.setPassword(map.get("password").toString());
        druidDataSource.setDefaultAutoCommit(true);
        return druidDataSource;

    }

    @Bean
    public DataSource setDynamicDataSource() {
        DynamicDataSource dataSource = new DynamicDataSource();
        //设置默认数据源
        dataSource.setDefaultTargetDataSource(defaultDataSource);
        Map<Object, Object> dataSourceMap = new HashMap<>();
        //设置动态数据源
        for (String key : dynamicDataSources.keySet()) {
            dataSourceMap.put(key, dynamicDataSources.get(key));
        }
        dataSource.setTargetDataSources(dataSourceMap);
        return dataSource;
    }

    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(setDynamicDataSource());
    }

}

4.测试清洗数据

清洗前库1数据

清洗前库2数据

用户实体

@Data
@AllArgsConstructor
@NoArgsConstructor
@ApiModel(value = "User", description = "用户信息")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 用户id
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 用户名
     */
    @ApiModelProperty(value = "用户名")
    private String userName;

    /**
     * 用户电话
     */
    @ApiModelProperty(value = "用户电话")

    private String phone;

    /**
     * 用户密码
     */
    @ApiModelProperty(value = "用户密码")

    private String passWord;

    /**
     * 数据状态：1-正常，2-删除
     */
    @ApiModelProperty(value = "数据状态")
    private Integer state;


}

业务代码

@Service
@Slf4j
public class UserServiceImpl implements UserService {


    @Resource
    private UserMapper userMapper;


    @Override
    public Integer batchInsertUser(List<User> userList) {
        return userMapper.insertBatchSomeColumn(userList);
    }

    @Override
    public Boolean cleanUser() {
        //为空时使用默认数据源-first
        log.info("初始数据源为：{}", DynamicDataSource.getDataSourceType());
        //从库1查询数据
        LambdaQueryChainWrapper<User> wapper = new LambdaQueryChainWrapper<>(userMapper);
        List<User> list = wapper.eq(User::getState, 1).list();
        if (!CollectionUtils.isEmpty(list)) {
            //切换数据源
            DynamicDataSource.setDataSourceType("second");
            log.info("切换数据源为：{}", DynamicDataSource.getDataSourceType());
            //将数据插入到库2

            this.batchInsertUser(list);
            DynamicDataSource.clearDataSourceType();
            log.info("还原数据源为：{}", DynamicDataSource.getDataSourceType());
        }

        return true;
    }

}

清洗后库2表数据

！！注意，如果数据没清洗成功，可能是加了事务的原因，所以要把事务关掉