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表数据
!!注意,如果数据没清洗成功,可能是加了事务的原因,所以要把事务关掉