Implementing Data Isolation in MyBatis with a Custom Interceptor and Annotations

1. Historical Background

In the project all environments (pre‑release, gray, online) share one database. Each table originally had an env column to distinguish rows, but only a core table contained it. A production incident where pre‑release operations polluted online data forced the team to add the env field to more than twenty tables.

1.1 Data Isolation Requirement

All tables must keep existing production data while allowing new rows to be filtered by environment. The solution was to initialize the new env column with the value all, meaning the row is visible in every environment.

1.2 Pre‑Isolation Situation

Only one core table had the env column; the rest did not. Adding the column to the remaining tables required a massive schema change and risked data loss.

1.3 Isolation Transformation

Each environment reads a unique identifier from application.properties. SQL statements are rewritten to include WHERE env = ${environment} (or env IN (${environment},'all') for backward compatibility).

SELECT XXX FROM tableName WHERE env = ${environment} AND ${condition}

1.4 Naïve Approach (Rejected)

Adding the env field to every DO, Mapper, and XML file individually was deemed unacceptable because it would be labor‑intensive and error‑prone.

1.5 Adopted Solution: MyBatis Interceptor

A custom MyBatis interceptor rewrites SQL at runtime, inserting the environment value on INSERT and adding the filter condition on SELECT. This approach avoids touching business code (DO, Mapper, XML) and centralises the change.

Business code remains untouched.

Only the interceptor logic needs modification.

Reduces the risk of missing a field during schema migration.

Facilitates future extensions.

1.6 Final Implementation

During INSERT the interceptor fills env with the current environment. During SELECT it rewrites the WHERE clause to env IN (${environment},'all') so that historical rows are still visible.

SELECT xxx FROM ${tableName} WHERE env IN (${currentEnv},'all') AND ${otherCondition}

The env value is read from application.properties and is globally unique per environment.

JSqlParser (open‑source) parses and rewrites the SQL string.

1.7 Sample Interceptor Code

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
@Component
public class EnvIsolationInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // Simplified logic
        if (SqlCommandType.INSERT == sqlCommandType) {
            try {
                insertMethodProcess(invocation, boundSql);
            } catch (Exception e) {
                log.error("parser insert sql exception, boundSql is:" + JSON.toJSONString(boundSql), e);
                throw e;
            }
        }
        return invocation.proceed();
    }
}

After deploying the interceptor, the data isolation worked flawlessly.

2. Evolution and Further Requirements

Business growth introduced new needs:

PRC interface required different environment matching.

Some environments (pre‑release, gray) needed to share data.

Developers wanted to correct online data from pre‑release.

One junior developer was tasked with the implementation. He initially filled env with null, causing runtime errors.

2.1 Root Cause Analysis

The failure stemmed from multiple ThreadLocal manipulations: method A called method B, B cleared the ThreadLocal, so A later retrieved null. The diagram below shows the call chain.

2.2 Hard‑Coded Workaround

To bypass the environment check, the developer added hard‑coded logic that set env to all possible values:

SELECT * FROM ${tableName} WHERE env IN ('pre','gray','online','all') AND ${otherCondition}

2.3 Refactoring Thoughts

The ad‑hoc code raised several concerns:

Violates the Open/Closed Principle.

Risk of missing future cases.

Mixes business logic with infrastructure concerns.

Relies on a global UserHolder to store the filter environment.

These observations motivated a more systematic redesign.

3. Refactoring with Custom Annotation + AOP

3.1 Challenges

The interceptor cannot reliably detect which service method triggered the SQL because the call stack may be deep. The goal is to modify as little existing code as possible while keeping the change isolated.

Avoid modifying existing methods.

Separate concerns between business and infrastructure.

Make the change single‑point and reusable.

3.2 Design

Define a custom annotation @InvokeChainSkipEnvRule that declares which environments or tables to skip.

Use an AOP aspect to read the annotation at runtime and store the rule in the application context.

The MyBatis interceptor reads the rule from the context and decides whether to inject the env filter.

Example usage on a controller method:

@InvokeChainSkipEnvRule(skipEnvList = {"pre"}, skipTableList = {"project"})
@GetMapping("/importSignedUserData")
public void importSignedUserData(HttpServletRequest request, HttpServletResponse response) {
    // business logic
}

3.3 Annotation Definition

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface InvokeChainSkipEnvRule {
    boolean isKip() default true; // whether to skip, default true
    String[] skipEnvList() default {};
    String[] skipTableList() default {};
}

3.4 Limitations

The rule applies to the whole table operation, so granularity is coarse.

Annotations can only be placed on entry‑point methods; internal calls are harder to control.

3.5 Final Thoughts

Even though the refactor reduced duplicated code, the author reflects that a better design would have been to separate the data‑isolation concern from the start, perhaps by using separate databases or schemas.

4. Summary and Takeaways

This case study shows how a MyBatis interceptor combined with a custom annotation can achieve data isolation across multiple environments without invasive changes to existing DAOs, while also highlighting the pitfalls of ad‑hoc hard‑coded solutions and the importance of clean separation between business and infrastructure logic.

Key lessons:

Prefer centralised interception over per‑table field additions.

Leverage open‑source tools like JSqlParser for SQL rewriting.

Use annotations and AOP to keep configuration declarative and reusable.

Always consider backward compatibility (the all value) when migrating legacy data.