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:

<code>&lt;dependency&gt;
    &lt;groupId&gt;org.mybatis.spring.boot&lt;/groupId&gt;
    &lt;artifactId&gt;mybatis-spring-boot-starter&lt;/artifactId&gt;
    &lt;version&gt;3.0.3&lt;/version&gt;
&lt;/dependency&gt;</code>

JSqlParser dependency:

<code>&lt;dependency&gt;
    &lt;groupId&gt;com.github.jsqlparser&lt;/groupId&gt;
    &lt;artifactId&gt;jsqlparser&lt;/artifactId&gt;
    &lt;version&gt;4.6&lt;/version&gt;
&lt;/dependency&gt;</code>

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.

<code>@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));
                    }
                }
            }
        }
    }
}
</code>

3. Test

Select

Mapper:

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

Original SQL before interception:

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

SQL after

setEnvToStatement

:

<code>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'</code>

Insert

Original SQL before interception:

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

SQL after

setEnvToInsert

:

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

Update

Original SQL before interception:

<code>UPDATE admin SET username=?, code=?, org_id=? WHERE id=?</code>

SQL after adding the

env

condition:

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

Delete

Original SQL before interception:

<code>DELETE FROM admin WHERE id=?</code>

SQL after adding the

env

condition:

<code>DELETE FROM admin WHERE id = ? AND env = 'test'</code>

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.