透過mybatis-plus的自定義攔截器實現控制 mybatis-plus的全域性邏輯刪除欄位的控制 （修改其最終執行的sql中的where條件）

需求:過濾部分請求不實現mybatis-plus的邏輯刪除

看到網上關於mybatis-plus的自定義攔截器的文章有的少 想了想自己寫了一篇 歡迎參考 指正

透過springboot的攔截器 在請求進來時 標記需要實現的需求的邏輯

import lombok.Data;

@Data
public class SyncBo {
    private Boolean needHandler;
}

上資料放在threadlocal以上

public final class SyncContextHolder {
    private static final ThreadLocal<SyncBo> CONTEXT = ThreadLocal.withInitial(SyncBo::new);

    private SyncContextHolder() {
    }

    /**
     * 獲取配置
     **/
    public static SyncBo getContext() {
        return CONTEXT.get();
    }

    public static void setContext(SyncBo bo) {
        CONTEXT.set(bo);
    }

    /**
     * 清空資料
     **/
    public static void clean() {
        CONTEXT.remove();
    }
}

實現springboot的攔截器

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.jetbrains.annotations.NotNull;
import org.springframework.stereotype.Component;
import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

@Component
@Slf4j
@RequiredArgsConstructor
public class DataAuthInterceptor implements HandlerInterceptor {

    private final CommonService commonService;

    private final ChsPubService chsPubService;

    @Override
    public boolean preHandle(@NotNull HttpServletRequest request, @NotNull HttpServletResponse response, @NotNull Object handler) throws Exception {
        SyncBo syncBo = SyncContextHolder.getContext();
        // 專案中的字典類。 你可以自己處理該部分的資料。反正最終要得到一個List<String>用於判斷uri是否在你需要處理的請求中
        List<DictData> syncUriList = commonService.getDictDataInfoByDictId(null, "SyncUri", null);
        if (CollectionUtils.isNotEmpty(syncUriList)) {
            List<String> syncUris = syncUriList.stream().map(DictData::getValue).collect(Collectors.toList());
            syncBo.setNeedHandler(syncUris.contains(request.getRequestURI()));
        } else {
            syncBo.setNeedHandler(false);
        }
        return HandlerInterceptor.super.preHandle(request, response, handler);
    }
  
		//以下兩個方法 預設實現即可
    @Override
    public void postHandle(@NotNull HttpServletRequest request, @NotNull HttpServletResponse response, @NotNull Object handler, ModelAndView modelAndView) throws Exception {
        HandlerInterceptor.super.postHandle(request, response, handler, modelAndView);
    }

    @Override
    public void afterCompletion(@NotNull HttpServletRequest request, @NotNull HttpServletResponse response, @NotNull Object handler, Exception ex) throws Exception {
        DataAuthContextHolder.clean();
        HandlerInterceptor.super.afterCompletion(request, response, handler, ex);
    }
}

將實現的攔截器註冊上spring中 並設定攔截所有的請求

import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
@RequiredArgsConstructor
public class AuthWebMvcConfig implements WebMvcConfigurer {

    private final DataAuthInterceptor dataAuthInterceptor;

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
      	//設定攔截所有的請求
        registry.addInterceptor(dataAuthInterceptor).addPathPatterns("/**");
    }
}

到了mybatis-plus部分

先實現一個自己的攔截器 其中發現了一個jsqlparser解析的報錯。該報錯在我的另外一篇部落格有解決方案

https://www.cnblogs.com/dkpp/p/17812677.html

以下程式碼我也不太想解釋了 涉及到mybatis-plus的原始碼和jsqlparser的原始碼。

反正就是要實現一個處理器 你所需要的改造方法在你的處理器中

import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils.MPBoundSql;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.*;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectBody;
import net.sf.jsqlparser.statement.select.SetOperationList;
import net.sf.jsqlparser.statement.update.Update;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class SyncInterceptor extends JsqlParserSupport implements InnerInterceptor {
    private SyncHandler syncHandler;

    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        if (InterceptorIgnoreHelper.willIgnoreDataPermission(ms.getId())) return;
        MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
        mpBs.sql(parserSingle(mpBs.sql(), ms.getId()));
    }

    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);
        MappedStatement ms = mpSh.mappedStatement();
        SqlCommandType sct = ms.getSqlCommandType();
        if (sct == SqlCommandType.UPDATE) {
            if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) {
                return;
            }
            BoundSql boundSql = mpSh.boundSql();
            MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
            String sql = boundSql.getSql();
            // jsqlparser目前不支援\n\n\n的解析 需要手動處理
            sql = sql.replaceAll("\n","");
            mpBs.sql(parserMulti(sql, ms.getId()));
        }
    }

    @Override
    protected void processUpdate(Update update, int index, String sql, Object obj) {
        update.setWhere(this.handlerWhere(update.getWhere(), (String) obj));
    }

    @Override
    protected void processSelect(Select select, int index, String sql, Object obj) {
        SelectBody selectBody = select.getSelectBody();
        if (selectBody instanceof PlainSelect) {
            ((PlainSelect) selectBody).setWhere(this.handlerWhere(((PlainSelect) selectBody).getWhere(), (String) obj));
        } else if (selectBody instanceof SetOperationList) {
            SetOperationList setOperationList = (SetOperationList) selectBody;
            List<SelectBody> selectBodyList = setOperationList.getSelects();
            selectBodyList.forEach(s -> ((PlainSelect) s).setWhere(this.handlerWhere(((PlainSelect) s).getWhere(), (String) obj)));
        }
    }

    protected Expression handlerWhere(Expression where, String whereSegment) {
        return syncHandler.getSqlSegment(where, whereSegment);
    }
}

處理器 其中excludedPaths引數是在構建的時候傳進來的

並且目前我需要處理的sql的mappedStatementId 為 updateById 和 selectById 兩個

這兩個方法下是一定只有兩個條件的where條件的(至少專案目前是這麼一個情況 所以我只判斷了AndExpression型別)

import com.baomidou.mybatisplus.extension.plugins.handler.DataPermissionHandler;
import com.chinacreator.c2.chs.bo.pub.SyncBo;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import org.apache.commons.lang3.BooleanUtils;

import java.util.Arrays;
import java.util.Objects;
import java.util.concurrent.atomic.AtomicBoolean;

@Slf4j
@RequiredArgsConstructor
public class SyncHandler implements DataPermissionHandler {

    private final String[] excludedPaths;

    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
        SyncBo syncBo = SyncContextHolder.getContext();
        if (BooleanUtils.isTrue(syncBo.getNeedHandler())) {
            AtomicBoolean needRemoveAtomic = new AtomicBoolean(false);
            Arrays.stream(excludedPaths).forEach(excludedPath -> {
                if (mappedStatementId.contains(excludedPath)) {
                    needRemoveAtomic.set(true);
                }
            });
            if (needRemoveAtomic.get())
                // 目前可以只處理AndExpression的情況
                // 原因為呼叫的方法是 updateById 和 selectById 方法
                // 這兩個方法預設只有一個where條件加一個vali_flag = '1'
                // 也就是兩個where條件所以可以只判斷AndExpression
                if (where instanceof AndExpression) {
                    AndExpression andExpression = (AndExpression) where;
                    String leftString = Objects.nonNull(andExpression.getLeftExpression().toString()) ? andExpression.getLeftExpression().toString() : "";
                    String rightString = Objects.nonNull(andExpression.getRightExpression().toString()) ? andExpression.getRightExpression().toString() : "";
                    if (leftString.contains("vali_flag = '1'"))
                        where = andExpression.getRightExpression();
                    if (rightString.contains("vali_flag = '1'"))
                        where = andExpression.getLeftExpression();
                }
        }
        return where;
    }
}

然後是將攔截器註冊到mybaits-plus的指定位置

我還實現了資料許可權的攔截器 這裡就不展開了

import com.baomidou.mybatisplus.core.incrementer.IdentifierGenerator;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.Map;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.LinkedBlockingQueue;

@Slf4j
@Configuration
public class MyBatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(@Value("#{'${logic-deleted.excluded.path}'.empty ? null : '${logic-deleted.excluded.path}'.split(';')}") String[] excludedPaths) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 我還實現了資料許可權的攔截器 這裡就不展開了
        // interceptor.addInnerInterceptor(new DataPermissionInterceptor(new GlobalDataAuthHandler()));
        interceptor.addInnerInterceptor(new SyncInterceptor(new SyncHandler(excludedPaths)));
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

最後是配置檔案

# mybatis-plus關於邏輯刪除欄位的配置
mybatis-plus.global-config.db-config.logic-delete-field=vali_flag
mybatis-plus.global-config.db-config.logic-not-delete-value=1

# 需要移除的mappedStatementId
logic-deleted.excluded.path=selectById;updateById

最後是結果

SyncInterceptor - original SQL: SELECT xxx FROM xxx WHERE xxx = ? AND vali_flag = '1'
SyncInterceptor - SQL to parse, SQL: SELECT xxx FROM xxx WHERE xxx = ? AND vali_flag = '1'
SyncInterceptor - parse the finished SQL: SELECT xxx FROM xxx WHERE pgid = ?
xxxMapper.selectById - ==>  Preparing: SELECT xxx FROM xxx WHERE pgid = ?
SyncInterceptor - original SQL: UPDATE xxx  SET xxx  WHERE xxx=?  AND vali_flag='1'
SyncInterceptor - SQL to parse, SQL: UPDATE xxx  SET xxx  WHERE xxx=?  AND vali_flag='1'
SyncInterceptor - parse the finished SQL: UPDATE xxx SET xxx WHERE xxx = ?
Mapper.updateById - ==>  Preparing: UPDATE xxx SET xxx WHERE xxx = ?

可以看到已經處理掉了