How to Achieve Seamless Data Isolation in SpringBoot with MyBatis Interceptors

Background

In a project where pre‑release, gray, and online environments shared a single database, each table had an env column indicating the environment. Initially only one core table had this column; later dozens more needed it for proper data isolation.

Isolation Before

Only one table had env; adding the column to many tables risked data inconsistency.

Transformation

New tables received env initialized to all to keep historical data compatible. The environment value is read from application.properties and injected into SQL statements.

Solution

Instead of modifying every DAO, a custom MyBatis interceptor rewrites SQL to automatically fill or filter by env. This reduces code changes, lowers error risk, and eases future extensions.

Business code unchanged.

Avoids massive field additions.

Facilitates extension.

Implementation Details

During INSERT, the interceptor adds the current environment; during SELECT it adds env in (${currentEnv},'all'). JSqlParser is used to parse and modify SQL.

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
@Component
public class EnvIsolationInterceptor implements Interceptor {
    // ...
    public Object intercept(Invocation invocation) throws Throwable {
        // rewrite SQL, fill env
        return invocation.proceed();
    }
}

Common Issues

Null env values were caused by ThreadLocal misuse across method calls.

Refactoring

Repeated env handling code was extracted into a ThreadLocal wrapper and a custom annotation @InvokeChainSkipEnvRule with AOP to control skipping logic.

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

Pros and Cons

Single‑point change, but granularity is coarse.

Annotation works only at entry points.

Summary

The article demonstrates a practical approach to data isolation and sharing in SpringBoot applications by using a MyBatis interceptor and custom annotations, highlighting design considerations, pitfalls, and best practices.