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

When building multi‑tenant systems or applications that require data permission control, data isolation is a key challenge. One effective solution is to implement data filtering in the database access layer of a Spring Boot project using MyBatis interceptors combined with JSqlParser, a powerful SQL parser.

Tool Introduction

MyBatis Interceptor

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

In this article we intercept the StatementHandler interface's prepare method to modify the SQL statement and achieve 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. The AST can be modified—such as adding extra filter conditions—and then converted back to a SQL string.

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>

Note: If the project uses MyBatis‑Plus, the above dependencies are already included and adding them again may cause version conflicts.

2. Define an Interceptor

Intercept all query statements and add an env condition based on the current runtime environment.

@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 statementHandler = (StatementHandler) target;
            BoundSql boundSql = statementHandler.getBoundSql();
            String originalSql = boundSql.getSql();
            String newSql = setEnvToStatement(originalSql);
            MetaObject metaObject = SystemMetaObject.forObject(boundSql);
            metaObject.setValue("sql", newSql);
        }
        return invocation.proceed();
    }

    private String setEnvToStatement(String originalSql) {
        net.sf.jsqlparser.statement.Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(originalSql);
        } catch (JSQLParserException e) {
            throw new RuntimeException("EnvironmentVariableInterceptor::SQL parsing error: " + originalSql);
        }
        if (statement instanceof Select) {
            Select select = (Select) statement;
            PlainSelect selectBody = select.getSelectBody(PlainSelect.class);
            if (selectBody.getFromItem() instanceof Table) {
                Expression newWhereExpression;
                if (selectBody.getJoins() == null || selectBody.getJoins().isEmpty()) {
                    newWhereExpression = setEnvToWhereExpression(selectBody.getWhere(), null);
                } else {
                    newWhereExpression = multipleTableJoinWhereExpression(selectBody);
                }
                selectBody.setWhere(newWhereExpression);
            } else if (selectBody.getFromItem() instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) selectBody.getFromItem();
                PlainSelect subSelectBody = subSelect.getSelectBody(PlainSelect.class);
                Expression newWhereExpression = setEnvToWhereExpression(subSelectBody.getWhere(), null);
                subSelectBody.setWhere(newWhereExpression);
            }
            return select.toString();
        } else if (statement instanceof Insert) {
            Insert insert = (Insert) statement;
            setEnvToInsert(insert);
            return insert.toString();
        } else if (statement instanceof Update) {
            Update update = (Update) statement;
            Expression newWhereExpression = setEnvToWhereExpression(update.getWhere(), null);
            update.setWhere(newWhereExpression);
            return update.toString();
        } else if (statement instanceof Delete) {
            Delete delete = (Delete) statement;
            Expression newWhereExpression = setEnvToWhereExpression(delete.getWhere(), null);
            delete.setWhere(newWhereExpression);
            return delete.toString();
        }
        return originalSql;
    }

    private Expression setEnvToWhereExpression(Expression whereExpression, String alias) {
        AndExpression andExpression = new AndExpression();
        EqualsTo envEquals = new EqualsTo();
        envEquals.setLeftExpression(new Column(StringUtils.isNotBlank(alias) ? String.format("%s.env", alias) : "env"));
        envEquals.setRightExpression(new StringValue(env));
        if (whereExpression == null) {
            return envEquals;
        } else {
            andExpression.setRightExpression(envEquals);
            andExpression.setLeftExpression(whereExpression);
            return andExpression;
        }
    }

    private Expression multipleTableJoinWhereExpression(PlainSelect selectBody) {
        Table mainTable = selectBody.getFromItem(Table.class);
        String mainTableAlias = mainTable.getAlias().getName();
        Expression newWhereExpression = setEnvToWhereExpression(selectBody.getWhere(), mainTableAlias);
        for (Join join : selectBody.getJoins()) {
            FromItem joinRightItem = join.getRightItem();
            if (joinRightItem instanceof Table) {
                Table joinTable = (Table) joinRightItem;
                String joinTableAlias = joinTable.getAlias().getName();
                newWhereExpression = setEnvToWhereExpression(newWhereExpression, joinTableAlias);
            }
        }
        return newWhereExpression;
    }

    private void setEnvToInsert(Insert insert) {
        List<Column> columns = insert.getColumns();
        columns.add(new Column("env"));
        List<SelectBody> selects = insert.getSelect().getSelectBody(SetOperationList.class).getSelects();
        for (SelectBody select : selects) {
            if (select instanceof ValuesStatement) {
                ValuesStatement valuesStatement = (ValuesStatement) select;
                ExpressionList expressions = (ExpressionList) valuesStatement.getExpressions();
                List<Expression> values = expressions.getExpressions();
                for (Expression expression : values) {
                    if (expression instanceof RowConstructor) {
                        RowConstructor rowConstructor = (RowConstructor) expression;
                        ExpressionList exprList = rowConstructor.getExprList();
                        exprList.addExpressions(new StringValue(env));
                    }
                }
            }
        }
    }
}

3. Test

Select

Mapper:

<select id="queryAllByOrgLevel" resultType="com.lyx.mybatis.entity.AllInfo">
    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}
</select>

Original SQL before 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=?

SQL after setEnvToStatement:

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 before interception:

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

SQL after setEnvToInsert:

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

Update

Original SQL before interception:

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

SQL after adding the env condition:

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

Delete

Original SQL before interception: DELETE FROM admin WHERE id=? SQL after adding the env condition:

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. This method prepares the PreparedStatement object, which represents the SQL that will be executed. Modifying the SQL in prepare therefore changes the final statement that runs.

Both query and update are invoked after prepare and operate on the already‑prepared PreparedStatement. Intercepting them cannot alter the SQL content; they are suitable only for logging or post‑processing.

In summary, to change the SQL (e.g., add a WHERE clause or modify ordering), the interception must occur before the statement is finalized, which is exactly what the prepare method provides.

Parse configuration and mapper files.

Generate StatementHandler and BoundSql objects.

Execute prepare to create and parameterize the PreparedStatement.

Call query or update to run the SQL.

Intercepting prepare allows modification of the SQL before it is executed.