Implementing Data Isolation in Spring Boot with MyBatis Interceptor and JSqlParser

When building multi‑tenant systems or applications that require data permission control, data isolation is a critical issue. One effective solution is to implement data filtering at the database access layer.

This article explains how to use MyBatis' powerful interceptor mechanism together with JSqlParser—a feature‑rich SQL parser—to easily achieve data isolation in a Spring Boot project, with examples based on the current runtime environment.

Tool Introduction

MyBatis Interceptor

MyBatis supports intercepting and inserting custom logic at different stages of SQL execution.

The interceptor will target the StatementHandler interface's prepare method to modify the SQL statement for data isolation.

JSqlParser

JSqlParser is an open‑source SQL parsing tool that can convert a SQL string into an abstract syntax tree (AST), allowing programs to understand and manipulate each component of the statement. By modifying the AST, additional filter conditions (e.g., an environment variable) can be added before converting it back to a SQL string.

SELECT syntax tree illustration:

Detailed Steps

1. Import Dependencies

MyBatis dependency:

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>

JSqlParser dependency:

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.6</version>
</dependency>

If the project already uses MyBatis‑Plus, the above dependencies are included automatically and should not be added again to avoid version conflicts.

2. Define the Interceptor

The interceptor intercepts all query statements and adds an env condition to the WHERE clause.

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.*;
import net.sf.jsqlparser.statement.*;
import net.sf.jsqlparser.statement.select.*;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.util.List;

@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
public class DataIsolationInterceptor implements Interceptor {
    @Value("${spring.profiles.active}")
    private String env;
    // intercept, setEnvToStatement, setEnvToWhereExpression, multipleTableJoinWhereExpression, setEnvToInsert methods ...
}

3. Test

Select

Original SQL:

SELECT a.username,a.code,o.org_code,o.org_name,o.level
FROM admin a left join organize o on a.org_id=o.id
WHERE a.dr=0 and o.level=#{level}

After interception:

SELECT a.username, a.code, o.org_code, o.org_name, o.level 
FROM admin a LEFT JOIN organize o ON a.org_id = o.id 
WHERE a.dr = 0 AND o.level = ? AND a.env = 'test' AND o.env = 'test'

Insert

Original SQL:

INSERT INTO admin (id,username,code,org_id) VALUES (?,?,?,?)

After interception:

INSERT INTO admin (id,username,code,org_id,env) VALUES (?,?,?,?,'test')

Update

Original SQL:

UPDATE admin SET username=?,code=?,org_id=? WHERE id=?

After interception:

UPDATE admin SET username = ?, code = ?, org_id = ? WHERE id = ? AND env = 'test'

Delete

Original SQL:

DELETE FROM admin WHERE id=?

After interception:

DELETE FROM admin WHERE id = ? AND env = 'test'

4. Why Intercept the prepare Method of StatementHandler ?

The SQL statement is constructed and parameterized inside the prepare method, which creates the PreparedStatement . Modifying the SQL at this point ensures the changes affect the final execution. Intercepting query or update occurs after the statement is already prepared, so changes to the SQL would not take effect.

Key steps in MyBatis execution:

Parse configuration and mapper files.

Generate StatementHandler and BoundSql .

Execute prepare to create the PreparedStatement .

Execute query or update to run the SQL.

By intercepting prepare , we can inject additional WHERE clauses, modify ordering, or otherwise alter the SQL before it is sent to the database.

Source: juejin.cn/post/7352075798067986458

Backend Exclusive Tech Group

Build a high‑quality technical community; developers, recruiters, and anyone interested in sharing job referrals are welcome.

Speak civilly, focusing on technical exchange , job referrals , and industry discussion .

Ads are prohibited; do not trust private messages to avoid scams.

Add me as a friend to join the group.

Click the ❤ below to support us, thank you!