java 对mybatis拦截Interceptor进行权限控制(条件修改),入参修改,返回修改

使用jsqlparser对sql解析拼装

基础准备

<dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.5</version><scope>compile</scope>
</dependency>
<dependency><groupId>commons-beanutils</groupId><artifactId>commons-beanutils</artifactId><version>1.9.4</version>
</dependency>

import lombok.Data;
/*** 需要拦截或修改的值*/
@Data
public class InitiateInterceptField {public static final String deptId = "deptId";public static final String createBy = "createBy";public static final String updateBy = "updateBy";public static final String dept_id = "dept_id";public static final String create_by = "create_by";public static final String update_by = "update_by";public static final String deptName = "deptName";public static final String createName = "createName";public static final String updateName = "updateName";}

import cn.hutool.core.thread.ThreadFactoryBuilder;
import lombok.Data;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Component;import java.util.concurrent.*;@Data
@Component
public class DisclosureThreadPool {@Bean("mybatisInterceptorExecutorService")public ExecutorService entranceAlerterThread() {ThreadFactory namedThreadFactory = new ThreadFactoryBuilder().setNamePrefix("thread-mybatisInterceptorExecutorService-runner-%d").build();return new ThreadPoolExecutor(4, 36, 200L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(204800), namedThreadFactory);}}

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;import java.lang.reflect.Proxy;
import java.util.Properties;/*** 插件工具类*/
public final class PluginUtils {public static final String DELEGATE_BOUNDSQL_SQL = "delegate.boundSql.sql";private PluginUtils() {// to do nothing}/*** 获得真正的处理对象,可能多层代理.*/@SuppressWarnings("unchecked")public static <T> T realTarget(Object target) {if (Proxy.isProxyClass(target.getClass())) {MetaObject metaObject = SystemMetaObject.forObject(target);return realTarget(metaObject.getValue("h.target"));}return (T) target;}/*** 根据 key 获取 Properties 的值*/public static String getProperty(Properties properties, String key) {String value = properties.getProperty(key);return StringUtils.isBlank(value) ? null : value;}
}

数据权限控制

比如根据部门权限动态修改sql

import cn.hutool.core.util.ObjectUtil;
import com.disclosure.common.core.domain.entity.SysRole;
import com.disclosure.common.core.domain.entity.SysUser;
import com.disclosure.common.core.domain.model.LoginUser;
import com.disclosure.common.utils.SecurityUtils;
import lombok.AllArgsConstructor;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
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.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;import java.sql.Connection;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.ExecutorService;/*** @author <p>* mybatis 数据权限拦截器*/
@Slf4j
@AllArgsConstructor
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare" , args = {Connection.class, Integer.class})})
public class AuthorityScopeInterceptor implements Interceptor {private final ExecutorService executorService;@Override@SneakyThrowspublic Object intercept(Invocation invocation) {StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(statementHandler);MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");//指定范围拦截if (!mappedStatement.getId().startsWith("com.disclosure.business")) return invocation.proceed();//只拦截 三种类型if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())&& !SqlCommandType.UPDATE.equals(mappedStatement.getSqlCommandType())&& !SqlCommandType.DELETE.equals(mappedStatement.getSqlCommandType())) return invocation.proceed();//获取用户属性LoginUser loginUser = SecurityUtils.getLoginUser();SysUser currentUser;if (ObjectUtil.isNull(loginUser) || ObjectUtil.isNull(currentUser = loginUser.getUser()))throw new RuntimeException("获取不到用户数据");// 如果是超级管理员，则不过滤数据if (currentUser.isAdmin()) return invocation.proceed();List<SysRole> roles = currentUser.getRoles();if (roles.isEmpty()) throw new RuntimeException("角色缺失");AuthorityCategory authorityCategory = new AuthorityCategory(executorService);//获取多角色的权限范围for (SysRole role : roles) {switch (role.getDataScope()) {case AuthorityCategory.DATA_SCOPE_ALL:return invocation.proceed();case AuthorityCategory.DATA_SCOPE_CUSTOM:authorityCategory.addDataScopeCustom(role.getRoleId());break;case AuthorityCategory.DATA_SCOPE_DEPT:authorityCategory.addDataScopeDept(currentUser.getDeptId());break;case AuthorityCategory.DATA_SCOPE_DEPT_AND_CHILD:authorityCategory.addDataScopeDeptAndChild(currentUser.getDeptId());break;default:authorityCategory.addDataScopeSelf(currentUser.getUserId());break;}}authorityCategory.removeRepeatDept();BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");String originalSql = boundSql.getSql();//生成新sqloriginalSql = authorityCategory.getNewSql(originalSql).toString();metaObject.setValue("delegate.boundSql.sql" , originalSql);return invocation.proceed();}/*** 生成拦截对象的代理** @param target 目标对象* @return 代理对象*/@Overridepublic Object plugin(Object target) {if (target instanceof StatementHandler) {return Plugin.wrap(target, this);}return target;}/*** mybatis配置的属性** @param properties mybatis配置的属性*/@Overridepublic void setProperties(Properties properties) {}
}

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import lombok.Data;
import lombok.SneakyThrows;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.FromItem;
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.update.Update;import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Future;
import java.util.stream.Collectors;
import java.util.stream.Stream;import static com.disclosure.business.config.InitiateInterceptField.create_by;
import static com.disclosure.business.config.InitiateInterceptField.dept_id;@Data
public class AuthorityCategory {private final ExecutorService executorService;/*** 全部*/public static final String DATA_SCOPE_ALL = "1";/*** 自定数据权限*/public static final String DATA_SCOPE_CUSTOM = "2";/*** 部门数据权限*/public static final String DATA_SCOPE_DEPT = "3";/*** 部门及以下数据权限*/public static final String DATA_SCOPE_DEPT_AND_CHILD = "4";/*** 仅本人数据权限*/public static final String DATA_SCOPE_SELF = "5";private ArrayList<Long> dataScopeCustomList;private ArrayList<Long> dataScopeDeptList;private ArrayList<Long> dataScopeDeptAndChildList;private ArrayList<Long> dataScopeSelfList;private String fromItemName;public AuthorityCategory(ExecutorService executorService) {this.executorService = executorService;this.dataScopeCustomList = new ArrayList<>();this.dataScopeDeptList = new ArrayList<>();this.dataScopeDeptAndChildList = new ArrayList<>();this.dataScopeSelfList = new ArrayList<>();this.fromItemName = "";}public void addDataScopeSelf(Long dataScopeSelf) {this.dataScopeSelfList.add(dataScopeSelf);}public void addDataScopeDeptAndChild(Long dataScopeDeptAndChild) {this.dataScopeDeptAndChildList.add(dataScopeDeptAndChild);}public void addDataScopeDept(Long dataScopeDept) {this.dataScopeDeptList.add(dataScopeDept);}public void addDataScopeCustom(Long dataScopeCustom) {this.dataScopeCustomList.add(dataScopeCustom);}public void removeRepeatDept() {dataScopeDeptList.removeAll(dataScopeDeptAndChildList);}@SneakyThrowspublic Statement getNewSql(String originalSql) {//解析只针对主表Statement statement = CCJSqlParserUtil.parse(new StringReader(originalSql));if (statement instanceof Select) {Select select = (Select) statement;SelectBody selectBody = select.getSelectBody();PlainSelect plainSelect = (PlainSelect) selectBody;plainSelect.setWhere(spliceSQl(plainSelect.getWhere(), plainSelect.getFromItem()));return select;} else if (statement instanceof Delete) {Delete delete = (Delete) statement;delete.setWhere(spliceSQl(delete.getWhere(), delete.getTable()));return delete;} else if (statement instanceof Update) {Update update = (Update) statement;update.setWhere(spliceSQl(update.getWhere(), update.getTable()));return update;}return statement;}private Expression spliceSQl(Expression expression, FromItem fromItem) {List<Expression> expressionList = this.newWhere(fromItem);if (ObjectUtil.isNull(expression)) {return getOr(expressionList);} else {AndExpression andExpression = new AndExpression();andExpression.setLeftExpression(expression);andExpression.setRightExpression(new Column("(" + getOr(expressionList) + ")"));return expression;}}/*** 处理 四种权限类型*/@SneakyThrowsprivate List<Expression> newWhere(FromItem fromItem) {handleFromItemName(fromItem);Future<Expression> handleDataScopeSelfLisSubmit = executorService.submit(this::handleDataScopeSelfList);Future<Expression> handleDataScopeDeptListSubmit = executorService.submit(this::handleDataScopeDeptList);Future<Expression> handleDataScopeCustomListSubmit = executorService.submit(this::handleDataScopeCustomList);Future<Expression> handleDataScopeDeptAndChildListSubmit = executorService.submit(this::handleDataScopeDeptAndChildList);return Stream.of(handleDataScopeSelfLisSubmit.get(),handleDataScopeDeptListSubmit.get(),handleDataScopeCustomListSubmit.get(),handleDataScopeDeptAndChildListSubmit.get()).filter(ObjectUtil::isNotNull).collect(Collectors.toList());}/*** 获取名字或别名*/private void handleFromItemName(FromItem fromItem) {if (ObjectUtil.isNull(fromItem.getAlias())) {this.fromItemName = fromItem.toString();} else {this.fromItemName = fromItem.getAlias().getName();}}/*** 拼接仅本人数据权限sql*/private Expression handleDataScopeSelfList() {if (!this.dataScopeSelfList.isEmpty()) {InExpression inExpression = new InExpression();inExpression.setLeftExpression(new Column(new Table(this.fromItemName), create_by));inExpression.setRightExpression(new Column("(" + CollUtil.join(this.dataScopeSelfList, ",") + ")"));return new Column("(" + inExpression + ")");}return null;}/*** 拼接仅部门数据权限sql*/private Expression handleDataScopeDeptList() {if (!this.dataScopeDeptList.isEmpty()) {InExpression inExpression = new InExpression();inExpression.setLeftExpression(new Column(new Table(this.fromItemName), dept_id));inExpression.setRightExpression(new Column("(" + CollUtil.join(this.dataScopeDeptList, ",") + ")"));return new Column("(" + inExpression + ")");}return null;}/*** 拼接自定义数据权限sql*/private Expression handleDataScopeCustomList() {if (!this.dataScopeCustomList.isEmpty()) {InExpression inExpression = new InExpression();inExpression.setLeftExpression(new Column(new Table(this.fromItemName), dept_id));inExpression.setRightExpression(new Column("(SELECT dept_id FROM sys_role_dept WHERE role_id in (" + CollUtil.join(this.dataScopeCustomList, ",") + "))"));return new Column("(" + inExpression + ")");}return null;}/*** 拼接部门及以下数据权限sql*/private Expression handleDataScopeDeptAndChildList() {if (!this.dataScopeDeptAndChildList.isEmpty()) {InExpression inExpression = new InExpression();inExpression.setLeftExpression(new Column(new Table(this.fromItemName), dept_id));inExpression.setRightExpression(new Column("(SELECT dept_id FROM sys_dept WHERE dept_id in (" + CollUtil.join(this.dataScopeDeptAndChildList, ",") + ") or " + this.dataScopeDeptAndChildList.parallelStream().map(n -> "find_in_set(" + n + ",ancestors)").collect(Collectors.joining(" or ")) + ")"));return new Column("(" + inExpression + ")");}return null;}private Expression getOr(List<Expression> expressions) {if (expressions.size() == 1) return expressions.get(0);String collect = expressions.parallelStream().map(Object::toString).collect(Collectors.joining(" or "));return new Column(collect);}}

入参修改

比如修改机构字段,删除机构字段,填充新建用户

import cn.hutool.core.util.ObjectUtil;
import com.disclosure.common.core.domain.entity.SysUser;
import com.disclosure.common.core.domain.model.LoginUser;
import com.disclosure.common.utils.SecurityUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;import java.io.StringReader;
import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;import static com.disclosure.business.config.InitiateInterceptField.*;@Slf4j
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare" , args = {Connection.class, Integer.class})})
public class AuthorityUserFillerInterceptor implements Interceptor {//清理指定映射字段private final String[] deleteParameter = {deptId, createBy, updateBy};private final String[] deleteColumn = {dept_id, create_by, update_by};@Override@SneakyThrowspublic Object intercept(Invocation invocation) {StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(statementHandler);MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");if (!mappedStatement.getId().startsWith("com.disclosure.business")) return invocation.proceed();if (!SqlCommandType.UPDATE.equals(mappedStatement.getSqlCommandType())&& !SqlCommandType.INSERT.equals(mappedStatement.getSqlCommandType())) {return invocation.proceed();}LoginUser loginUser = SecurityUtils.getLoginUser();SysUser currentUser;if (ObjectUtil.isNull(loginUser) || ObjectUtil.isNull(currentUser = loginUser.getUser()))throw new RuntimeException("获取不到用户数据");BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");String originalSql = boundSql.getSql();Statement statement = CCJSqlParserUtil.parse(new StringReader(originalSql));List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();//删除参数映射以免出现下列问题//        Parameter index out of range (X > number of parameters, which is X)List<String> deleteParameterList = Arrays.asList(deleteParameter);List<ParameterMapping> deptIdParameterMappings = parameterMappings.stream().parallel().map(n -> {if (deleteParameterList.contains(n.getProperty())) {return n;}return null;}).filter(ObjectUtil::isNotNull).collect(Collectors.toList());if (!deptIdParameterMappings.isEmpty()) {parameterMappings.removeAll(deptIdParameterMappings);}if (statement instanceof Update) {Update update = (Update) CCJSqlParserUtil.parse(originalSql);ArrayList<UpdateSet> updateSets = update.getUpdateSets();//删除sql对应值updateCleanse(updateSets);//填充信息Map<String, Object> updateParameter = new HashMap<String, Object>() {{put(update_by, currentUser.getUserId());}};setUpdateParameter(updateSets, updateParameter);originalSql = update.toString();} else if (statement instanceof Insert) {Insert insert = (Insert) CCJSqlParserUtil.parse(originalSql);List<Column> columns = insert.getColumns();List<Expression> expressions = insert.getItemsList(ExpressionList.class).getExpressions();insertCleanse(columns, expressions);Map<String, Object> insertParameter = new HashMap<String, Object>() {{put(dept_id, currentUser.getDeptId());put(create_by, currentUser.getUserId());}};//填充信息setInsertParameter(columns, expressions, insertParameter);originalSql = insert.toString();} else {return invocation.proceed();}metaObject.setValue("delegate.boundSql.sql" , originalSql);return invocation.proceed();}/*** 填充Insert入参,只做string和long,可另行扩展*/private void setInsertParameter(List<Column> columns, List<Expression> expressions, Map<String, Object> insertParameter) {insertParameter.forEach((k, v) -> {columns.add(new Column(k));if (v instanceof Long) {expressions.add(new LongValue((long) v));} else {expressions.add(new StringValue((String) v));}});}/*** 填充update入参,只做string和long,可另行扩展*/public void setUpdateParameter(List<UpdateSet> updateSets, Map<String, Object> updateParameter) {updateParameter.forEach((k, v) -> {UpdateSet updateSet = new UpdateSet();if (v instanceof Long) {updateSet.add(new Column(k), new LongValue((long) v));} else {updateSet.add(new Column(k), new StringValue(String.valueOf(v)));}updateSets.add(updateSet);});}/*** 删除update数据*/private void updateCleanse(ArrayList<UpdateSet> updateSets) {List<String> deleteColumnList = Arrays.asList(deleteColumn);List<UpdateSet> deleteUpdateSets = updateSets.parallelStream().map(n -> {ArrayList<Column> columns = n.getColumns();for (Column column : columns) {if (deleteColumnList.contains(column.toString())) {return n;}}return null;}).filter(ObjectUtil::isNotNull).collect(Collectors.toList());if (!deleteUpdateSets.isEmpty()) {updateSets.removeAll(deleteUpdateSets);}}/*** 删除insert数据*/private void insertCleanse(List<Column> columns, List<Expression> expressions) {List<String> deleteColumnList = Arrays.asList(deleteColumn);//删除sql对应值List<Column> deleteColumn = columns.parallelStream().map(n -> {if (deleteColumnList.contains(n.toString())) {return n;}return null;}).filter(ObjectUtil::isNotNull).collect(Collectors.toList());if (!deleteColumn.isEmpty()) {deleteColumn.parallelStream().forEach(n -> expressions.remove(columns.indexOf(n)));columns.removeAll(deleteColumn);}}/*** 生成拦截对象的代理** @param target 目标对象* @return 代理对象*/@Overridepublic Object plugin(Object target) {if (target instanceof StatementHandler) {return Plugin.wrap(target, this);}return target;}/*** mybatis配置的属性** @param properties mybatis配置的属性*/@Overridepublic void setProperties(Properties properties) {}
}

返回数据修改

比如根据部门id查询部门返回部门名称

import cn.hutool.core.util.StrUtil;
import io.swagger.annotations.ApiModel;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ResultMap;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;import static com.disclosure.business.config.InitiateInterceptField.*;@Intercepts({@Signature(type = ResultSetHandler.class, method = "handleResultSets" , args = {Statement.class})})
public class FillUserInterceptor implements Interceptor {private final String[] initiateInterceptField = {deptId, createBy, updateBy};public Object intercept(Invocation invocation) throws Throwable {MetaObject metaStatementHandler = SystemMetaObject.forObject(invocation.getTarget());MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("mappedStatement");if (!mappedStatement.getId().startsWith("com.disclosure.business")) return invocation.proceed();if (!SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) return invocation.proceed();List<ResultMap> resultMaps = mappedStatement.getResultMaps();Class<?> resultType = resultMaps.get(0).getType();//稍微判断一下只有实体类又这个注解才进入下面if (!resultType.isAnnotationPresent(ApiModel.class)) {return invocation.proceed();}//获取mybatis返回的实体类类型名int resultMapCount = resultMaps.size();if (resultMapCount > 0) {Statement statement = (Statement) invocation.getArgs()[0];ResultSet resultSet = statement.getResultSet();if (resultSet != null) {//获得对应列名ResultSetMetaData resultSetMetaData = resultSet.getMetaData();List<String> columnList = new ArrayList<>();List<String> initiateInterceptFieldList = Arrays.asList(initiateInterceptField);for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {//做下划线转驼峰操作columnList.add(StrUtil.toCamelCase(resultSetMetaData.getColumnName(i)));}initiateInterceptFieldList.retainAll(columnList);List<LinkedHashMap<String, Object>> resList = new ArrayList<>();while (resultSet.next()) {LinkedHashMap<String, Object> resultMap = new LinkedHashMap<>();for (String colName : columnList) {resultMap.put(colName, resultSet.getString(StrUtil.toUnderlineCase(colName)));}
//                    将转换后的map转换为实体类中,适用于实体类中有需要填充的数据
//                    Object o = resultType.newInstance();
//                    BeanUtils.populate(o, resultMap);resultMap.put(deptName, "测试部门");resultMap.put(createName, "测试新增人员");resultMap.put(updateName, "测试修改人员");resList.add(resultMap);}return resList;}}return invocation.proceed();}public Object plugin(Object target) {
//        System.out.println("将要包装的目标对象：" + target);return Plugin.wrap(target, this);}public void setProperties(Properties properties) {}
}