image.png
2. 解决思路
⽤户登录成功，查询该⽤户组织结构ID orgId及其⼦孙部门ID orgIds，存储这些⽤户信息到集中缓存redis中⽤户访问接⼝时，根据token从redis取出⽤户信息设置到线程变量ThreadLocal中
编写JPA（Mybatis同理），为符合条件的SQL进⾏解析并修改，筛选组织数据
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 source.jdbc.spi.StatementInspector;
import java.util.ArrayList;
import java.util.List;
/**
* 参考Mybatis-Plus插件中的TenantSqlParser进⾏组织ID解析处理，其实现为使⽤jsqlparser对sql进⾏解析，拼装SQL语句 *
* @author wangqichang
* @since 2020/1/12
*/
@Slf4j
@Data
@Accessors(chain = true)
@SuppressWarnings("ALL")
public class OrganizationInterceptor implements StatementInspector {
private String orgId;
private List<String> orgIds;
private List<String> orgTables;
private String orgIdColumn = "org_id";
/**
* 重写StatementInspector的inspect接⼝，参数为hibernate处理后的原始SQL，返回值为我们修改后的SQL
*
* @param sql
* @return
*/
@Override
public String inspect(String sql) {
try {
/**
* 未登录⽤户，系统⽤户不做解析
*/
CurrentUser current = UserContext.current();
if (UserContext.current() == null || UserContext.current().getAdministrator()) {
return null;
}
/**
* 初始化需要进⾏解析的组织表,
*/
if (orgTables == null) {
synchronized (OrganizationInterceptor.class) {
OrganizationProperties bean = Bean(OrganizationProperties.class);
if (bean != null) {
orgTables = Tables();
} else {
throw new RuntimeException("未能获取TenantProperties参数配置");
}
}
}
/**
* 从当前线程获取登录⽤户的所属⽤户组织ID及其⼦孙组织ID
*/
CurrentUser user = UserContext.current();
orgId = OrganizationId();
orgIds = OrganizationIds();
log.info("组织筛选解析开始，原始SQL：{}", sql);
Statements statements = CCJSqlParserUtil.parseStatements(sql);
StringBuilder sqlStringBuilder = new StringBuilder();
int i = 0;
for (Statement statement : Statements()) {
if (null != statement) {
if (null != statement) {
if (i++ > 0) {
sqlStringBuilder.append(';');
}
sqlStringBuilder.append(this.processParser(statement));
}
}
String newSql = String();
log.info("组织筛选解析结束，解析后SQL：{}", newSql);
return newSql;
} catch (Exception e) {
<("组织筛选解析失败，解析SQL异常{}", e.getMessage());
e.printStackTrace();
} finally {
orgId = null;
}
return null;
}
jpa mybatisprivate String processParser(Statement statement) {
if (statement instanceof Insert) {
this.processInsert((Insert) statement);
} else if (statement instanceof Select) {
this.processSelectBody(((Select) statement).getSelectBody());
} else if (statement instanceof Update) {
this.processUpdate((Update) statement);
} else if (statement instanceof Delete) {
this.processDelete((Delete) statement);
}
/**
* 返回处理后的SQL
*/
String();
}
/
**
* select 语句处理
*/
public void processSelectBody(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
processPlainSelect((PlainSelect) selectBody);
} else if (selectBody instanceof WithItem) {
WithItem withItem = (WithItem) selectBody;
if (SelectBody() != null) {
SelectBody());
}
} else {
SetOperationList operationList = (SetOperationList) selectBody;
if (Selects() != null && Selects().size() > 0) {
}
}
}
/**
* insert 语句处理
*/
public void processInsert(Insert insert) {
if (Table().getFullyQualifiedName())) {
if (Select() != null) {
processPlainSelect((PlainSelect) Select().getSelectBody(), true);
} else if (ItemsList() != null) {
// fixed github pull/295
ItemsList itemsList = ItemsList();
if (itemsList instanceof MultiExpressionList) {
((MultiExpressionList) itemsList).getExprList().forEach(el -> el.getExpressions().add(new StringValue(orgId)));                } else {
((ExpressionList) ItemsList()).getExpressions().add(new StringValue(orgId));
}
} else {
throw new RuntimeException("Failed to process multiple-table update, please exclude the tableName or statementId");            }
}
}
/**
* update 语句处理
*/
public void processUpdate(Update update) {
final Table table = Table();
if (FullyQualifiedName())) {
update.setWhere(this.andExpression(table, Where()));
}
}
/**
* delete 语句处理
*/
public void processDelete(Delete delete) {
if (Table().getFullyQualifiedName())) {
delete.setWhere(this.Table(), Where()));
}
}
/**
* delete update 语句 where 处理
*/
protected BinaryExpression andExpression(Table table, Expression where) {
//获得where条件表达式
EqualsTo equalsTo = new EqualsTo();
equalsTo.AliasColumn(table));
equalsTo.setRightExpression(new StringValue(orgId));
if (null != where) {
if (where instanceof OrExpression) {
return new AndExpression(equalsTo, new Parenthesis(where));
} else {
return new AndExpression(equalsTo, where);
}
}
return equalsTo;
}
/**
* 处理 PlainSelect
*/
protected void processPlainSelect(PlainSelect plainSelect) {
if (Where() != null) {
processPlainSelect(plainSelect, true);
} else {
processPlainSelect(plainSelect, false);
}
}
/**
* 处理 PlainSelect
*
* @param plainSelect ignore
* @param addColumn  是否添加租户列,insert into select语句中需要
*/
protected void processPlainSelect(PlainSelect plainSelect, boolean addColumn) {
FromItem fromItem = FromItem();
if (fromItem instanceof Table) {
Table fromTable = (Table) fromItem;
if (FullyQualifiedName())) {
//#1186 github
plainSelect.setWhere(Where(), fromTable));
if (addColumn) {
}
}
}
} else {
processFromItem(fromItem);
}
List<Join> joins = Joins();
if (joins != null && joins.size() > 0) {
joins.forEach(j -> {
processJoin(j);
RightItem());
});
}
}
/
**
* 处理⼦查询等
*/
protected void processFromItem(FromItem fromItem) {
if (fromItem instanceof SubJoin) {
SubJoin subJoin = (SubJoin) fromItem;
if (JoinList() != null) {
}
if (Left() != null) {
Left());
}
} else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
if (SelectBody() != null) {
SelectBody());
}
} else if (fromItem instanceof ValuesList) {
log.debug("Perform a subquery, if you do not give us feedback");
} else if (fromItem instanceof LateralSubSelect) {
LateralSubSelect lateralSubSelect = (LateralSubSelect) fromItem;
if (SubSelect() != null) {
SubSelect subSelect = SubSelect();
if (SelectBody() != null) {
SelectBody());
}
}
}
}
/**
* 处理联接语句
*/
protected void processJoin(Join join) {
if (RightItem() instanceof Table) {
Table fromTable = (Table) RightItem();
if (FullyQualifiedName())) {
join.setOnExpression(OnExpression(), fromTable));            }
}
}
/**
* 处理条件:
* 创建InExpression，即封装where orgId in ('','')
*/
protected Expression builderExpression(Expression currentExpression, Table table) {        final InExpression organizationExpression = new InExpression();
List<Expression> expressions = new ArrayList<>();
orgIds.forEach(organizatinId -> {
expressions.add(new StringValue(organizatinId));
});
ExpressionList expressionList = new ExpressionList(expressions);
organizationExpression.AliasColumn(table));
organizationExpression.setRightItemsList(expressionList);
Expression appendExpression  =  null;
if (!(organizationExpression instanceof SupportsOldOracleJoinSyntax)) {
appendExpression = new EqualsTo();
((EqualsTo) appendExpression).AliasColumn(table));            ((EqualsTo) appendExpression).setRightExpression(organizationExpression);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。