sharding-datasource之Mybatis基于AbstractRoutingDataSource动态切换数据源

介绍

基于sharding-datasource, mybatis进行分库操作, 实现AbstractRoutingDataSource动态切换数据源

SaaS服务多租户介绍，每个租户的资源都是独立的，从入口到应用部署到数据，都是完全隔离的。每个租户相当于“托管”在提供服务的公司里面，公司做的其实是统一运维。好处在于，这个隔离非常彻底，基本不太会有相互影响；如果有特殊客户要求的定制化，也没啥处理难度。缺陷在于，很容易因为隔离和定制，导致版本不统一，资源也浪费比较大，因为是“代运维”的模式，因此服务成本也比较高。这个时候通常采用的数据库方式是分库

1. maven项目依赖

<dependencies>
	<dependency>
           <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
</dependencies>

2.application.yml配置

spring:
  application:
    name: jdbc-db-mybatis
  profiles:
    include: jdbc

mybatis:
  mapper-locations: classpath*:/mappers/*-mapper.xml
  type-aliases-package: com.lance.sharding.mybatis.domain
  configuration:
    default-fetch-size: 20
    default-statement-timeout: 30
    map-underscore-to-camel-case: true
    use-generated-keys: true

logging:
  file:
    name: logs/${spring.application.name}.log
  level:
    org.springframework: info
    com.lance.sharding.mybatis: debug

3.application-jdbc.yml配置

com:
  multi:
    db:
      bbs_1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        password: li123456
        username: root
      bbs_2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_2?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        password: li123456
        username: root
      bbs_3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://127.0.0.1:3306/bbs_3?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        password: li123456
        username: root

4.测试Sql脚本

CREATE TABLE `t_order`
(
    `order_id`    bigint NOT NULL AUTO_INCREMENT,
    `user_id`     int    NOT NULL,
    `address_id`  bigint NOT NULL,
    `status`      tinyint NULL DEFAULT NULL,
    `creator`     varchar(32) NULL DEFAULT NULL,
    `create_time` datetime NULL DEFAULT NULL,
    `updater`     varchar(32) NULL DEFAULT NULL,
    `update_time` datetime NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

5.配置多数据源

public class DbContextHolder {

	private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

	public static void set(String source) {
		CONTEXT.set(source);
	}

	public static String get() {
		return CONTEXT.get();
	}

	public static void clear() {
		CONTEXT.remove();
	}
}

public class DynamicDataSource extends AbstractRoutingDataSource {

	public DynamicDataSource(Map<Object, Object> targetDataSources) {
		super.setTargetDataSources(targetDataSources);
	}

	/**
	 * 从 DbContextHolder 中获取所需的数据源
	 */
	@Override
	protected Object determineCurrentLookupKey() {
		return DbContextHolder.get();
	}
}

@Configuration
@AllArgsConstructor
@EnableConfigurationProperties(CustomDbProperties.class)
public class MultiDbConfig {
	private final CustomDbProperties customDbProperties;

	@Bean
	public DynamicDataSource dataSource() {
		Map<Object, Object> map = new HashMap<>(8);
		customDbProperties.getDb().forEach((k, cfg) -> map.put(k, new HikariDataSource(cfg)));
		return new DynamicDataSource(map);
	}
}

6.单元测试Test

class OrderMapperTests {
	@Autowired
	private OrderMapper orderMapper;

	@Test
	@Disabled
	void save() {
		ThreadLocalRandom random = ThreadLocalRandom.current();

		//DbContextHolder.set("bbs_1");
		DbContextHolder.set("bbs_2");

		IntStream.range(0, 20).forEach(i -> {
			Order order = new Order();
			order.setOrderId(System.nanoTime() + i);
			order.setAddressId(i);
			order.setCity("Beijing");
			order.setUserId(Math.abs(random.nextInt()));
			order.setCreator("user.0" + i);
			order.setIntervalTime(new Date());
			order.setUpdater(order.getCreator());
			log.info("====>{}", order);
			orderMapper.save(order);
		});
	}

	@Test
	@Disabled
	void findAll() {
		DbContextHolder.set("bbs_2");
		List<Order> list = orderMapper.findAll();
		log.info("===>{}", list);
	}
}

7.项目完整地址

sharding-datasource之Mybatis基于AbstractRoutingDataSource动态切换数据源 Github 地址

sharding-datasource之Mybatis基于AbstractRoutingDataSource动态切换数据源 Gitee 地址