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

记录：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

<?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_cityWHERE 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.动态数据源注解@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_cityWHERE 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_cityWHERE 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 {@Autowiredprivate GetDataMapper getDataMapper;@Autowiredprivate InsertDataMapper insertDataMapper;@Autowiredprivate GetAndInsertDataMapper getAndInsertDataMapper;@Autowiredprivate 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+8datasource:dynamic:primary: hub_a_dbstrict: falsedatasource:hub_a_db:url: jdbc:mysql://127.0.0.1:3306/hub_a_dbusername: hub_apassword: 12345678driver-class-name: com.mysql.cj.jdbc.Driverhub_b_db:url: jdbc:mysql://127.0.0.1:3306/hub_b_dbusername: hub_bpassword: 12345678driver-class-name: com.mysql.cj.jdbc.Driver
mybatis:mapper-locations: classpath*:mapper/**/*.xmlconfiguration: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日