Mybatis自定义拦截实现注解方式数据权限拦截

问题一：和分页的拦截器冲突，造成拦截器执行不了的问题

刚开始解决方法是

import com.yzc.aboatedu.interceptor.ExecutorInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationListener;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.stereotype.Component;
 
import java.util.List;
 
@Component
public class StartSysListener implements ApplicationListener<ContextRefreshedEvent> {
 
  private static Logger log = LoggerFactory.getLogger(StartSysListener.class);
  
  @Autowired
  private ExecutorInterceptor executorInterceptor;
  @Autowired
  private List<SqlSessionFactory> sqlSessionFactoryList;
 
 
  @Override
  public void onApplicationEvent(ContextRefreshedEvent event) {
 
    this.addMyInterceptor();
 
  }
 
  private void addMyInterceptor() {
    log.debug("添加自定义Mybatis SQL拦截器.");
    for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
      sqlSessionFactory.getConfiguration().addInterceptor(executorInterceptor);
    }
  }
 
}

后面发现要么SQL就拼接不上要么，要么SQL就重复拼接。后来查看了PageHelper的官方对拦截器的说明：

https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/Interceptor.md

把拦截器的定义按他的规范来使用，然而拦截器能执行，也不需要上面的starterListner的监听。但是问题还是一样，拼接SQL会重复。

后来查了些资料，得到如下解决方案：

@Configuration
@AutoConfigureAfter(PageHelperAutoConfiguration.class)
public class TestLogAutoConfiguration {
    @Autowired
    private List<SqlSessionFactory> sqlSessionFactoryList;

    @PostConstruct
    public void addMyInterceptor() {
        DataPermissionInterceptor e = new DataPermissionInterceptor();
        for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
            sqlSessionFactory.getConfiguration().addInterceptor(e);
        }
    }
}

原则就是在加载PageHelper的配置之后加载该配置，添加拦截器，保证执行顺序。

问题二：如何拦截SQL，实现权限SQL语句拼接

首先肯定需要进行SQL拦截，必然的拦截器不能少，所以第一步，自定义拦截器

/**
 * 封装基础拦截器，针对不同的操作进行不同的处理
 */
@Intercepts({
	@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
	@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
	}
)
@Slf4j
public class DataPermissionInterceptor  implements Interceptor {

	private static final Integer MAPPED_STATEMENT_INDEX = 0;
	private static final Integer PARAM_OBJ_INDEX = 1;
	private static final Integer ROW_BOUNDS_INDEX = 2;
	private static final Integer RESULT_HANDLER_INDEX = 3;
	private static final Integer CACHE_KEY_INDEX = 4;
	private static final Integer BOUND_SQL_INDEX = 5;
	private static final String COUNT_PRE = "_COUNT";

	@Autowired
	private SpringUtil springUtil;

	@Override
	public Object intercept(Invocation invocation) throws Throwable {
		Object[] args = invocation.getArgs();
		MappedStatement ms = (MappedStatement) args[MAPPED_STATEMENT_INDEX];
		Object parameter = args[PARAM_OBJ_INDEX];
		RowBounds rowBounds = (RowBounds) args[ROW_BOUNDS_INDEX];
		ResultHandler resultHandler = (ResultHandler) args[RESULT_HANDLER_INDEX];
		Executor executor = (Executor) invocation.getTarget();
		CacheKey cacheKey;
		BoundSql boundSql;
		//由于逻辑关系，只会进入一次
		if(args.length == 4){
			//4 个参数时
			boundSql = ms.getBoundSql(parameter);
			cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
		} else {
			//6 个参数时
			cacheKey = (CacheKey) args[CACHE_KEY_INDEX];
			boundSql = (BoundSql) args[BOUND_SQL_INDEX];
		}

		
		return executor.query(newMappedStatement, parameter, rowBounds, resultHandler, cacheKey, boundSql);
	}

然后考虑第一个问题，不是所有的查询都要拦截，那么就必然是标注了的才需要去拦截。

目标明确，解决方法也就很明确了：注解

自定义注解如下：

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface DataAuth {

    String empId();

    String project();

    String org() default "";

    String orderBy();
}

这个注解很好理解，作用域是方法级别，就是要查那个员工的，那个部门的，那个项目的，最后再排序下。

OK有了注解，就需要在拦截器里面进行捕获该注解：

	/**
	 * 获取数据权限注解信息
	 *
	 * @param mappedStatement
	 * @return
	 */
	private DataAuth getPermissionByDelegate(MappedStatement mappedStatement) {
		DataAuth dataAuth = null;
		try {
			String id = mappedStatement.getId();
			//统计SQL取得注解也是实际查询id上得注解，所以需要去掉_COUNT
			if(id.contains(COUNT_PRE)){
				id = id.replace(COUNT_PRE,"");
			}
			String className = id.substring(0, id.lastIndexOf("."));
			String methodName = id.substring(id.lastIndexOf(".") + 1, id.length());
			final Class<?> cls = Class.forName(className);
			final Method[] method = cls.getMethods();
			for (Method me : method) {
				if (me.getName().equals(methodName) && me.isAnnotationPresent(DataAuth.class)) {
					dataAuth = me.getAnnotation(DataAuth.class);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dataAuth;
	}

这个就是反射了，没什么很难理解的，唯一需要注意的细节就是，如果是使用PageHelper的话，会默认先生存一个查询总数的SQL。这里需要共用改注解，而PageHelper的生成查询总量的方法其实就在该查询ID的后面追加_count

private static final String COUNT_PRE = "_COUNT";

所以这里捕获注解的时候需要去原查询的Id上去捕获，保证统计方法也能拼接上权限SQL。

考虑第二个问题：如何拼接SQL，也就是捕获原SQL生成新SQL

获取原SQL，这个比较简单

//原sql
String sql = boundSql.getSql();

拼接SQL得到新SQL

		String newSql = permissionSql(sql,dataAuth,boundSql);

	/**
	 * 权限关联
	 * @param sql
	 * @param dataAuth
	 * @param boundSql
	 */
	private String permissionSql(String sql,DataAuth dataAuth, BoundSql boundSql) {
		String oauthSql = getDataPermission(dataAuth.empId(),dataAuth.project(),dataAuth.org());
		log.info("权限SQL：{}"+oauthSql);
		String newSql = sql +oauthSql + dataAuth.orderBy();
		return newSql;
	}
	/**
	 * 获取数据权限
	 * @param ownerField
	 * @param projectFiled
	 * @return
	 */
	private String getDataPermission(String ownerField,String projectFiled,String orgIdField){
		BaseEmployee employee = AuthSqlUtil.getBaseEmployee();
		String dataPermission = AuthSqlUtil.getCurrentMenuCodeDataPermission();
		String sql = "";
		if(DataPermission.ONE.getCode().equals(dataPermission)){
			if(StringUtils.isNotBlank(ownerField)) {
				sql += " and " + ownerField + " = '" + employee.getId() + "' ";
			}
			if(StringUtils.isNotBlank(orgIdField)){
				sql += " and " + orgIdField + " = '" + employee.getOrgId() + "' ";
			}
			log.info("当前个人权限");
		}
		//本部及以下
		if(DataPermission.THREE.getCode().equals(dataPermission)) {
			//当前登录人所在组织和所有下级组织
			String suborg = AuthSqlUtil.getSubOrg();
			if(StringUtils.isNotBlank(orgIdField)){
				sql += " and find_in_set("+orgIdField+",'"+suborg+"')>0";
			}
			String employeeMyOrgAndSubordinate = AuthSqlUtil.getSubOrgEmp();
			if(StringUtils.isNotBlank(ownerField)){
				sql += " and find_in_set("+ownerField+",'"+employeeMyOrgAndSubordinate+"')>0";
			}
		}
		//全部
		if(DataPermission.FOUR.getCode().equals(dataPermission)){
			//当前登录人所在的项目(任意上级有项目属性都可以,每个人只能看自己所在项目的数据)
			String project = AuthSqlUtil.getProject();
			if(project!=null) {
			}
		}
		//本军团
		if(DataPermission.FIVE.getCode().equals(dataPermission)){
			String groupOrgs = AuthSqlUtil.getGroupOrg();
			if(StringUtils.isNotBlank(orgIdField)){
				sql += " and find_in_set("+orgIdField+",'"+groupOrgs+"')>0";
			}
			String result = AuthSqlUtil.getGroupOrgEmp();
			if(StringUtils.isNotBlank(ownerField)){
				sql += " and find_in_set("+ownerField+",'"+result+"')>0";
			}
		}
		//上级部门级以下
		if(DataPermission.SIX.getCode().equals(dataPermission)){
			String upperOrgId = employee.getParentOrgId();
			String upperorg = AuthSqlUtil.getUpperOrg();
			if(StringUtils.isNotBlank(orgIdField)){
				sql += " and find_in_set("+orgIdField+",'"+upperorg+"')>0";
			}
			String result = AuthSqlUtil.getUpperOrgEmp();
			if(StringUtils.isNotBlank(ownerField)){
				sql += " and find_in_set("+ownerField+",'"+result+"')>0";
			}
		}
		//存在关联部门
		if(DataPermission.SEVEN.getCode().equals(dataPermission)) {
			//本部及关联部门
			String employeeManyOrg = AuthSqlUtil.getOtherOrg();
			if(StringUtils.isNotBlank(orgIdField)){
				sql += " and find_in_set("+orgIdField+",'"+employeeManyOrg+"')>0";
			}
			String result =  AuthSqlUtil.getOtherOrgEmp();
			if(StringUtils.isNotBlank(ownerField)){
				sql += " and find_in_set("+ownerField+",'"+result+"')>0";
			}
		}
		return sql;
	}

使新的SQL生效

MappedStatement newMappedStatement = setCurrentSql(ms,parameter,boundSql,newSql);
args[MAPPED_STATEMENT_INDEX] = newMappedStatement;

	/**
	 * 设置sql
	 * @param mappedStatement
	 * @param paramObj
	 * @param boundSql
	 * @param sql
	 * @return
	 */
	private MappedStatement setCurrentSql(MappedStatement mappedStatement, Object paramObj,BoundSql boundSql,String sql) {
		BoundSqlSource boundSqlSource = new BoundSqlSource(boundSql);
		MappedStatement newMappedStatement = copyFromMappedStatement(mappedStatement, boundSqlSource);
		MetaObject metaObject = MetaObject.forObject(newMappedStatement,
				new DefaultObjectFactory(), new DefaultObjectWrapperFactory(),
				new DefaultReflectorFactory());
		metaObject.setValue("sqlSource.boundSql.sql", sql);
		return newMappedStatement;
	}

	/**
	 * 获取MappedStatement
	 * @param invo
	 * @return
	 */
	private MappedStatement getMappedStatement(Invocation invo) {
		Object[] args = invo.getArgs();
		Object mappedStatement = args[MAPPED_STATEMENT_INDEX];
		return (MappedStatement) mappedStatement;
	}

	/**
	 * 自定义私有SqlSource
	 */
	private class BoundSqlSource implements SqlSource {

		private BoundSql boundSql;

		private BoundSqlSource(BoundSql boundSql) {
			this.boundSql = boundSql;
		}

		@Override
		public BoundSql getBoundSql(Object parameterObject) {
			return boundSql;
		}
	}

	/**
	 * copy
	 * @param ms
	 * @param newSqlSource
	 * @return
	 */
	private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
		MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType());
		builder.resource(ms.getResource());
		builder.fetchSize(ms.getFetchSize());
		builder.statementType(ms.getStatementType());
		builder.keyGenerator(ms.getKeyGenerator());
		if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) {
			builder.keyProperty(ms.getKeyProperties()[0]);
		}
		builder.timeout(ms.getTimeout());
		builder.parameterMap(ms.getParameterMap());
		builder.resultMaps(ms.getResultMaps());
		builder.resultSetType(ms.getResultSetType());
		builder.cache(ms.getCache());
		builder.flushCacheRequired(ms.isFlushCacheRequired());
		builder.useCache(ms.isUseCache());
		return builder.build();
	}

当然这里的权限数据，是通过过滤器和线程来进行捕获和维护，至于具体的实现就比较简单，大家根据自己的组织结构去实现就行。

这里需要注意的一个问题，过滤器和spring security结合的时候，可能会导致，执行两次。解决方法很简单，通过请求头增加判断逻辑即可。

   HttpServletRequest request = (HttpServletRequest)servletRequest;
        String authCode = request.getHeader(Constant.AUTHORIZATION);
        if(StringUtils.isBlank(authCode)){
            chain.doFilter(servletRequest, servletResponse);
            return;

        }

public class AuthSqlUtil {

    private static final ThreadLocal<String> currentMenuCodeDataPermission = new ThreadLocal<>();
    private static final ThreadLocal<String> subOrg = new ThreadLocal<>();
    private static final ThreadLocal<String> subOrgEmp = new ThreadLocal<>();
    private static final ThreadLocal<String> groupOrg = new ThreadLocal<>();
    private static final ThreadLocal<String> groupOrgEmp = new ThreadLocal<>();
    private static final ThreadLocal<String> otherOrg = new ThreadLocal<>();
    private static final ThreadLocal<String> otherOrgEmp = new ThreadLocal<>();
    private static final ThreadLocal<String> upperOrg = new ThreadLocal<>();
    private static final ThreadLocal<String> upperOrgEmp = new ThreadLocal<>();
    private static final ThreadLocal<String> project = new ThreadLocal<>();
    private static final ThreadLocal<BaseEmployee> baseEmployee = new ThreadLocal<>();

    public static String getCurrentMenuCodeDataPermission() {
        return currentMenuCodeDataPermission.get();
    }

    public static void setCurrentMenuCodeDataPermission(String dataPermission) {
        currentMenuCodeDataPermission.set(dataPermission);
    }

    public static String getOtherOrg(){
        return otherOrg.get();
    }

    public static void setOtherOrg(String org){
        otherOrg.set(org);
    }
    public static String getOtherOrgEmp(){
        return otherOrgEmp.get();
    }

    public static void setOtherOrgEmp(String orgEmp){
        otherOrgEmp.set(orgEmp);
    }
    public static String getSubOrg(){
        return subOrg.get();
    }

    public static void setSubOrg(String org){
        subOrg.set(org);
    }
    public static String getSubOrgEmp(){
        return subOrgEmp.get();
    }

    public static void setSubOrgEmp(String orgEmp){
        subOrgEmp.set(orgEmp);
    }
    public static String getGroupOrg(){
        return groupOrg.get();
    }

    public static void setGroupOrg(String org){
        groupOrg.set(org);
    }
    public static String getGroupOrgEmp(){
        return groupOrgEmp.get();
    }

    public static void setGroupOrgEmp(String orgEmp){
        groupOrgEmp.set(orgEmp);
    }
    public static String getUpperOrg(){
        return upperOrg.get();
    }

    public static void setUpperOrg(String org){
        upperOrg.set(org);
    }
    public static String getUpperOrgEmp(){
        return upperOrgEmp.get();
    }

    public static void setUpperOrgEmp(String orgEmp){
        upperOrgEmp.set(orgEmp);
    }
    public static String getProject(){
        return project.get();
    }

    public static void setProject(String p){
        project.set(p);
    }

    public static void setBaseEmployee(BaseEmployee employee){
        baseEmployee.set(employee);
    }

    public static BaseEmployee getBaseEmployee(){
        return baseEmployee.get();
    }
}