默認租戶插件處理器的缺陷
在springboot工程中引入mybatis-plus的租戶插件TenantLineInnerInterceptor,能簡化我們的數據隔離操作,例如各類含租戶用戶登錄權限的rest接口中,不需要再根據登錄用戶-set租戶條件-觸發查詢,租戶插件能幫我們省略手動插入條件的繁瑣過程。
mybatis-plus默認僅支持單個字段的租戶條件,實際使用場景中,我們的“租戶”在系統中,可能是一個多類型的數據概念,例如菜單大類模塊一可能一種用戶能訪問,菜單大類模塊二是另一種用戶能訪問,兩種模塊用戶權限都在同一管理菜單、權限、用戶中進行配置,即用戶區分類型,不同類型租戶id屬性來源不同。由于不同大類模塊字段可能不一致,即“TenantId”在不同表中,是不同的字段名稱,這時候使用原始的租戶插件接口,就滿足不了需求了。
public interface TenantLineHandler {/*** 獲取租戶 ID 值表達式,只支持單個 ID 值** @return 租戶 ID 值表達式*/Expression getTenantId();/*** 獲取租戶字段名* 默認字段名叫: tenant_id** @return 租戶字段名*/default String getTenantIdColumn() {return "tenant_id";}/*** 根據表名判斷是否忽略拼接多租戶條件* 默認都要進行解析并拼接多租戶條件** @param tableName 表名* @return 是否忽略, true:表示忽略,false:需要解析并拼接多租戶條件*/default boolean ignoreTable(String tableName) {return false;}/*** 忽略插入租戶字段邏輯** @param columns 插入字段* @param tenantIdColumn 租戶 ID 字段* @return*/default boolean ignoreInsert(List<Column> columns, String tenantIdColumn) {return columns.stream().map(Column::getColumnName).anyMatch(i -> i.equalsIgnoreCase(tenantIdColumn));}
}
上述接口中getTenantId和getTenantIdColumn是唯一的,無法區分不同表不同租戶字段。
針對多字段條件租戶插件的實現
改造步驟如下:
1、定義一個新的租戶數據行處理器
如這里命名FixTenantLineHandler,
僅更新getTenantId和getTenantIdColumn方法,方便根據表名來判斷取什么租戶字段。
package com.infypower.vpp.security.permission;import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.schema.Column;import java.util.List;/*** 租戶處理器( TenantId 行級 )** @author endcy* @see com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler*/
public interface FixTenantLineHandler {/*** 獲取租戶 ID 值表達式,只支持單個 ID 值* <p>** @return 租戶 ID 值表達式*/Expression getTenantId(String tableName);/*** 獲取租戶字段名* <p>* 默認字段名叫: tenant_id** @return 租戶字段名*/default String getTenantIdColumn(String tableName) {return "tenant_id";}/*** 根據表名判斷是否忽略拼接多租戶條件* <p>* 默認都要進行解析并拼接多租戶條件** @param tableName 表名* @return 是否忽略, true:表示忽略,false:需要解析并拼接多租戶條件*/default boolean ignoreTable(String tableName) {return false;}/*** 忽略插入租戶字段邏輯** @param columns 插入字段* @param tenantIdColumn 租戶 ID 字段* @return*/default boolean ignoreInsert(List<Column> columns, String tenantIdColumn) {return columns.stream().map(Column::getColumnName).anyMatch(i -> i.equalsIgnoreCase(tenantIdColumn));}
}
2、實現自定義TenantLineHandler
下列TenantSecurityUtils是用戶登錄時注入的requestScope或者ThreadLocal存儲的用戶信息,包含不同租戶類型信息及ID、管理員租戶數據授權配置信息等,可自定義實現。
hasProperty方法會取mybatis-plus緩存的表信息,根據表名判斷表映射屬性是否包含不同租戶id字段,這樣做的好處是無需額外編碼處理不同表對應不同屬性名稱配置判斷,直接使用mybatis-plus原有的TableInfoHelper相關功能。
import cn.hutool.core.collection.CollUtil;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.core.metadata.TableInfoHelper;
import com.gitee.coadmin.enums.UserIdentityTypeEnum;
import com.gitee.coadmin.utils.TenantSecurityUtils;
import com.infypower.vpp.common.CesConstant;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.schema.Column;
import org.springframework.stereotype.Component;import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;/*** ...** @author endcy* @date 2025/9/9*/
@Slf4j
@Component
public class FixTenantLineInnerInterceptor implements FixTenantLineHandler {private static final String TENANT_ID1_COLUMN = "tenant_id1";private static final String TENANT_ID1_NAME = "tenantId1";private static final String TENANT_ID2_COLUMN = "tenant_id2";private static final String TENANT_ID2_NAME = "tenantId2";private static final String TENANT_ID3_COLUMN = "tenant_id3";private static final String TENANT_ID3_NAME = "tenantId3";//存在租戶id 字段但忽略租戶過濾的數據表 逐行加private final List<String> IGNORE_TABLES = CollUtil.newArrayList("");@Overridepublic Expression getTenantId(String tableName) {UserIdentityTypeEnum identityType = TenantSecurityUtils.getCurrentUserIdentityType();//如果是平臺權限用戶 可能有需要過濾租戶類型1數據if (identityType == UserIdentityTypeEnum.PLATFORM) {Set<Long> tenantIds = TenantSecurityUtils.getCurrentPlatformUserTenantId1List();if (CollUtil.isEmpty(tenantIds)) {//默認返回平臺管理權限return new LongValue(0L);}List<Expression> valueList = tenantIds.stream().map(LongValue::new).collect(Collectors.toList());return new InExpression(new Column(getTenantIdColumn(tableName)),new ExpressionList(valueList));}return new LongValue(TenantSecurityUtils.getCurrentUserMajorIdentityId());}@Overridepublic String getTenantIdColumn(String tableName) {String tenantColumn = TENANT_ID1_COLUMN;//平臺理員綁定了特定租戶權限if (hasProperty(tableName, TENANT_ID1_NAME) && hasProperty(tableName, TENANT_ID2_NAME)) {tenantColumn = TENANT_ID1_COLUMN;if (TenantSecurityUtils.getCurrentUserIdentityType() == UserIdentityTypeEnum.TENANT1) {tenantColumn = TENANT_ID2_COLUMN;}} else if (hasProperty(tableName, TENANT_ID2_NAME)) {tenantColumn = TENANT_ID2_COLUMN;} else if (hasProperty(tableName, TENANT_ID3_NAME)) {tenantColumn = TENANT_ID3_COLUMN;}//默認返回主租戶字段log.debug(">>>> table:{} tenantColumn:{}", tableName, tenantColumn);return tenantColumn;}@Overridepublic boolean ignoreTable(String tableName) {boolean ignore;if (IGNORE_TABLES.contains(tableName)) {return true;}//全數據管理員略過if (TenantSecurityUtils.getCurrentUserMajorIdentityId() == 0L&& CollUtil.isEmpty(TenantSecurityUtils.getCurrentPlatformUserTenantId1List())) {return true;}if (!hasProperty(tableName, TENANT_ID1_NAME)&& !hasProperty(tableName, TENANT_ID2_NAME)&& !hasProperty(tableName, TENANT_ID3_NAME)) {ignore = true;log.debug(">>>> ignore tenant data for table:{}", tableName);} else {ignore = false;}if (ignore) {IGNORE_TABLES.add(tableName);return true;}
// ignore = checkIgnoreTable(tableName);return false;}private boolean checkIgnoreTable(String tableName) {if (IGNORE_TABLES.contains(tableName.toLowerCase())) {return true;}//其他校驗邏輯待拓展return false;}public static boolean hasProperty(String tableName, String propertyName) {TableInfo tableInfo = TableInfoHelper.getTableInfo(tableName);if (tableInfo == null) {return false;}// 檢查字段列表是否包含該屬性return tableInfo.getFieldList().stream().anyMatch(field -> field.getProperty().equals(propertyName));}}
3、重寫租戶sql注入處理器
參考原TenantLineInnerInterceptor實現,使用tenantLineHandler,并替換需要傳入表名的調用
package com.infypower.vpp.security.permission;import com.baomidou.mybatisplus.core.plugins.InterceptorIgnoreHelper;
import com.baomidou.mybatisplus.core.toolkit.*;
import com.baomidou.mybatisplus.extension.parser.JsqlParserSupport;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.baomidou.mybatisplus.extension.toolkit.PropertyMapper;
import lombok.*;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
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 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.*;/*** @author endcy* @since 2029/9/9* @see com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
@SuppressWarnings({"rawtypes"})
public class FixTenantLineInnerInterceptor2 extends JsqlParserSupport implements InnerInterceptor {private FixTenantLineHandler tenantLineHandler;@Overridepublic void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {……}@Overridepublic void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {……}@Overrideprotected void processSelect(Select select, int index, String sql, Object obj) {……}protected void processSelectBody(SelectBody selectBody) {……}@Overrideprotected void processInsert(Insert insert, int index, String sql, Object obj) {String tableName = insert.getTable().getName();if (tenantLineHandler.ignoreTable(tableName)) {// 過濾退出執行return;}List<Column> columns = insert.getColumns();if (CollectionUtils.isEmpty(columns)) {// 針對不給列名的insert 不處理return;}String tenantIdColumn = tenantLineHandler.getTenantIdColumn(tableName);if (tenantLineHandler.ignoreInsert(columns, tenantIdColumn)) {// 針對已給出租戶列的insert 不處理return;}columns.add(new Column(tenantIdColumn));// fixed gitee pulls/141 duplicate updateList<Expression> duplicateUpdateColumns = insert.getDuplicateUpdateExpressionList();if (CollectionUtils.isNotEmpty(duplicateUpdateColumns)) {EqualsTo equalsTo = new EqualsTo();equalsTo.setLeftExpression(new StringValue(tenantIdColumn));equalsTo.setRightExpression(tenantLineHandler.getTenantId(tableName));duplicateUpdateColumns.add(equalsTo);}Select select = insert.getSelect();if (select != null) {this.processInsertSelect(tableName, select.getSelectBody());} else if (insert.getItemsList() != null) {// fixed github pull/295ItemsList itemsList = insert.getItemsList();if (itemsList instanceof MultiExpressionList) {((MultiExpressionList) itemsList).getExpressionLists().forEach(el -> el.getExpressions().add(tenantLineHandler.getTenantId(tableName)));} else {((ExpressionList) itemsList).getExpressions().add(tenantLineHandler.getTenantId(tableName));}} else {throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId");}}/*** update 語句處理*/@Overrideprotected void processUpdate(Update update, int index, String sql, Object obj) {final Table table = update.getTable();if (tenantLineHandler.ignoreTable(table.getName())) {// 過濾退出執行return;}update.setWhere(this.andExpression(table, update.getWhere()));}/*** delete 語句處理*/@Overrideprotected void processDelete(Delete delete, int index, String sql, Object obj) {……}/*** delete update 語句 where 處理*/protected BinaryExpression andExpression(Table table, Expression where) {//獲得where條件表達式EqualsTo equalsTo = new EqualsTo();equalsTo.setLeftExpression(this.getAliasColumn(table));equalsTo.setRightExpression(tenantLineHandler.getTenantId(table.getName()));if (null != where) {if (where instanceof OrExpression) {return new AndExpression(equalsTo, new Parenthesis(where));} else {return new AndExpression(equalsTo, where);}}return equalsTo;}/*** 處理 insert into select* <p>* 進入這里表示需要 insert 的表啟用了多租戶,則 select 的表都啟動了** @param selectBody SelectBody*/protected void processInsertSelect(String tableName, SelectBody selectBody) {PlainSelect plainSelect = (PlainSelect) selectBody;FromItem fromItem = plainSelect.getFromItem();if (fromItem instanceof Table) {// fixed gitee pulls/141 duplicate updateprocessPlainSelect(plainSelect);appendSelectItem(tableName, plainSelect.getSelectItems());} else if (fromItem instanceof SubSelect) {SubSelect subSelect = (SubSelect) fromItem;appendSelectItem(tableName, plainSelect.getSelectItems());processInsertSelect(tableName, subSelect.getSelectBody());}}/*** 追加 SelectItem** @param selectItems SelectItem*/protected void appendSelectItem(String tableName, List<SelectItem> selectItems) {if (CollectionUtils.isEmpty(selectItems))return;if (selectItems.size() == 1) {SelectItem item = selectItems.get(0);if (item instanceof AllColumns || item instanceof AllTableColumns)return;}selectItems.add(new SelectExpressionItem(new Column(tenantLineHandler.getTenantIdColumn(tableName))));}/*** 處理 PlainSelect*/protected void processPlainSelect(PlainSelect plainSelect) {……}/*** 處理where條件內的子查詢** @param where where 條件*/protected void processWhereSubSelect(Expression where) {……}protected void processSelectItem(SelectItem selectItem) {……}/*** 處理函數* <p>支持: 1. select fun(args..) 2. select fun1(fun2(args..),args..)<p>* <p> fixed gitee pulls/141</p>** @param function*/protected void processFunction(Function function) {ExpressionList parameters = function.getParameters();if (parameters != null) {parameters.getExpressions().forEach(expression -> {if (expression instanceof SubSelect) {processSelectBody(((SubSelect) expression).getSelectBody());} else if (expression instanceof Function) {processFunction((Function) expression);}});}}/*** 處理子查詢等*/protected void processFromItem(FromItem fromItem) {……}/*** 處理 joins** @param joins join 集合*/private void processJoins(List<Join> joins) {……}/*** 處理聯接語句*/protected void processJoin(Join join) {……}/*** 處理條件*/protected Expression builderExpression(Expression currentExpression, Table table) {EqualsTo equalsTo = new EqualsTo();equalsTo.setLeftExpression(this.getAliasColumn(table));equalsTo.setRightExpression(tenantLineHandler.getTenantId(table.getName()));if (currentExpression == null) {return equalsTo;}if (currentExpression instanceof OrExpression) {return new AndExpression(new Parenthesis(currentExpression), equalsTo);} else {return new AndExpression(currentExpression, equalsTo);}}/*** 租戶字段別名設置* <p>tenantId 或 tableAlias.tenantId</p>** @param table 表對象* @return 字段*/protected Column getAliasColumn(Table table) {StringBuilder column = new StringBuilder();if (table.getAlias() != null) {column.append(table.getAlias().getName()).append(StringPool.DOT);}column.append(tenantLineHandler.getTenantIdColumn(table.getName()));return new Column(column.toString());}@Overridepublic void setProperties(Properties properties) {PropertyMapper.newInstance(properties).whenNotBlank("tenantLineHandler",ClassUtils::newInstance, this::setTenantLineHandler);}
}
4、注入自定義租戶插件
在mybatis-plus配置類中注入對應租戶插件及其他數據權限插件等。
package com.infypower.vpp.config;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.DataPermissionInterceptor;
import com.infypower.vpp.security.permission.*;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** 配置文件* @author endcy* @since 2025/9/9*/
@Slf4j
@Configuration
@RequiredArgsConstructor
public class MybatisPlusConfig {private final FixTenantLineInnerInterceptor tenantLineInnerInterceptor;/*** admin模塊MP插件* 多租戶插件、數據權限插件*/@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 租戶代理interceptor.addInnerInterceptor(new FixTenantLineInnerInterceptor(tenantLineInnerInterceptor));// 其他數據權限,根據用戶配置進行數據權限控制DataPermissionInterceptor xxxPermissionInterceptor = new ResourceUserPermissionInterceptor();resourceUserPermissionInterceptor.setDataPermissionHandler(new XxxPermissionHandler());interceptor.addInnerInterceptor(xxxPermissionInterceptor);return interceptor;}}
其他配置保持不變。
上述getTenantId和getTenantIdColumn即核心實現,根據不同的用戶類型,賦不同的租戶字段標識和租戶id信息。相比于網上其他多字段租戶方案,本方案不依賴額外配置,不需要復雜解析,應該是最簡潔且拓展最為便捷的方式。