記錄：415

場(chǎng)景：使用mybatis和dynamic-datasource-spring-boot-starter動(dòng)態(tài)切換數(shù)據(jù)源操作數(shù)據(jù)庫。

版本：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：一個(gè)基于springboot的快速集成多數(shù)據(jù)源的啟動(dòng)器。

1.動(dòng)態(tài)數(shù)據(jù)源注解@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

<?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.GetDataMapper">
  <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>
</mapper>

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

<?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.InsertDataMapper">
  <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>

2.動(dòng)態(tài)數(shù)據(jù)源注解@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操作動(dòng)態(tài)數(shù)據(jù)源

不使用注解，在調(diào)用時(shí)，使用DynamicDataSourceContextHolder操作動(dòng)態(tài)數(shù)據(jù)源。

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.測(cè)試類

4.1測(cè)試類

@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.動(dòng)態(tài)數(shù)據(jù)源注解@DS作用在類上
   * */
  @GetMapping("/load01")
  public Object load01() {
    log.info("測(cè)試開始...");
    List<Long> paraList =  Arrays.asList(1L,2L,3L);
    List<Map<String, Object>> data = getDataMapper.getData(paraList);
    insertDataMapper.insertData(data);
    log.info("測(cè)試結(jié)束...");
    return "執(zhí)行成功";
  }
  /**
   * 2.動(dòng)態(tài)數(shù)據(jù)源注解@DS作用在方法上
   * */
  @GetMapping("/load02")
  public Object load02() {
    log.info("測(cè)試開始...");
    List<Long> paraList =  Arrays.asList(1L,2L,3L);
    List<Map<String, Object>> data = getAndInsertDataMapper.getData(paraList);
    getAndInsertDataMapper.insertData(data);
    log.info("測(cè)試結(jié)束...");
    return "執(zhí)行成功";
  }
  /**
   * 3.使用DynamicDataSourceContextHolder操作動(dòng)態(tài)數(shù)據(jù)源
   * */
  @GetMapping("/load03")
  public Object load03() {
    log.info("測(cè)試開始...");
    //1.使用hub_a_db數(shù)據(jù)源讀數(shù)據(jù)
    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數(shù)據(jù)源寫數(shù)據(jù)
    DynamicDataSourceContextHolder.poll();
    DynamicDataSourceContextHolder.push("hub_b_db");
    getAndInsertDataByHolderMapper.insertData(data);
    log.info("測(cè)試結(jié)束...");
    return "執(zhí)行成功";
  }
}

5.基礎(chǔ)配置

5.1配置動(dòng)態(tài)數(shù)據(jù)源

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動(dòng)態(tài)數(shù)據(jù)源依賴包

<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 '唯一標(biāo)識(shí)',
  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 '生產(chǎn)總值',
  CITY_DESCRIBE VARCHAR(512) COLLATE utf8_bin DEFAULT NULL COMMENT '城市描述',
  DATA_YEAR VARCHAR(16) COLLATE utf8_bin DEFAULT NULL COMMENT '數(shù)據(jù)年份',
  UPDATE_TIME DATETIME DEFAULT NULL COMMENT '更新時(shí)間'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='城市信息表';

以上，感謝。

2023年4月18日文章來源地址http://www.zghlxwxcb.cn/news/detail-417932.html

到了這里，關(guān)于使用mybatis和dynamic-datasource-spring-boot-starter動(dòng)態(tài)切換數(shù)據(jù)源操作數(shù)據(jù)庫的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！