How to Implement Data Isolation in Spring Boot with MyBatis Interceptor and JSqlParser

To achieve tenant‑aware data isolation in a Spring Boot application, a MyBatis interceptor can modify SQL statements before they are prepared, injecting an environment condition (e.g., env = 'test') into WHERE clauses, INSERT column lists, and other statements.

Dependencies

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

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

If MyBatis‑Plus is used, these dependencies are already included.

Interceptor Implementation

@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 {
        if (invocation.getTarget() instanceof StatementHandler) {
            StatementHandler sh = (StatementHandler) invocation.getTarget();
            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 sql) {
        try {
            net.sf.jsqlparser.statement.Statement stmt = CCJSqlParserUtil.parse(sql);
            if (stmt instanceof Select) {
                Select select = (Select) stmt;
                PlainSelect body = select.getSelectBody(PlainSelect.class);
                Expression where = body.getWhere();
                Expression newWhere = (body.getJoins() == null || body.getJoins().isEmpty())
                        ? setEnvToWhereExpression(where, null)
                        : multipleTableJoinWhereExpression(body);
                body.setWhere(newWhere);
                return select.toString();
            } else if (stmt instanceof Insert) {
                Insert insert = (Insert) stmt;
                setEnvToInsert(insert);
                return insert.toString();
            } else if (stmt instanceof Update) {
                Update update = (Update) stmt;
                update.setWhere(setEnvToWhereExpression(update.getWhere(), null));
                return update.toString();
            } else if (stmt instanceof Delete) {
                Delete delete = (Delete) stmt;
                delete.setWhere(setEnvToWhereExpression(delete.getWhere(), null));
                return delete.toString();
            }
        } catch (JSQLParserException e) {
            throw new RuntimeException("SQL parse error: " + sql, e);
        }
        return sql;
    }

    private Expression setEnvToWhereExpression(Expression original, String alias) {
        EqualsTo envEq = new EqualsTo();
        envEq.setLeftExpression(new Column(alias != null ? alias + ".env" : "env"));
        envEq.setRightExpression(new StringValue(env));
        if (original == null) {
            return envEq;
        }
        AndExpression and = new AndExpression();
        and.setLeftExpression(original);
        and.setRightExpression(envEq);
        return and;
    }

    private Expression multipleTableJoinWhereExpression(PlainSelect select) {
        Table main = select.getFromItem(Table.class);
        String mainAlias = main.getAlias() != null ? main.getAlias().getName() : main.getName();
        Expression where = setEnvToWhereExpression(select.getWhere(), mainAlias);
        for (Join join : select.getJoins()) {
            if (join.getRightItem() instanceof Table) {
                Table jt = (Table) join.getRightItem();
                String jtAlias = jt.getAlias() != null ? jt.getAlias().getName() : jt.getName();
                where = setEnvToWhereExpression(where, jtAlias);
            }
        }
        return where;
    }

    private void setEnvToInsert(Insert insert) {
        insert.getColumns().add(new Column("env"));
        // Assuming VALUES clause; add env value to each row
        if (insert.getSelect() != null && insert.getSelect().getSelectBody() instanceof SetOperationList) {
            SetOperationList setOps = (SetOperationList) insert.getSelect().getSelectBody();
            for (SelectBody sb : setOps.getSelects()) {
                if (sb instanceof ValuesStatement) {
                    ValuesStatement vs = (ValuesStatement) sb;
                    ExpressionList exprList = (ExpressionList) vs.getExpressions();
                    exprList.getExpressions().add(new StringValue(env));
                }
            }
        }
    }
}

SQL Transformation Examples

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

becomes

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 (?, ?, ?, ?)

becomes

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

Update

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

becomes

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

Delete DELETE FROM admin WHERE id=? becomes

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

Why Intercept prepare ?

The SQL string is finalized when MyBatis calls StatementHandler.prepare to create the PreparedStatement. Intercepting query or update occurs after the statement is already prepared, so modifications would not affect execution. By modifying the SQL at the prepare stage, the added tenant condition is part of the actual query.

MyBatis execution flow:

Load configuration and mapper files.

Create StatementHandler and BoundSql containing the raw SQL.

Call StatementHandler.prepare to build the PreparedStatement (interception point).

Execute query or update using the prepared statement.

This interceptor provides a reusable, framework‑level solution for tenant‑aware data isolation without modifying individual mapper XML files.