1. 概述

最近接手一个多租户系统，多租户主要的就是租户之间的数据是相互隔离的，每个租户拥有自己独立的数据，相互之间不干扰。目前实现多租户主要有三种方案：
独立数据库
每个租户拥有自己单独的数据库，从物理上隔离了自己的数据，安全性最高，但是成本比较高，容易浪费数据库资源
同一数据库，不同表
每个租户的数据都在同一个数据库里，每个租户拥有一个独立的表，同样也实现了数据的隔离，安全性和成本其次
同一数据库，同一张表，字段区分
租户使用同一个数据库和同一张表，在每张表里添加进一个字段，例如tenant来区分每个租户的数据，安全性和成本都比较低，维护性也较高，单表的数据量也比较大，给查询和数据迁移都来带了麻烦
基于以上方案，本文选择第一种方案实现多租户系统

2. 开发环境

本文使用使用的开发工具/组件如表所示：

名称	版本
Idea	2020
JDK	11
SpringBoot	2.7.10
mybatis-plus-boot-starter	3.5.3.1
dynamic-datasource-spring-boot-starter	3.6.1
druid-spring-boot-starter	1.2.14
mapstruct	1.5.3.Final
postgresql	15.2
redis	7.0.10

3. 搭建项目

3.1. 新建数据库和表

先建几个数据库，分别是dynamic-master、dynamic-slave-1和dynamic-slave-2，在master库中新建tenant表，在slave库中建customer表，建表sql如下：

CREATE SEQUENCE IF NOT EXISTS tenant_id_seq;
CREATE TABLE public.tenant (id bigint NOT null DEFAULT nextval('tenant_id_seq'),tenant_id varchar(30) NOT NULL,data_source_url varchar(100) NOT NULL,data_source_username varchar(30) NOT NULL,data_source_password varchar(68) NOT NULL,data_source_driver varchar(50) NOT NULL,data_source_poolname varchar(50) NOT NULL,CONSTRAINT tenant_pk PRIMARY KEY (id),CONSTRAINT tenant_un UNIQUE (tenant_id)
);
COMMENT ON TABLE "tenant" IS '租户表';
COMMENT ON COLUMN "tenant"."tenant_id" IS '租户id';
COMMENT ON COLUMN "tenant"."data_source_url" IS '数据源URL';
COMMENT ON COLUMN "tenant"."data_source_username" IS '数据源用户名';
COMMENT ON COLUMN "tenant"."data_source_password" IS '数据源密码';
COMMENT ON COLUMN "tenant"."data_source_driver" IS '数据源驱动';
COMMENT ON COLUMN "tenant"."data_source_poolname" IS '数据源池名称';CREATE SEQUENCE IF NOT exists customer_id_seq;
CREATE TABLE public.customer (id bigint NOT NULL DEFAULT nextval('customer_id_seq'),customer_name varchar(30) NOT NULL,CONSTRAINT customer_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.customer IS '客户表';
COMMENT ON COLUMN public.customer.id IS '客户ID';
COMMENT ON COLUMN public.customer.customer_name IS '客户名称';

3.2. 引入核心依赖

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><exclusions><exclusion><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-logging</artifactId></exclusion></exclusions>
</dependency>
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.1</version>
</dependency>
<dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>3.6.1</version><exclusions><exclusion><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId></exclusion></exclusions>
</dependency>
<dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.14</version>
</dependency>
<dependency><groupId>org.postgresql</groupId><artifactId>postgresql</artifactId><scope>runtime</scope>
</dependency>
<dependency><groupId>org.mapstruct</groupId><artifactId>mapstruct</artifactId><version>1.5.3.Final</version>
</dependency>
<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>

3.3. 编写application.yml文件

server:port: 8000
spring:application:name: SPRINGBOOT-TENANTdatasource:dynamic:primary: masterstrict: falsedatasource:master:url: jdbc:postgresql://xxxxx:5432/dynamic-masterusername: xxxxpassword: xxxxdriver-class-name: org.postgresql.Driverdruid:initial-size: 1max-active: 20min-idle: 1max-wait: 6000pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20validation-query: select 1validation-query-timeout: 10
logging:config: classpath:log4j2.xml

3.4. 初始化数据源

新建DynamicDataSource配置类，将master库tenant表中数据源初始化

@Configuration
public class DynamicDataSource {@Value("${spring.datasource.dynamic.datasource.master.driver-class-name}")private String driverName;@Value("${spring.datasource.dynamic.datasource.master.url}")private String url;@Value("${spring.datasource.dynamic.datasource.master.username}")private String username;@Value("${spring.datasource.dynamic.datasource.master.password}")private String password;@Beanpublic DynamicDataSourceProvider dynamicDataSourceProvider() {return new AbstractJdbcDataSourceProvider(driverName, url, username, password) {@Overrideprotected Map<String, DataSourceProperty> executeStmt(Statement statement) throws SQLException {Map<String, DataSourceProperty> dataSourceMap = new HashMap<>();ResultSet resultSet = statement.executeQuery("select * from tenant");while (resultSet.next()) {String tenant = resultSet.getString("tenant_id");DataSourceProperty sourceProperty = new DataSourceProperty();sourceProperty.setDriverClassName(resultSet.getString("data_source_driver"));sourceProperty.setUrl(resultSet.getString("data_source_url"));sourceProperty.setUsername(resultSet.getString("data_source_username"));sourceProperty.setPassword(resultSet.getString("data_source_password"));dataSourceMap.put(tenant, sourceProperty);}return dataSourceMap;}};}
}

3.5. 存储当前数据源

因为每次请求需要访问的数据库可能都不一样，所以需要在每次请求操作时需要指定需要访问哪个数据库，新建一个拦截器

@Log4j2
public class DynamicDataSourceInterceptor implements HandlerInterceptor {@Overridepublic boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {String headerTenant = request.getHeader("X-Tenant-Id");if (StringUtils.hasText(headerTenant)) {DynamicDataSourceContextHolder.push(headerTenant);return true;}writerMessage(response, ResponseEntity.status(HttpStatus.BAD_REQUEST).body("X-Tenant-Id in request header cannot be empty!"));log.warn("X-Tenant-Id in request header cannot be empty, The path is {}", request.getRequestURL());return false;}private void writerMessage(HttpServletResponse response, ResponseEntity<String> errorMessage) {try (PrintWriter writer = response.getWriter()) {response.setStatus(errorMessage.getStatusCodeValue());response.setCharacterEncoding("UTF-8");response.setContentType("text/html; charset=utf-8");writer.print(errorMessage.getBody());} catch (Exception e) {e.printStackTrace();}}@Overridepublic void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {DynamicDataSourceContextHolder.clear();}
}

将自定义拦截器加入配置类，新建一个Web配置类

@Configuration
public class WebAutoConfig implements WebMvcConfigurer {@Overridepublic void addInterceptors(InterceptorRegistry registry) {registry.addInterceptor(dynamicDataSourceInterceptor()).addPathPatterns("/**");}@Beanpublic DynamicDataSourceInterceptor dynamicDataSourceInterceptor() {return new DynamicDataSourceInterceptor();}
}

3.6. 编写数据源Controller

@RestController
@RequestMapping(value = "/datasource")
public class DataSourceController {@Autowiredprivate DataSource dataSource;@Autowiredprivate DefaultDataSourceCreator dataSourceCreator;@Autowiredprivate TenantService tenantService;@GetMapping(value = "/getAllDataSources")public Set<String> getAllDataSources() {DynamicRoutingDataSource routingDataSource = (DynamicRoutingDataSource) dataSource;return routingDataSource.getDataSources().keySet();}@PostMapping(value = "/addDataSource")public ResponseEntity<String> addDataSource(@RequestBody DataSourceDto dataSourceDto) {DataSourceProperty sourceProperty = TenantMapper.TENANT_MAPPER.dataSourceDtoToDataSourceProperty(dataSourceDto);DynamicRoutingDataSource routingDataSource = (DynamicRoutingDataSource) dataSource;DataSource propertyDataSource = dataSourceCreator.createDataSource(sourceProperty);routingDataSource.addDataSource(dataSourceDto.getTenantId(), propertyDataSource);Tenant tenant = TenantMapper.TENANT_MAPPER.dataSourceDtoToTenant(dataSourceDto);tenantService.saveOrUpdate(tenant);String dataSourceStr = routingDataSource.getDataSources().keySet().stream().collect(Collectors.joining(","));return ResponseEntity.ok(dataSourceStr);}
}

4. 测试

在postman中输入地址http://localhost:8000/datasource/getAllDataSources，在请求头新增X-Tenant-Id=master参数，发起GET请求
初始获取数据源信息
租户张三加入系统后，只需要为张三新建一个数据库，调用新增数据源接口就行，在postman中输入地址http://localhost:8000/datasource/addDataSource，发起POST请求

此时租户张三就可以查询自己的数据信息了，在postman中输入地址http://localhost:8000/tenant/customer/getCustomerInfo/:id，发起GET请求
查询张三信息
注意：请求头必须携带需要操作的数据源标识，否则会提出错误
无X-Tenant-Id访问
以上示例就简单实现了单体部署多租户系统的集成，如果是多实例部署是否有问题呢？

5. 多实例部署

5.1. 存在的问题

在Idea中同时启动两个实例8000和9000,8000服务新增租户李四数据源，分别查询8000服务和9000服务的数据源信息
8000服务数据源信息
再次查询9000服务数据源信息

对比发下在8000服务上新增了数据源，9000服务查询不到，且无法使用新增的数据源，这是因为服务一启动就将数据源信息初始化进了内存，8000服务和9000服务内存是相互独立的，故而8000服务上操作的数据无法同步到9000服务。如果将新增后的数据源存放到8000服务和9000服务都能访问到的第三方服务上，请求进入服务后执行前先对比本地内存数据源和远程服务数据源是否相等，若不等，就先将远程服务的数据源信息同步到本地内存，这样问题是不就解决了呢！

5.2. 同步数据源信息

本示例引入redis作为第三方服务，在拦截器中增加同步数据源的操作

@Log4j2
public class DynamicDataSourceInterceptor implements HandlerInterceptor {@Autowiredprivate TenantService tenantService;@Overridepublic boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {if (StringUtils.hasText(headerTenant)) {tenantService.reloadDataSource();//其他代码省略...}}
}

同步数据源的代码如下：

public void reloadDataSource() {DynamicRoutingDataSource routingDataSource = (DynamicRoutingDataSource) dataSource;Set<String> dataSourceTypeSet = routingDataSource.getDataSources().keySet();String dataSourceType = dataSourceTypeSet.stream().collect(Collectors.joining(","));String redisDataSourceType = redisTemplate.opsForValue().get("dataSourceType");if (!dataSourceType.equals(redisDataSourceType)) {dataSourceTypeSet.stream().filter(sourceType -> !sourceType.equals("master")).forEach(routingDataSource::removeDataSource);List<Tenant> tenantList = this.list();tenantList.stream().filter(tenant -> !tenant.getTenantId().equals("master")).forEach(tenant -> {DataSourceProperty sourceProperty = new DataSourceProperty();sourceProperty.setDriverClassName(tenant.getDataSourceDriver());sourceProperty.setUrl(tenant.getDataSourceUrl());sourceProperty.setUsername(tenant.getDataSourceUsername());sourceProperty.setPoolName(tenant.getDataSourcePoolname());sourceProperty.setPassword(tenant.getDataSourcePassword());DataSource propertyDataSource = dataSourceCreator.createDataSource(sourceProperty);routingDataSource.addDataSource(tenant.getTenantId(), propertyDataSource);});redisTemplate.opsForValue().set("dataSourceType", tenantList.stream().map(tenant -> tenant.getTenantId()).collect(Collectors.joining(",")));}
}

同时需要在新增数据源的地方将数据源信息set进redis

@PostMapping(value = "/addDataSource")
public ResponseEntity<String> addDataSource(@RequestBody DataSourceDto dataSourceDto) {//其他代码省略....redisTemplate.opsForValue().set("dataSourceType", dataSourceStr);//......
}