使用mybatis和dynamic-datasource-spring-boot-starter动态切换数据源操作数据库

「爱情、让人受尽委屈。」 2024-04-23 03:15 116阅读 0赞

**记录**：415

**场景**：使用mybatis和dynamic-datasource-spring-boot-starter动态切换数据源操作数据库。

**版本**：JDK 1.8,Spring Boot 2.6.3,dynamic-datasource-spring-boot-starter-3.3.2,mybatis-3.5.9。

**源码**：https://github.com/baomidou/dynamic-datasource-spring-boot-starter

**dynamic-datasource-spring-boot-starter**：一个基于springboot的快速集成多数据源的启动器。

**1.动态数据源注解@DS作用在类上**

**1.1GetDataMapper接口和XML**

**1.1.1GetDataMapper接口**

@DS("hub_a_db")
    @Repository
    public interface GetDataMapper {
      List<Map<String, Object>> getData(List<Long> paraList);
    }

**1.1.2GetDataMapper.xml**

**1.2InsertDataMapper接口和XML**

**1.2.1InsertDataMapper接口**

@DS("hub_b_db")
    @Repository
    public interface InsertDataMapper {
      void insertData(List<Map<String, Object>> data);
    }

**1.2.2InsertDataMapper.xml**

**2.动态数据源注解@DS作用在方法**

**2.1GetAndInsertDataMapper接口**

@Repository
    public interface GetAndInsertDataMapper {
      @DS("hub_a_db")
      List<Map<String, Object>> getData(List<Long> paraList);
      @DS("hub_b_db")
      void insertData(List<Map<String, Object>> data);
    }

**2.2GetAndInsertDataMapper.xml**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.hub.example.load.mapper.GetAndInsertDataMapper">
      <select id="getData" resultType="java.util.Map">
          select CITY_ID AS "cityId",
          CITY_NAME AS "cityName",
          LAND_AREA AS "landArea",
          POPULATION AS "population",
          GROSS AS "gross",
          CITY_DESCRIBE AS "cityDescribe",
          DATA_YEAR AS "dataYear",
          UPDATE_TIME AS "updateTime"
          from t_city
          WHERE CITY_ID IN
          <foreach collection="list" item="cityId" open="(" separator="," close=")">
              #{cityId}
          </foreach>
      </select>
      <insert id="insertData" parameterType="java.util.List">
          insert into t_city_01 (CITY_ID,
          CITY_NAME,
          LAND_AREA,
          POPULATION,
          GROSS,
          CITY_DESCRIBE,
          DATA_YEAR,
          UPDATE_TIME)
          values
          <foreach collection="list" item="city" open="" separator="," close="">
              (#{city.cityId},
              #{city.cityName},
              #{city.landArea},
              #{city.population},
              #{city.gross},
              #{city.cityDescribe},
              #{city.dataYear},
              #{city.updateTime})
          </foreach>
      </insert>
    </mapper>

**3.使用DynamicDataSourceContextHolder操作动态数据源**

不使用注解，在调用时，使用DynamicDataSourceContextHolder操作动态数据源。

**3.1GetAndInsertDataByHolderMapper接口**

@Repository
    public interface GetAndInsertDataByHolderMapper {
      @DS("hub_a_db")
      List<Map<String, Object>> getData(List<Long> paraList);
      @DS("hub_b_db")
      void insertData(List<Map<String, Object>> data);
    }

**3.2GetAndInsertDataByHolderMapper.xml**

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.hub.example.load.mapper.GetAndInsertDataByHolderMapper">
      <select id="getData" resultType="java.util.Map">
          select CITY_ID AS "cityId",
          CITY_NAME AS "cityName",
          LAND_AREA AS "landArea",
          POPULATION AS "population",
          GROSS AS "gross",
          CITY_DESCRIBE AS "cityDescribe",
          DATA_YEAR AS "dataYear",
          UPDATE_TIME AS "updateTime"
          from t_city
          WHERE CITY_ID IN
          <foreach collection="list" item="cityId" open="(" separator="," close=")">
              #{cityId}
          </foreach>
      </select>
      <insert id="insertData" parameterType="java.util.List">
          insert into t_city_01 (CITY_ID,
          CITY_NAME,
          LAND_AREA,
          POPULATION,
          GROSS,
          CITY_DESCRIBE,
          DATA_YEAR,
          UPDATE_TIME)
          values
          <foreach collection="list" item="city" open="" separator="," close="">
              (#{city.cityId},
              #{city.cityName},
              #{city.landArea},
              #{city.population},
              #{city.gross},
              #{city.cityDescribe},
              #{city.dataYear},
              #{city.updateTime})
          </foreach>
      </insert>
    </mapper>

**4.测试类**

**4.1测试类**

@Slf4j
    @RestController
    @RequestMapping("/hub/example/load01")
    public class LoadController {
      @Autowired
      private GetDataMapper getDataMapper;
      @Autowired
      private InsertDataMapper insertDataMapper;
      @Autowired
      private GetAndInsertDataMapper getAndInsertDataMapper;
      @Autowired
      private GetAndInsertDataByHolderMapper getAndInsertDataByHolderMapper;
      /**
       * 1.动态数据源注解@DS作用在类上
       * */
      @GetMapping("/load01")
      public Object load01() {
        log.info("测试开始...");
        List<Long> paraList =  Arrays.asList(1L,2L,3L);
        List<Map<String, Object>> data = getDataMapper.getData(paraList);
        insertDataMapper.insertData(data);
        log.info("测试结束...");
        return "执行成功";
      }
      /**
       * 2.动态数据源注解@DS作用在方法上
       * */
      @GetMapping("/load02")
      public Object load02() {
        log.info("测试开始...");
        List<Long> paraList =  Arrays.asList(1L,2L,3L);
        List<Map<String, Object>> data = getAndInsertDataMapper.getData(paraList);
        getAndInsertDataMapper.insertData(data);
        log.info("测试结束...");
        return "执行成功";
      }
      /**
       * 3.使用DynamicDataSourceContextHolder操作动态数据源
       * */
      @GetMapping("/load03")
      public Object load03() {
        log.info("测试开始...");
        //1.使用hub_a_db数据源读数据
        DynamicDataSourceContextHolder.push("hub_a_db");
        List<Long> paraList =  Arrays.asList(1L,2L,3L);
        List<Map<String, Object>> data = getAndInsertDataByHolderMapper.getData(paraList);
        //2.使用hub_b_db数据源写数据
        DynamicDataSourceContextHolder.poll();
        DynamicDataSourceContextHolder.push("hub_b_db");
        getAndInsertDataByHolderMapper.insertData(data);
        log.info("测试结束...");
        return "执行成功";
      }
    }

**5.基础配置**

**5.1配置动态数据源**

spring:
      jackson:
        time-zone: GMT+8
      datasource:
        dynamic:
          primary: hub_a_db
          strict: false
          datasource:
            hub_a_db:
              url: jdbc:mysql://127.0.0.1:3306/hub_a_db
              username: hub_a
              password: 12345678
              driver-class-name: com.mysql.cj.jdbc.Driver
            hub_b_db:
              url: jdbc:mysql://127.0.0.1:3306/hub_b_db
              username: hub_b
              password: 12345678
              driver-class-name: com.mysql.cj.jdbc.Driver
    mybatis:
      mapper-locations: classpath*:mapper/**/*.xml
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

**5.2动态数据源依赖包**

<dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
      <version>3.3.2</version>
    </dependency>

**5.3建表语句**

CREATE TABLE t_city (
      CITY_ID BIGINT(16) NOT NULL COMMENT '唯一标识',
      CITY_NAME VARCHAR(64) COLLATE utf8_bin NOT NULL COMMENT '城市名',
      LAND_AREA DOUBLE DEFAULT NULL COMMENT '城市面积',
      POPULATION BIGINT(16) DEFAULT NULL COMMENT '城市人口',
      GROSS DOUBLE DEFAULT NULL COMMENT '生产总值',
      CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
      DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '数据年份',
      UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新时间'
    ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

以上，感谢。

2023年4月18日