【Spring Boot】整合Mybatis实现不同Mapper映射不同数据源

Love The Way You Lie 2022-04-13 06:51 319阅读 0赞

【问题背景】

在前两周写过一篇博客，关于项目中商品同步的优化方案记录，最后一个环节，需要将商品同步给商城，让php商城提供了一个批量同步商品的接口，但遇到的问题是每次调用接口，处理的商品到2万条就会出错，很奇怪，两种语言，但用的测试环境mysql是一个，我们可以处理十万条商品数据，他们却处理不了。

【处理方案】

php处理不了十万商品数据，也不能影响整个系统运行，所以只能是我这边做处理，在商品服务应用中配置多数据源，实现不同的Mapper映射不同的数据源，将商品数据直接插入他们的数据库中。

【项目架构】

Maven + Spring Boot + Mybatis + Druid

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70]

【项目结构】

├── pom.xml
    └── src
        └── main
            ├── java
            │   └── com
            │       └── uqiauto
            │               ├── Application.java
            │               ├── config
            │               │   └── ds
            │               │       ├── ShopDataSourceConfig.java
            │               │       └── UqierpDataSourceConfig.java
            │               ├── api
            │               │   └── WmsGoodsApi.java
            │               ├── dao
            │               │   ├── shop
            │               │   │   └── ShopGoodsMapper.java
            │               │   └── WmsGoodsMapper.java
            │               ├── model
            │               │   ├── shop
            │               │   │  └── ShopGoods.java
            │               │   ├── WmsGoods.java
            │               └── service
            │                   ├── WmsGoodsApiService.java
            └── resources
                ├── application.properties
                ├── application-dev.properties
                └── mapper
                    ├── shop
                    │   └── ShopGoodsMapper.xml
                    └── WmsGoodsMapper.xml

【代码实现】

1.  配置多数据库连接信息

#测试uplus
    uqierp.datasource.url = jdbc:mysql://****:3306/uqierp?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    uqierp.datasource.username = ***
    uqierp.datasource.password =  ***
    uqierp.datasource.driverClassName = com.mysql.jdbc.Driver
    
    #测试shop
    shop.datasource.url = jdbc:mysql://****:3306/uql_shop?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    shop.datasource.username =  ***
    shop.datasource.password =   ***
    shop.datasource.driverClassName = com.mysql.jdbc.Driver

1.  Mybatis配置信息

// Uplus数据源配置信息
    @Configuration
    @MapperScan(basePackages = UqierpDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "uqierpSqlSessionFactory")
    public class UqierpDataSourceConfig {
    
        static final String PACKAGE = "com.uqiauto.dao";
        static final String MAPPER_LOCATION = "classpath:mybatis/mapper/*.xml";
    
        @Value("${uqierp.datasource.url}")
        private String url;
    
        @Value("${uqierp.datasource.username}")
        private String user;
    
        @Value("${uqierp.datasource.password}")
        private String password;
    
        @Value("${uqierp.datasource.driverClassName}")
        private String driverClass;
    
        @Bean(name = "uqierpDataSource")
        @Primary
        public DataSource uqierpDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            return dataSource;
        }
    
        @Bean(name = "uqierpTransactionManager")
        @Primary
        public DataSourceTransactionManager uqierpTransactionManager() {
            return new DataSourceTransactionManager(uqierpDataSource());
        }
    
    
        @Bean(name = "uqierpSqlSessionFactory")
        @Primary
        public SqlSessionFactory uqierpSqlSessionFactory(@Qualifier("uqierpDataSource") DataSource uqierpDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(uqierpDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(UqierpDataSourceConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }
    
    // Shop数据源配置信息
    @Configuration
    @MapperScan(basePackages = ShopDataSourceConfig.PACKAGE,sqlSessionFactoryRef = "shopSqlSessionFactory")
    public class ShopDataSourceConfig {
    
        static final String PACKAGE = "com.uqiauto.dao.shop";
        static final String MAPPER_LOCATION = "classpath:mybatis/mapper/shop/*.xml";
    
        @Value("${shop.datasource.url}")
        private String url;
    
        @Value("${shop.datasource.username}")
        private String user;
    
        @Value("${shop.datasource.password}")
        private String password;
    
        @Value("${shop.datasource.driverClassName}")
        private String driverClass;
    
        @Bean(name = "shopDataSource")
        public DataSource shopDataSource() {
            DruidDataSource dataSource = new DruidDataSource();
            dataSource.setDriverClassName(driverClass);
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(password);
            return dataSource;
        }
    
        @Bean(name = "shopTransactionManager")
        public DataSourceTransactionManager shopTransactionManager() {
            return new DataSourceTransactionManager(shopDataSource());
        }
    
        @Bean(name = "shopSqlSessionFactory")
        public SqlSessionFactory shopSqlSessionFactory(@Qualifier("shopDataSource") DataSource shopDataSource)
                throws Exception {
            final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
            sessionFactory.setDataSource(shopDataSource);
            sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                    .getResources(ShopDataSourceConfig.MAPPER_LOCATION));
            return sessionFactory.getObject();
        }
    }

1.  GoodsService业务层实现多数据源商品插入

//1. 批量插入wms_goods商品表
      wmsGoodsService.insertBatch(newGoodsList);
    
      //2. 批量插入wms_goods_platform商品中间表
      wmsGoodsPlatformService.insertBatch(newGoodsPlatformList);
    
      //4. 进行比对，将goodsId回写商品中间表
      if(newGoodsList.size()>0 && newGoodsPlatformList.size()>0){
          for (WmsGoods wmsGoods: newGoodsList) {
              String erpGoodsId=wmsGoods.getErpGoodsId();
              for (WmsGoodsPlatform wmsGoodsPlatform: newGoodsPlatformList) {
                  String platformErpGoodsId=wmsGoodsPlatform.getErpGoodsId();
                  if(erpGoodsId.equals(platformErpGoodsId)){
                      wmsGoodsPlatform.setGoodsId(Integer.parseInt(wmsGoods.getId().toString()));
                      break;
                  }
              }
          }
      }
    
     //5. 批量更新商品中间表
      wmsGoodsPlatformService.updateBatch(newGoodsPlatformList);
    
      //6. 批量插入商城goods_common表
      List<ShopGoodsCommon> newShopGoodsCommonList=new ArrayList<>();
      for (WmsGoodsPlatform wmsGoodsPlatform: newGoodsPlatformList) {
          String erpGoodsId=wmsGoodsPlatform.getErpGoodsId();
          for (ShopGoodsCommon shopGoodsCommon: shopGoodsCommonList) {
              if(erpGoodsId.equals(shopGoodsCommon.getErpGoodsId())){
                  shopGoodsCommon.setDdtGoodsId(wmsGoodsPlatform.getGoodsId().toString());
                  newShopGoodsCommonList.add(shopGoodsCommon);
                  break;
              }
          }
      }
      shopGoodsService.insertBatchGoodsCommon(newShopGoodsCommonList);
    
      //7. 批量插入商城goods表
      List<ShopGoods> newShopGoodsList=new ArrayList<>();
      for (ShopGoods shopGoods: shopGoodsList) {
          String erpGoodsId=shopGoods.getErpGoodsId();
          for (ShopGoodsCommon shopGoodsCommon: newShopGoodsCommonList) {
              if(erpGoodsId.equals(shopGoodsCommon.getErpGoodsId())){
                  shopGoods.setGoodsCommonid(shopGoodsCommon.getGoodsCommonid());
                  shopGoods.setDdtGoodsId(shopGoodsCommon.getDdtGoodsId());
                  newShopGoodsList.add(shopGoods);
                  break;
              }
          }
      }
      shopGoodsService.insertBatch(newShopGoodsList);

【总结】

在之前的项目中，也有用Spring Boot + Mybatis实现多数据源的配置。刚开始想的就是借鉴统计服务中动态切换数据源的方式，但其中有个ThreadLocal保证线程间数据源一致，就一直切换不了数据源，所以换了这种方式，实现了在同一业务中进行数据源的切换。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTMwMzQyMjM_size_16_color_FFFFFF_t_70]: /images/20220413/9d7bb19d45434915ab97762b52e45600.png