不同数据库数据同步

不同数据库表同步

配置两个数据源进行数据的同步

配置主数据源

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 * 主数据源配置
 * <p>
 * Author: zouqinqin
 * Date: 2022/9/13 17:33
 * ModifyDate: 2022/9/13 17:33
 * Version: 1.0
 */

@Configuration
@MapperScan(basePackages = {"com.flydiy.example.ext.dao.primary"}, sqlSessionFactoryRef = "sqlSessionFactory")
public class PrimaryDataSourceConfig {

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    @Primary
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        org.apache.ibatis.session.Configuration config = new org.apache.ibatis.session.Configuration();
        config.setMapUnderscoreToCamelCase(true);
        bean.setConfiguration(config);
        return bean.getObject();
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

配置第二个数据源

package com.flydiy.example.ext.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

/**
 * 第二个数据源配置
 *
 * @author niujinpeng
 * @website: https://www.wdbyte.com
 * @date 2020/12/19
 */
@Configuration
@MapperScan(basePackages = {"com.flydiy.example.ext.dao.dataSource2"}, sqlSessionFactoryRef = "sqlSessionFactory2")
public class SecondDataSourceConfig {

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.datasource2")
    public DataSource dataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "transactionManager2")
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource2") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "sqlSessionTemplate2")
    public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

数据同步业务逻辑

@Transactional(rollbackFor = Throwable.c
lass, transactionManager = "transactionManager")
    @Override
    public Boolean synUserBasic() {
        //用作老库中新增的数据
        List<UserBasic> addNewUserList = new ArrayList<>();
        //两次同步之间变更的数据
        List<UserBasic> updateNewUserList = new ArrayList<>();
        //上一次同步记录时间
        Date lastDate = getLastDate("auth_user_basic");
        if (lastDate == null) {
            //批量新增到新库中
            firstInsertUserBasic();
            return true;
        }
        //两次定时器同步之间新增和改变的用户数据
        List<UserBasic> usersChanges = userBasicOldMapper.changeUserState(lastDate, new Date());
        if (usersChanges == null) {
            //老库中没有改变用户，不作任务操作直接返回
            return true;
        }
        //从第二次开始为增量同步，通过改变的用户去查询新库，看有没有这个用户
        for (UserBasic u : usersChanges) {
            UserBasic userBasic = userBasicNewMapper.getUserByOldId(u.getId());
            if (userBasic == null) {
                //要新增到新库中
                addNewUserList.add(u);
            } else {
                //更新新库中用户
                updateNewUserList.add(u);
            }
        }
        if (!CollectionUtils.isEmpty(addNewUserList)) {
            userBasicNewMapper.insertBatchUserBasic(addNewUserList);
        }
        if (!CollectionUtils.isEmpty(updateNewUserList)) {
            userBasicNewMapper.updateBatchUserBasic(updateNewUserList);
        }
        return true;

    }

	<select id="changeUserState" resultMap="UserBasic">
	        select *
	        from auth_user_basic
	        where unix_timestamp(last_modified_date) &gt; unix_timestamp(#{startDate})
	          and unix_timestamp(last_modified_date) &lt; unix_timestamp(#{endDate})
    </select>