介绍
基于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 地址