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

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.RowConstructor;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.values.ValuesStatement;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
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 {
    /**
     * 從配置文件中環(huán)境變量
     */
    @Value("${spring.profiles.active}")
    private String env;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object target = invocation.getTarget();
        //確保只有攔截的目標(biāo)對(duì)象是 StatementHandler 類型時(shí)才執(zhí)行特定邏輯
        if (target instanceof StatementHandler) {
            StatementHandler statementHandler = (StatementHandler) target;
            // 獲取 BoundSql 對(duì)象，包含原始 SQL 語(yǔ)句
            BoundSql boundSql = statementHandler.getBoundSql();
            String originalSql = boundSql.getSql();
            String newSql = setEnvToStatement(originalSql);
            // 使用MetaObject對(duì)象將新的SQL語(yǔ)句設(shè)置到BoundSql對(duì)象中
            MetaObject metaObject = SystemMetaObject.forObject(boundSql);
            metaObject.setValue("sql", newSql);
        }
        // 執(zhí)行SQL
        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語(yǔ)句解析異常："+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 {
                    // 如果是多表關(guān)聯(lián)查詢，在關(guān)聯(lián)查詢中新增每個(gè)表的環(huán)境變量條件
                    newWhereExpression = multipleTableJoinWhereExpression(selectBody);
                }
                // 將新的where設(shè)置到Select中
                selectBody.setWhere(newWhereExpression);
            } else if (selectBody.getFromItem() instanceof SubSelect) {
                // 如果是子查詢，在子查詢中新增環(huán)境變量條件
                // 當(dāng)前方法只能處理單層子查詢，如果有多層級(jí)的子查詢的場(chǎng)景需要通過(guò)遞歸設(shè)置環(huán)境變量
                SubSelect subSelect = (SubSelect) selectBody.getFromItem();
                PlainSelect subSelectBody = subSelect.getSelectBody(PlainSelect.class);
                Expression newWhereExpression = setEnvToWhereExpression(subSelectBody.getWhere(), null);
                subSelectBody.setWhere(newWhereExpression);
            }

            // 獲得修改后的語(yǔ)句
            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);
            // 將新的where設(shè)置到Update中
            update.setWhere(newWhereExpression);

            return update.toString();
        } else if (statement instanceof Delete) {
            Delete delete = (Delete) statement;
            Expression newWhereExpression = setEnvToWhereExpression(delete.getWhere(),null);
            // 將新的where設(shè)置到delete中
            delete.setWhere(newWhereExpression);

            return delete.toString();
        }
        return originalSql;
    }

    /**
     * 將需要隔離的字段加入到SQL的Where語(yǔ)法樹中
     * @param whereExpression SQL的Where語(yǔ)法樹
     * @param alias 表別名
     * @return 新的SQL Where語(yǔ)法樹
     */
    private Expression setEnvToWhereExpression(Expression whereExpression, String alias) {
        // 添加SQL語(yǔ)法樹的一個(gè)where分支，并添加環(huán)境變量條件
        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 {
            // 將新的where條件加入到原where條件的右分支樹
            andExpression.setRightExpression(envEquals);
            andExpression.setLeftExpression(whereExpression);
            return andExpression;
        }
    }

    /**
     * 多表關(guān)聯(lián)查詢時(shí)，給關(guān)聯(lián)的所有表加入環(huán)境隔離條件
     * @param selectBody select語(yǔ)法樹
     * @return 新的SQL Where語(yǔ)法樹
     */
    private Expression multipleTableJoinWhereExpression(PlainSelect selectBody){
        Table mainTable = selectBody.getFromItem(Table.class);
        String mainTableAlias = mainTable.getAlias().getName();
        // 將 t1.env = ENV 的條件添加到where中
        Expression newWhereExpression = setEnvToWhereExpression(selectBody.getWhere(), mainTableAlias);
        List<Join> joins = selectBody.getJoins();
        for (Join join : joins) {
            FromItem joinRightItem = join.getRightItem();
            if (joinRightItem instanceof Table) {
                Table joinTable = (Table) joinRightItem;
                String joinTableAlias = joinTable.getAlias().getName();
                // 將每一個(gè)join的 tx.env = ENV 的條件添加到where中
                newWhereExpression = setEnvToWhereExpression(newWhereExpression, joinTableAlias);
            }
        }
        return newWhereExpression;
    }

    /**
     * 新增數(shù)據(jù)時(shí)，插入env字段
     * @param insert Insert 語(yǔ)法樹
     */
    private void setEnvToInsert(Insert insert) {
        // 添加env列
        List<Column> columns = insert.getColumns();
        columns.add(new Column("env"));
        // values中添加環(huán)境變量值
        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));
                    }
                }
            }
        }
    }
}

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

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

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 INTO admin  ( id, username, code,   org_id )  VALUES (  ?, ?, ?,   ?  )

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

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

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

DELETE FROM admin WHERE id=?

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