package com.nuzar.fcms.common.core.interceptor.mybatis;
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 com.nuzar.cloud.mapper.handler.DataScopeHandler;
import lombok.Data;
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.*;
/**
* @description: <p>
*
* </p>
* @author: ZhangZh
* @time: 2023/5/30
*/
@Data
public class TermCodeInterceptor extends JsqlParserSupport implements InnerInterceptor {
private TemrCodeHandler temrCodeHandler;
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = PluginUtils.mpBoundSql(boundSql);
mpBs.sql(parserSingle(mpBs.sql(), null));
}
@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.INSERT || sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
if (sct == SqlCommandType.UPDATE || sct == SqlCommandType.DELETE) {
if (InterceptorIgnoreHelper.willIgnoreTenantLine(ms.getId())) return;
PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();
mpBs.sql(parserMulti(mpBs.sql(), null));
}
}
@Override
protected void processSelect(Select select, int index, String sql, Object obj) {
processSelectBody(select.getSelectBody());
List<WithItem> withItemsList = select.getWithItemsList();
if (!CollectionUtils.isEmpty(withItemsList)) {
withItemsList.forEach(this::processSelectBody);
}
}
protected void processSelectBody(SelectBody selectBody) {
if (selectBody == null) {
return;
}
if (selectBody instanceof PlainSelect) {
processPlainSelect((PlainSelect) selectBody);
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
processSelectBody(withItem.getSubSelect().getSelectBody());
} else {
SetOperationList operationList = (SetOperationList) selectBody;
List<SelectBody> selectBodys = operationList.getSelects();
if (CollectionUtils.isNotEmpty(selectBodys)) {
selectBodys.forEach(this::processSelectBody);
}
}
}
@Override
protected void processInsert(Insert insert, int index, String sql, Object obj) {
return;
}
/**
* update 语句处理
*/
@Override
protected void processUpdate(Update update, int index, String sql, Object obj) {
final Table table = update.getTable();
if (temrCodeHandler.ignoreTable(table.getName())) {
// 过滤退出执行
return;
}
update.setWhere(this.andExpression(table, update.getWhere()));
}
/**
* delete 语句处理
*/
@Override
protected void processDelete(Delete delete, int index, String sql, Object obj) {
if (temrCodeHandler.ignoreTable(delete.getTable().getName())) {
// 过滤退出执行
return;
}
delete.setWhere(this.andExpression(delete.getTable(), delete.getWhere()));
}
/**
* delete update 语句 where 处理
*/
protected BinaryExpression andExpression(Table table, Expression where) {
//获得where条件表达式
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(this.getAliasColumn(table));
inExpression.setRightItemsList(temrCodeHandler.getTermCodeList());
if (null != where) {
if (where instanceof OrExpression) {
return new AndExpression(inExpression, new Parenthesis(where));
} else {
return new AndExpression(inExpression, where);
}
}
return new AndExpression(inExpression, null);
}
/**
* 处理 insert into select
* <p>
* 进入这里表示需要 insert 的表启用了多租户,则 select 的表都启动了
*
* @param selectBody SelectBody
*/
protected void processInsertSelect(SelectBody selectBody) {
PlainSelect plainSelect = (PlainSelect) selectBody;
FromItem fromItem = plainSelect.getFromItem();
if (fromItem instanceof Table) {
// fixed gitee pulls/141 duplicate update
processPlainSelect(plainSelect);
appendSelectItem(plainSelect.getSelectItems());
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
appendSelectItem(plainSelect.getSelectItems());
processInsertSelect(subSelect.getSelectBody());
}
}
/**
* 追加 SelectItem
*
* @param selectItems SelectItem
*/
protected void appendSelectItem(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(temrCodeHandler.getTermCodeColumn())));
}
/**
* 处理 PlainSelect
*/
protected void processPlainSelect(PlainSelect plainSelect) {
FromItem fromItem = plainSelect.getFromItem();
Expression where = plainSelect.getWhere();
processWhereSubSelect(where);
if (fromItem instanceof Table) {
Table fromTable = (Table) fromItem;
if (!temrCodeHandler.ignoreTable(fromTable.getName())) {
//#1186 github
plainSelect.setWhere(builderExpression(where, fromTable));
}
} else {
processFromItem(fromItem);
}
//#3087 github
List<SelectItem> selectItems = plainSelect.getSelectItems();
if (CollectionUtils.isNotEmpty(selectItems)) {
selectItems.forEach(this::processSelectItem);
}
List<Join> joins = plainSelect.getJoins();
if (CollectionUtils.isNotEmpty(joins)) {
processJoins(joins);
}
}
/**
* 处理where条件内的子查询
* <p>
* 支持如下:
* 1. in
* 2. =
* 3. >
* 4. <
* 5. >=
* 6. <=
* 7. <>
* 8. EXISTS
* 9. NOT EXISTS
* <p>
* 前提条件:
* 1. 子查询必须放在小括号中
* 2. 子查询一般放在比较操作符的右边
*
* @param where where 条件
*/
protected void processWhereSubSelect(Expression where) {
if (where == null) {
return;
}
if (where instanceof FromItem) {
processFromItem((FromItem) where);
return;
}
if (where.toString().indexOf("SELECT") > 0) {
// 有子查询
if (where instanceof BinaryExpression) {
// 比较符号 , and , or , 等等
BinaryExpression expression = (BinaryExpression) where;
processWhereSubSelect(expression.getLeftExpression());
processWhereSubSelect(expression.getRightExpression());
} else if (where instanceof InExpression) {
// in
InExpression expression = (InExpression) where;
ItemsList itemsList = expression.getRightItemsList();
if (itemsList instanceof SubSelect) {
processSelectBody(((SubSelect) itemsList).getSelectBody());
}
} else if (where instanceof ExistsExpression) {
// exists
ExistsExpression expression = (ExistsExpression) where;
processWhereSubSelect(expression.getRightExpression());
} else if (where instanceof NotExpression) {
// not exists
NotExpression expression = (NotExpression) where;
processWhereSubSelect(expression.getExpression());
} else if (where instanceof Parenthesis) {
Parenthesis expression = (Parenthesis) where;
processWhereSubSelect(expression.getExpression());
}
}
}
protected void processSelectItem(SelectItem selectItem) {
if (selectItem instanceof SelectExpressionItem) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
if (selectExpressionItem.getExpression() instanceof SubSelect) {
processSelectBody(((SubSelect) selectExpressionItem.getExpression()).getSelectBody());
} else if (selectExpressionItem.getExpression() instanceof Function) {
processFunction((Function) selectExpressionItem.getExpression());
}
}
}
/**
* 处理函数
* <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) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (subJoin.getJoinList() != null) {
processJoins(subJoin.getJoinList());
}
if (subJoin.getLeft() != null) {
processFromItem(subJoin.getLeft());
}
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
} else if (fromItem instanceof ValuesList) {
logger.debug("Perform a subquery, if you do not give us feedback");
} else if (fromItem instanceof LateralSubSelect) {
LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
if (lateralSubSelect.getSubSelect() != null) {
SubSelect subSelect = lateralSubSelect.getSubSelect();
if (subSelect.getSelectBody() != null) {
processSelectBody(subSelect.getSelectBody());
}
}
}
}
/**
* 处理 joins
*
* @param joins join 集合
*/
private void processJoins(List<Join> joins) {
//对于 on 表达式写在最后的 join,需要记录下前面多个 on 的表名
Deque<Table> tables = new LinkedList<>();
for (Join join : joins) {
// 处理 on 表达式
FromItem fromItem = join.getRightItem();
if (fromItem instanceof Table) {
Table fromTable = (Table) fromItem;
// 获取 join 尾缀的 on 表达式列表
Collection<Expression> originOnExpressions = join.getOnExpressions();
// 正常 join on 表达式只有一个,立刻处理
if (originOnExpressions.size() == 1) {
processJoin(join);
continue;
}
// 当前表是否忽略
boolean needIgnore = temrCodeHandler.ignoreTable(fromTable.getName());
// 表名压栈,忽略的表压入 null,以便后续不处理
tables.push(needIgnore ? null : fromTable);
// 尾缀多个 on 表达式的时候统一处理
if (originOnExpressions.size() > 1) {
Collection<Expression> onExpressions = new LinkedList<>();
for (Expression originOnExpression : originOnExpressions) {
Table currentTable = tables.poll();
if (currentTable == null) {
onExpressions.add(originOnExpression);
} else {
onExpressions.add(builderExpression(originOnExpression, currentTable));
}
}
join.setOnExpressions(onExpressions);
}
} else {
// 处理右边连接的子表达式
processFromItem(fromItem);
}
}
}
/**
* 处理联接语句
*/
protected void processJoin(Join join) {
if (join.getRightItem() instanceof Table) {
Table fromTable = (Table) join.getRightItem();
if (temrCodeHandler.ignoreTable(fromTable.getName())) {
// 过滤退出执行
return;
}
// 走到这里说明 on 表达式肯定只有一个
Collection<Expression> originOnExpressions = join.getOnExpressions();
List<Expression> onExpressions = new LinkedList<>();
onExpressions.add(builderExpression(originOnExpressions.iterator().next(), fromTable));
join.setOnExpressions(onExpressions);
}
}
/**
* 处理条件
*/
protected Expression builderExpression(Expression currentExpression, Table table) {
if (this.getTemrCodeHandler().getTermCodeList() == null && this.getTemrCodeHandler().getTermCodeColumn() == null) {
return currentExpression;
} else {
InExpression inExpression = new InExpression();
inExpression.setLeftExpression(this.getAliasColumn(table));
inExpression.setRightItemsList(temrCodeHandler.getTermCodeList());
if (currentExpression == null) {
return inExpression;
}
if (currentExpression instanceof OrExpression) {
return new AndExpression(new Parenthesis(currentExpression), inExpression);
} else {
return new AndExpression(currentExpression, inExpression);
}
}
}
/**
* 租户字段别名设置
* <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(temrCodeHandler.getTermCodeColumn());
return new Column(column.toString());
}
// @Override
// public void setProperties(Properties properties) {
// PropertyMapper.newInstance(properties).whenNotBlank("temrCodeHandler",
// ClassUtils::newInstance, this::settemrCodeHandler);
// }
public interface TemrCodeHandler {
default String getTermCodeColumn() {
return "term_code";
}
default boolean ignoreTable(String tableName) {
return false;
}
ItemsList getTermCodeList();
}
}
拦截器
fcms:
tenant:
enable: true
idgen:
enabled: true
mybatis:
dbSupportAutoKey: true #mysql启用此配置
tenant:
enable: true
ignore-tenant-type: port,terminal #忽略的客户类型代码
term:
enable: true
include-tables:
- STD_SCHEDULES
- STD_VOYAGES
- STD_BERTHES
- WEB_PAS_PLANS
- CBS_CHARGE_BILLS
- CBS_CHARGE_INVOICES
- CBS_INVOICELIST_VW
- CBS_INCOME_CAMT_VW
ignore-tables:
- STD_REF_CODES
- DUAL
term-column: TERM_CODE
tenant-channel-type: TERM_CODE
suit-tenant-type: terminal #适用的客户类型代码
permit-all-code: default,SUPER,LJ,HFG #拥有所有数据权限的客户代码
package com.nuzar.fcms.common.core.config;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import java.util.List;
/**
* @description: <p>
*
* </p>
* @author: ZhangZh
* @time: 2023/5/30
*/
@ConfigurationProperties(
prefix = "fcms.mybatis"
)
@Data
public class FcmsMybatisProperties {
public static final String PREFIX = "fcms.mybatis";
private Term term;
private Tenant tenant;
private Boolean dbSupportAutoKey = false; // 数据库是否支持自动主键,oracle false, mysql true
@Data
public static class Tenant {
private Boolean enable = false;
private List<String> ignoreTenantType;
}
@Data
public static class Term {
private Boolean enable = false;
private String termColumn = "term_code";
private List<String> includeTables;
private List<String> ignoreTables;
private List<String> tenantChannelType;
private List<String> suitTenantType;
private List<String> permitAllCode;
}
}
@Bean
public FcmsMybatisInterceptor fcmsMybatisPlusInterceptor(final FcmsMybatisProperties mybatisProperties, final NuzarMybatisConfig mybatisConfig, final EnvSupplier envSupplier, MybatisPlusInterceptor mybatisPlusInterceptor) {
FcmsMybatisInterceptor interceptor = new FcmsMybatisInterceptor(mybatisProperties);
interceptor.init(mybatisConfig, envSupplier, mybatisPlusInterceptor);
return interceptor;
}
package com.nuzar.fcms.common.core.interceptor.mybatis;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;
import com.nuzar.cloud.common.exception.BizException;
import com.nuzar.cloud.common.utils.CollectionUtils;
import com.nuzar.cloud.property.NuzarMybatisConfig;
import com.nuzar.cloud.service.EnvSupplier;
import com.nuzar.common.security5.common.util.SecurityUtils;
import com.nuzar.fcms.common.core.business.utils.TermUtils;
import com.nuzar.fcms.common.core.business.utils.Utils;
import com.nuzar.fcms.common.core.config.FcmsMybatisProperties;
import com.nuzar.fcms.common.core.context.MybatisTenantContext;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ItemsList;
import java.util.LinkedList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;
/**
* @description: <p>
*
* </p>
* @author: ZhangZh
* @time: 2023/5/30
*/
@Slf4j
public class FcmsMybatisInterceptor {
private FcmsMybatisProperties mybatisProperties;
public FcmsMybatisInterceptor(FcmsMybatisProperties mybatisProperties) {
this.mybatisProperties = mybatisProperties;
}
public void init(final NuzarMybatisConfig mybatisConfig, final EnvSupplier envSupplier, MybatisPlusInterceptor mybatisPlusInterceptor) {
// 替换原TenantLineInnerInterceptor
TenantListLineInnerInterceptor tenantLineInnerInterceptor = new TenantListLineInnerInterceptor();
if (mybatisConfig.getTenant().getEnabled() && mybatisProperties.getTenant().getEnable()) {
tenantLineInnerInterceptor.setTenantListHandler(new TenantListLineInnerInterceptor.TenantListHandler() {
public ItemsList getTenantIdList() {
// List<String> tenantIdList = new ArrayList<>();
try {
String tenantId = SecurityUtils.getTenant();
return new ExpressionList(Utils.getComposedTenantIdList(tenantId).stream()
.map(StringValue::new).collect(Collectors.toList()));
} catch (Exception e) {
log.error(e.getMessage());
throw new BizException("租户权限加载失败");
}
}
});
tenantLineInnerInterceptor.setTenantLineHandler(new TenantLineHandler() {
public String getTenantIdColumn() {
return mybatisConfig.getTenant().getTenantColumn();
}
public boolean ignoreTable(String tableName) {
//是否忽略租户
if (Objects.nonNull(MybatisTenantContext.get())){
log.info("是否做租户隔离:{}",MybatisTenantContext.get());
return MybatisTenantContext.get();
}
if (mybatisConfig.getTenant().getPermitAllCode() != null
&& mybatisConfig.getTenant().getPermitAllCode().contains(envSupplier.supply())
|| Utils.isAdminTenant(SecurityUtils.getTenant())) {
return true;
} else if (mybatisConfig.getTenant().getIncludeTables() != null) {
return mybatisConfig.getTenant().getIncludeTables() == null
|| !mybatisConfig.getTenant().getIncludeTables().contains(tableName);
} else if (mybatisConfig.getTenant().getIgnoreTables() == null) {
return false;
} else {
return mybatisConfig.getTenant().getIgnoreTables() != null
&& mybatisConfig.getTenant().getIgnoreTables().contains(tableName);
}
}
public Expression getTenantId() {
return new StringValue(envSupplier.supply());
}
});
}
TermCodeInterceptor termCodeInterceptor = new TermCodeInterceptor();
if (mybatisProperties.getTerm().getEnable()) {
log.info(mybatisProperties.getTerm().toString());
termCodeInterceptor.setTemrCodeHandler(new TermCodeInterceptor.TemrCodeHandler() {
@Override
public ItemsList getTermCodeList() {
List<String> list = TermUtils.getTermScopeList();
if (CollectionUtils.isEmpty(list)) {
list.add("default");
}
return new ExpressionList(list.stream()
.map(StringValue::new).collect(Collectors.toList()));
}
@Override
public String getTermCodeColumn() {
return mybatisProperties.getTerm().getTermColumn()!=null ? mybatisProperties.getTerm().getTermColumn()
: TermCodeInterceptor.TemrCodeHandler.super.getTermCodeColumn();
}
@Override
public boolean ignoreTable(String tableName) {
if (mybatisProperties.getTerm().getPermitAllCode() != null
&& mybatisProperties.getTerm().getPermitAllCode().contains(envSupplier.supply())){
return true;
} else if (mybatisProperties.getTerm().getIncludeTables()!=null) {
return !(mybatisProperties.getTerm().getIncludeTables().contains(tableName)
&& TermUtils.suitTenantTypeForScope(mybatisProperties.getTerm().getSuitTenantType()));
} else if (mybatisProperties.getTerm().getIncludeTables()!=null) {
return mybatisProperties.getTerm().getIgnoreTables().contains(tableName)
|| !TermUtils.suitTenantTypeForScope(mybatisProperties.getTerm().getSuitTenantType());
}
return !TermUtils.suitTenantTypeForScope(mybatisProperties.getTerm().getSuitTenantType());
}
// private boolean suitTenantType(List<String> suitTenantTypes) {
// if (CollectionUtils.isEmpty(suitTenantTypes)) {
// return true;
// }
// List<String> tenantTypes = Utils.getTenantTypes(SecurityUtils.getTenant());
// return tenantTypes.stream().anyMatch(t->{
// return suitTenantTypes.contains(t);
// });
// }
});
}
// 原顺序添加interceptors,最后加上termCodeInterceptor
List<InnerInterceptor> interceptors = new LinkedList<>(mybatisPlusInterceptor.getInterceptors());
int i = 0;
for (;i<interceptors.size();i++) {
InnerInterceptor interceptor = interceptors.get(i);
if (interceptor instanceof TenantLineInnerInterceptor) {
break;
}
}
if (i<interceptors.size()) {
interceptors.set(i, tenantLineInnerInterceptor);
mybatisPlusInterceptor.setInterceptors(interceptors);
if (mybatisProperties.getTerm().getEnable()) {
interceptors.add(i, termCodeInterceptor);
}
} else if (mybatisProperties.getTerm().getEnable()) {
interceptors.add(0, termCodeInterceptor);
}
// 数据库兼容性keyGenerator拦截处理
mybatisPlusInterceptor.addInnerInterceptor(new KeyGeneratorResetInterceptor(mybatisProperties));
}
}