Implementing Multi‑Tenant Isolation in MyBatis‑Plus: A Step‑by‑Step Guide

Introduction

Multi‑tenant is a key SaaS concept where a single system instance serves multiple tenants with data isolation.

Each tenant uses an independent database – high isolation, good performance, high cost.

Tenants share a database with separate schemas.

Tenants share a schema with a tenant_id column – lowest isolation.

Database Design

Two tables are created, each with a tenant_id column:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  `tenant_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `dept` (
  `id` bigint(20) NOT NULL,
  `dept_name` varchar(64) DEFAULT NULL,
  `comment` varchar(128) DEFAULT NULL,
  `tenant_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Dependency Import

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>3.1</version>
</dependency>

Implementation

The configuration class defines a PaginationInterceptor with a TenantSqlParser and a TenantHandler that supplies the tenant ID, column name, and table filter.

@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        List<ISqlParser> sqlParserList = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new TenantSqlParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean select) {
                String tenantId = "3";
                return new StringValue(tenantId);
            }
            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }
            @Override
            public boolean doTableFilter(String tableName) {
                return false;
            }
        });
        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);
        return paginationInterceptor;
    }
}

The interceptor automatically adds a tenant filter to queries, as shown in the following SQL result:

Tenant ID can be obtained from the request header:

@Override
public Expression getTenantId(boolean select) {
    ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    HttpServletRequest request = attributes.getRequest();
    String tenantId = request.getHeader("tenantId");
    return new StringValue(tenantId);
}

In asynchronous threads the request context is not propagated; it can be shared by capturing the current RequestAttributes and resetting them inside the new thread.

public List<User> getUserListByFuture() {
    ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    Callable<List<User>> getUser = () -> {
        RequestContextHolder.setRequestAttributes(sra, true);
        return userMapper.selectList(new LambdaQueryWrapper<User>().isNotNull(User::getId));
    };
    FutureTask<List<User>> future = new FutureTask<>(getUser);
    new Thread(future).start();
    try {
        return future.get();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

To exclude a table from tenant filtering, override doTableFilter and return true for that table name, e.g., "dept".

@Override
public boolean doTableFilter(String tableName) {
    List<String> IGNORE_TENANT_TABLES = Arrays.asList("dept");
    return IGNORE_TENANT_TABLES.stream().anyMatch(e -> e.equalsIgnoreCase(tableName));
}

Alternatively, use the @SqlParser(filter = true) annotation on specific mapper methods, or configure a filter in the PaginationInterceptor for particular method IDs.

@SqlParser(filter = true)
@Select("select * from user where name = #{name}")
User selectUserByName(@Param("name") String name);

When copying beans, exclude the tenant_id field to avoid duplicate tenant conditions:

BeanUtil.copyProperties(user, userSnapshot, "tenantId");

Join and sub‑query statements also receive tenant filters automatically, as demonstrated by the following examples.

For plain multi‑table queries without an explicit JOIN, only the first table receives the tenant filter, so developers must add the condition manually.