SpringBoot集成Mybatis-Plus实现多租户动态数据源_mybatisplus实现多租户多数据源

1. 概述

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

2. 开发环境

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

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 '客户名称';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

3.3. 编写application.yml文件

server:
  port: 8000
spring:
  application:
    name: SPRINGBOOT-TENANT
  datasource:
    dynamic:
      primary: master
      strict: false
      datasource:
        master:
          url: jdbc:postgresql://xxxxx:5432/dynamic-master
          username: xxxx
          password: xxxx
          driver-class-name: org.postgresql.Driver
      druid:
        initial-size: 1
        max-active: 20
        min-idle: 1
        max-wait: 6000
        pool-prepared-statements: true
        max-pool-prepared-statement-per-connection-size: 20
        validation-query: select 1
        validation-query-timeout: 10
logging:
  config: classpath:log4j2.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        return new AbstractJdbcDataSourceProvider(driverName, url, username, password) {
            @Override
            protected 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;
            }
        };
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

3.5. 存储当前数据源

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

@Log4j2
public class DynamicDataSourceInterceptor implements HandlerInterceptor {

    @Override
    public 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();
        }
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
        DynamicDataSourceContextHolder.clear();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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

@Configuration
public class WebAutoConfig implements WebMvcConfigurer {

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(dynamicDataSourceInterceptor()).addPathPatterns("/**");
    }

    @Bean
    public DynamicDataSourceInterceptor dynamicDataSourceInterceptor() {
        return new DynamicDataSourceInterceptor();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

3.6. 编写数据源Controller

@RestController
@RequestMapping(value = "/datasource")
public class DataSourceController {

    @Autowired
    private DataSource dataSource;
    @Autowired
    private DefaultDataSourceCreator dataSourceCreator;
    @Autowired
    private 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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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请求

注意：请求头必须携带需要操作的数据源标识，否则会提出错误

以上示例就简单实现了单体部署多租户系统的集成，如果是多实例部署是否有问题呢？

5. 多实例部署

5.1. 存在的问题

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

再次查询9000服务数据源信息

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

5.2. 同步数据源信息

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

@Log4j2
public class DynamicDataSourceInterceptor implements HandlerInterceptor {

    @Autowired
    private TenantService tenantService;

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        if (StringUtils.hasText(headerTenant)) {
            tenantService.reloadDataSource();
            //其他代码省略...
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

同步数据源的代码如下：

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(",")));
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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

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

重启两个示例，再次新增数据源和查询数据源信息

后记

由于作者能力有限，文中难免会出现一些错误，欢迎各位大佬不吝赐教，也希望各位大佬就多实例部署如何同步数据源问题在评论处留言讨论