本来项目写好了,但是由于新的需求出现,要在一个表上增加一个字段,原先由于功能运行都是正常的,而且这个表查询用的地方比较多,只是因为新增这个功能导致原先查询这个表的条件都需要增加查询这个字段的条件,全局搜了一下,这个表的查询太多了,所以就思考有没有什么办法在一个地方添加,查询这个表会自动加上这个搜索条件,当存在这个条件时就加这个字段的搜索条件呢?
废话不说,上代码
import cn.hutool.core.util.ReflectUtil;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
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.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.springframework.stereotype.Component;
import net.sf.jsqlparser.statement.Statement;
import java.sql.Connection;
/**
* @author
* @description 当表无该字段查询条件时增加默认查询条件
*/
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Slf4j
@Component
public class MybatisDataFilterInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
//获取StatementHandler构造器
StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
// 通过反射获取delegate父类BaseStatementHandler的mappedStatement属性
MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement");
SqlCommandType commandType = mappedStatement.getSqlCommandType();
if (SqlCommandType.SELECT.equals(commandType)) {
String sqlId = mappedStatement.getId();
BoundSql boundSql = delegate.getBoundSql();
String sql = boundSql.getSql();
Statement statement = CCJSqlParserUtil.parse(sql);
Select select = (Select) statement;
PlainSelect selectBody = (PlainSelect) select.getSelectBody();
addWhere(selectBody);
ReflectUtil.setFieldValue(boundSql, "sql", statement.toString());
}
return invocation.proceed();
}
//增加条件
private void addWhere(PlainSelect selectBody){
try{
Table fromItem = (Table) selectBody.getFromItem();
String name = fromItem.getName();
if (name.indexOf("表名") != -1) {
String stringExpression = "";
try{
EqualsTo where = (EqualsTo) selectBody.getWhere();
stringExpression = where.getStringExpression();
}catch (Exception e){
stringExpression = selectBody.getWhere().toString();
}
//如果字段搜索条件为空则搜索字段为空或指定数据
StringBuilder sqlFilter = new StringBuilder(128);
if (stringExpression.indexOf("字段名") == -1) {
sqlFilter.append("(表名.字段名!='1' or 表名.字段名 is null) ");
buildWhereClause(selectBody, sqlFilter.toString());
}
}
}catch (Exception e){
//多表查询时由于不是最后一层,获取不到Table,继续获取子表
SubSelect ss = (SubSelect)selectBody.getFromItem();
PlainSelect subSelect = (PlainSelect) ss.getSelectBody();
addWhere(subSelect);
}
}
private void buildWhereClause(PlainSelect select, String dataFilter) throws JSQLParserException {
if (select.getWhere() == null) {
select.setWhere(CCJSqlParserUtil.parseCondExpression(dataFilter));
} else {
AndExpression and = new AndExpression(
CCJSqlParserUtil.parseCondExpression(dataFilter), select.getWhere());
select.setWhere(and);
}
}
}
借鉴这篇mybatis-plus自定义拦截器实现数据权限学习文章进行改造