Implementing Environment-Based Data Isolation in MyBatis with Custom Interceptors and Annotations

Background

In a system where pre‑release, gray and online environments share a single database, an env column was added to each table to distinguish data.

Isolation before

Initially only one core table had the env field; later dozens of tables needed the column, requiring a migration strategy that preserved existing data.

Transformation

New rows are inserted with env='all' to be visible in all environments; queries are rewritten to add env in (${currentEnv},'all').

Solution

A custom MyBatis interceptor reads the current environment from application.properties and rewrites SQL statements at runtime, avoiding changes to DO, Mapper or XML files.

Key benefits:

Business code remains unchanged.

Reduced manual field addition and error risk.

Easier future extensions.

Implementation details include using JSqlParser to parse and modify SQL, handling INSERT and SELECT cases, and storing the environment value in a thread‑local context.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Consolas, Monaco, Menlo, monospace; font-size: 12px"><span style="color: #61aeee; line-height: 26px"><span>@Intercepts</span></span><span>(</span><span><br/></span><span>        {</span><span style="color: #61aeee; line-height: 26px"><span>@Signature</span></span><span>(type = Executor</span><span style="line-height: 26px"><span>.</span><span style="color: #c678dd; line-height: 26px"><span>class</span></span><span>, </span><span style="color: #e6c07b; line-height: 26px"><span>method</span></span></span><span>= "update", args = {MappedStatement</span><span style="line-height: 26px"><span>.</span><span style="color: #c678dd; line-height: 26px"><span>class</span></span><span>, </span><span style="color: #e6c07b; line-height: 26px"><span>Object</span></span><span>.</span><span style="color: #e6c07b; line-height: 26px"><span>class</span></span><span>})}</span><span><br/></span><span>)</span><span><br/></span><span>@</span><span style="color: #e6c07b; line-height: 26px"><span>Component</span></span><span><br/></span><span style="color: #e6c07b; line-height: 26px"><span>public</span></span><span style="color: #e6c07b; line-height: 26px"><span>class</span></span><span style="color: #e6c07b; line-height: 26px"><span>EnvIsolationInterceptor</span></span><span style="color: #c678dd; line-height: 26px"><span>implements</span></span><span style="color: #e6c07b; line-height: 26px"><span>Interceptor</span></span></span><span>{</span><span><br/></span><span>    ......</span><span><br/></span><span style="color: #61aeee; line-height: 26px"><span>@Override</span></span><span><br/></span><span>    publicObjectintercept(Invocation invocation) throwsThrowable {</span><span><br/></span><span>        ......</span><span><br/></span><span style="color: #c678dd; line-height: 26px"><span>if</span></span><span> (SqlCommandType.INSERT == sqlCommandType) {</span><span><br/></span><span style="color: #c678dd; line-height: 26px"><span>try</span></span><span> {</span><span><br/></span><span style="color: #5c6370; font-style: italic; line-height: 26px"><span>// 重写 sql 执行语句，填充环境参数等</span></span><span><br/></span><span>                insertMethodProcess(invocation, boundSql);</span><span><br/></span><span>            } </span><span style="color: #c678dd; line-height: 26px"><span>catch</span></span><span> (Exception exception) {</span><span><br/></span><span>                log.error("parser insert sql exception, boundSql:" + JSON.toJSONString(boundSql), exception);</span><span><br/></span><span>                throwexception;</span><span><br/></span><span>            }</span><span><br/></span><span>        }</span><span><br/></span><span><br/></span><span>        returninvocation.proceed();</span><span><br/></span><span>    }</span><span><br/></span><span>}</span><span><br/></span></code>

Evolution

Later requirements demanded skipping environment checks for certain tables or methods. An annotation @InvokeChainSkipEnvRule with AOP was introduced to declare skip rules at the method level.

<code style="padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Consolas, Monaco, Menlo, monospace; font-size: 12px"><span style="color: #61aeee; line-height: 26px"><span>@Target</span></span><span>({ElementType.METHOD})</span><span><br/></span><span style="color: #61aeee; line-height: 26px"><span>@Retention</span></span><span>(RetentionPolicy.RUNTIME)</span><span><br/></span><span style="color: #c678dd; line-height: 26px"><span>public</span></span><span style="color: #61aeee; line-height: 26px"><span>@interface</span></span><span> InvokeChainSkipEnvRule {</span><span><br/></span><span><br/></span><span style="color: #5c6370; font-style: italic; line-height: 26px"><span>/**</span><span><br/></span><span>     * 是否跳过环境。默认 true，不推荐设置 false</span><span><br/></span><span>     *</span><span><br/></span><span>     * </span><span style="color: #c678dd; line-height: 26px"><span>@return</span></span><span><br/></span><span>     */</span></span><span><br/></span><span>    boolean isKip() default true;</span><span><br/></span><span><br/></span><span style="color: #5c6370; font-style: italic; line-height: 26px"><span>/**</span><span><br/></span><span>     * 赋值则判断规则，否则不判断</span><span><br/></span><span>     *</span><span><br/></span><span>     * </span><span style="color: #c678dd; line-height: 26px"><span>@return</span></span><span><br/></span><span>     */</span></span><span><br/></span><span>    String[] skipEnvList() default {};</span><span><br/></span><span><br/></span><span style="color: #5c6370; font-style: italic; line-height: 26px"><span>/**</span><span><br/></span><span>     * 赋值则判断规则，否则不判断</span><span><br/></span><span>     *</span><span><br/></span><span>     * </span><span style="color: #c678dd; line-height: 26px"><span>@return</span></span><span><br/></span><span>     */</span></span><span><br/></span><span>    String[] skipTableList() default {};</span><span><br/></span><span>}</span><span><br/></span></code>

The interceptor reads these rules from the application context to decide whether to apply the environment filter.

Shortcomings

The whole table is skipped, which is a coarse granularity.

Annotations can only be placed on entry methods, not on internal utility methods.

Reflection

The article concludes that a well‑designed technical solution (or separate databases) should be considered from the start to avoid invasive code changes and maintain data safety across environments.