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 essential. One effective solution is to implement data filtering in the database access layer of a Spring Boot project.

The article demonstrates how to combine MyBatis' powerful interceptor mechanism with JSqlParser, a feature‑rich SQL parser, to easily achieve data isolation based on the current runtime environment.

Tool Introduction

MyBatis Interceptor

MyBatis supports intercepting various stages of SQL execution to insert custom logic. In this case, we intercept the StatementHandler.prepare method to modify the SQL statement.

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, and then reconstruct the modified SQL.

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 MyBatis‑Plus is used, the above dependencies are already included 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. The core code is shown below:

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
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.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@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;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        if (target instanceof StatementHandler) {
            StatementHandler sh = (StatementHandler) target;
            BoundSql boundSql = sh.getBoundSql();
            String originalSql = boundSql.getSql();
            String newSql = setEnvToStatement(originalSql);
            MetaObject meta = SystemMetaObject.forObject(boundSql);
            meta.setValue("sql", newSql);
        }
        return invocation.proceed();
    }

    private String setEnvToStatement(String originalSql) {
        // parse, modify WHERE/INSERT/UPDATE/DELETE and return new SQL
        // (implementation omitted for brevity)
        return originalSql; // placeholder
    }
    // additional helper methods (setEnvToWhereExpression, multipleTableJoinWhereExpression, setEnvToInsert) omitted
}

3. Testing

Examples of SQL before and after interception:

Select

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 = ?

After interception (env = 'test'):

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

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

After interception:

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

Update

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

After interception:

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

Delete

DELETE FROM admin WHERE id = ?

After interception:

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

4. Why Intercept the prepare Method?

The SQL statement is constructed and parameterized inside the prepare method, which creates the PreparedStatement . Modifying the SQL at this point ensures the changes are applied before execution. Intercepting query or update occurs after the statement is already prepared, so changes to the SQL would have no effect.

Therefore, to add or modify WHERE clauses, ordering, or other SQL elements, interception must happen during prepare , before the final SQL is bound to the database.