Elegant Multi‑Tenant Data Isolation with MyBatis‑Plus in SaaS Applications

In a SaaS system a tenant represents a company customer; multiple tenants share the same application instance, so data isolation is essential to prevent one tenant from accessing another's data.

Three common isolation architectures are described:

Each tenant uses an independent database (high isolation, high cost).

All tenants share one database but have separate tablespaces (moderate cost, moderate isolation).

All tenants share the same tables and are distinguished by a tenant‑id column (low cost, low isolation).

The article includes a comparison table of these schemes:

Isolation Scheme

Cost

Supported Tenant Count

Advantages

Disadvantages

Independent Database

High

Few

High isolation and security; can customize per tenant

High hardware and maintenance cost

Separate Tablespace

Medium

More

Logical isolation within a single DB

Complex DB management, many tables

Tenant‑ID Column

Low

Many

Lowest cost, supports most tenants

Lowest isolation and security

Most companies adopt the third approach (tenant‑id column). The article then shows how to achieve elegant data isolation using MyBatis‑Plus’s multi‑tenant plugin.

Key classes:

public class TenantLineInnerInterceptor extends JsqlParserSupport implements InnerInterceptor { ... }

Implementation steps:

Create a custom TenantLineHandler (e.g., TenantDatabaseHandler ) to supply the tenant column name, tenant value, and tables to ignore.

Define a configuration properties class ( TenantProperties ) to enable the feature and list ignored tables.

Register the interceptor in a Spring @Bean, ensuring the tenant interceptor is added before the pagination interceptor.

Example handler code:

public class TenantDatabaseHandler implements TenantLineHandler {
    private final Set
ignoreTables = new HashSet<>();
    public TenantDatabaseHandler(TenantProperties properties) {
        properties.getIgnoreTables().forEach(t -> { ignoreTables.add(t.toLowerCase()); ignoreTables.add(t.toUpperCase()); });
    }
    @Override
    public String getTenantIdColumn() { return "org_id"; }
    @Override
    public Expression getTenantId() { return new LongValue(RequestUserHolder.getCurrentUser().getOrgId()); }
    @Override
    public boolean ignoreTable(String tableName) { return CollUtil.contains(ignoreTables, tableName); }
}

Configuration properties:

@ConfigurationProperties(prefix = "ptc.tenant")
@Data
public class TenantProperties {
    private Boolean enable = Boolean.TRUE;
    private Set
ignoreTables = Collections.emptySet();
}

Bean registration:

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(TenantProperties properties) {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    if (properties.getEnable()) {
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantDatabaseHandler(properties)));
    }
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
    return interceptor;
}

SQL example before and after interception shows the tenant condition (e.g., u.org_id = 3 ) being automatically appended to queries.

To ignore a specific table, add it to ptc.tenant.ignore-tables . The article notes a subtle issue when the table name is a MySQL keyword and is quoted (e.g., `user` ); the ignore logic may fail because the parser returns the raw name without backticks.

For queries that should bypass tenant filtering, the @InterceptorIgnore(tenantLine = "true") annotation can be placed on the mapper method.

The underlying SQL parsing is performed by JSqlParser. An example demonstrates a parsing failure when using an alias ur for user_role , which is a known limitation of the current JSqlParser version. The author references an issue on the MyBatis‑Plus GitHub and suggests upgrading JSqlParser or changing the alias.

Finally, the article summarizes that the multi‑tenant plugin works by parsing SQL, injecting the tenant filter, and that similar techniques can be extended to more granular data‑permission schemes (e.g., department, team, user level).

It ends with a call for readers to like, share, and follow the author’s public account, and promotes a paid knowledge community.