SpringBoot整合MyBatis-Plus实现多数据源数据迁移

SpringBoot整合MyBatis-Plus实现多数据源数据迁移(达梦数据库、mysql)

1. 相关pom

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

<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>DmJdbcDriver18</artifactId>
    <version>8.1.3.140</version>
</dependency>

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.2.0</version>
</dependency>

2. 项目结构

3. 数据源配置

1. 配置文件

spring:
  datasource:
    dynamic:
      #设置默认数据源
      primary: mysql 
      #严格匹配数据源，默认false。true：未匹配到数据源抛异常，false：使用默认数据源
      strict: true
      datasource: 
        mysql:
          url: jdbc:mysql://xxx:3306/xxx?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          driver-class-name: com.mysql.cj.jdbc.Driver
          password: xxx
          username: root
        dm:
          url: jdbc:dm://xxx:5236/xxx
          driver-class-name: dm.jdbc.driver.DmDriver
          username: SYSDBA
          password: xxx

2. mysql数据源

@Configuration
@MapperScan(value = {"com.xxx.mysql.mapper"},
        basePackages = "com.xxx.mysql.mapper",
        sqlSessionTemplateRef  = "mysqlSqlSessionTemplate")
public class MysqlConfig {

    @Bean(name = "mysqlDSProperties")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.mysql")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "mysqlDS")
    public DataSource mysqlDataSource(@Qualifier("mysqlDSProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDS") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/mysql/*Mapper.xml"));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultEnumTypeHandler(EnumOrdinalTypeHandler.class);
        sessionFactory.setConfiguration(configuration);
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));

        sessionFactory.setPlugins(new Interceptor[]{interceptor});

        return sessionFactory.getObject();
    }

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

    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

注意：

在PlatformTransactionManager增加了一个 @Primary 注解，用来指定默认事务管理器。

原因：在多数据源情况下，使用mybatis-plus的公共方法saveBatch()时，会报错No qualifying bean of type ‘org.springframework.transaction.TransactionManager’ available ，公共方法saveBatch()不会指定事务管理器,使用的是默认管理器,但我在代码中没有告诉spring哪个数据源的事务管理器才是默认的,所以就一直在报错。

详见：https://blog.csdn.net/y_hai_yang/article/details/122617711

3. 达梦数据源

@Configuration
@MapperScan(value = {"com.xxx.dameng.mapper"},
        basePackages = "com.xxx.dameng.mapper",
        sqlSessionTemplateRef  = "dmSqlSessionTemplate")
public class DamengConfig {

    @Bean(name = "dmDSProperties")
    @ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.dm")
    public DataSourceProperties dmDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "dmDS")
    public DataSource dmDataSource(@Qualifier("dmDSProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "dmSqlSessionFactory")
    public SqlSessionFactory dmSqlSessionFactory(@Qualifier("dmDS") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean sessionFactory = new MybatisSqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/mapper/dameng/*Mapper.xml"));

        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setDefaultEnumTypeHandler(EnumOrdinalTypeHandler.class);
        sessionFactory.setConfiguration(configuration);
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));

        sessionFactory.setPlugins(new Interceptor[]{interceptor});

        return sessionFactory.getObject();
    }

    @Bean(name = "dmTransactionManager")
    public PlatformTransactionManager dmTransactionManager(@Qualifier("dmDS") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "dmSqlSessionTemplate")
    public SqlSessionTemplate dmSqlSessionTemplate(@Qualifier("dmSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

4. service层数据读取写入

其他代码省略…

@Service
@Slf4j
public class DmUserServiceImpl extends ServiceImpl<DmUserMapper, DmUser> implements DmUserService {

    @Autowired
    private UserService mysqlUserService;

    // 从达梦数据库获取数据
    @Override
    public List<DmUser> getUser() {
        QueryWrapper<DmUser> queryWrapper = new QueryWrapper<>();
        return baseMapper.selectList(queryWrapper);
    }

    // 从达梦数据库获取数据写入mysql
    // 事务管理器选择mysql的
    @Override
    @Transactional(transactionManager = "mysqlTransactionManager", rollbackFor = Exception.class)
    public void getUserAndWrite(DmUser user) {
        List<DmUser> dmUsers = getUser(user);
        ArrayList<Users> mysqlUsers = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(dmUsers)) {
            for (DmUser dmUser : dmUsers) {
                Users mysqlUser = new Users();
                BeanUtil.copyBeanSafe(dmUser, mysqlUser);
                mysqlUsers.add(mysqlUser);
            }
        }
        if (CollectionUtil.isNotEmpty(mysqlUsers)) {
            mysqlUserService.saveBatch(mysqlUsers);
        }
    }

    // 从mysql读取数据写入达梦数据库
    // 事务管理器选择达梦的
    @Override
    @Transactional(transactionManager = "dmTransactionManager", rollbackFor = Exception.class)
    public void getUserAndWriteDm(DmUser user) {
        Users users = new Users();
        List<Users> mysqlUsers = mysqlUserService.getUsers(users);
        if (CollectionUtil.isNotEmpty(mysqlUsers)) {
            ArrayList<DmUser> dmUsers = new ArrayList<>();
            for (Users mysqlUser : mysqlUsers) {
                DmUser dmUser = new DmUser();
                BeanUtil.copyBeanSafe(mysqlUser, dmUser);
                dmUser.setId(null);
                dmUser.setName(dmUser.getName() + "ss");
                dmUsers.add(dmUser);
            }
            saveBatch(dmUsers);
        }

    }
}

5. 备注

使用dbeaver连接达梦数据库：https://eco.dameng.com/community/article/56885f5ce2c66511506f7c7968da84fe