springboot中添加数据级别的权限拦截（包装SQL）

使用mybatis拦截器和aop实现SQL包装

在需要数据权限拦截的controller层添加更新权限列表注解，在mapper层添加开启拦截器的注解

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

1.注解

import java.lang.annotation.*;

/**
 * appName权限注解，必须和updateUserPermissions注解一同使用
 * 此注解在mapper层使用
 *
 * @author ***
 */
@Target({ElementType.METHOD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
public @interface PermissionsForAppName {
    boolean flag() default true;
}

/**
 * 用于更新用户权限信息
 * 在需要加appName权限的接口（Controller）上使用，此注解和PermissionsForAppName注解搭配使用
 *
 * @author ***
 */
@Target({ElementType.METHOD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface UpdateUserPermissions {
}

2.mybatis SQL拦截器

因为使用的是pagehelper-spring-boot-starter分页器，找了许久一直没找到自定义拦截器先于pagehelper执行的办法，所以就连同分页的count查询一起包装了
注意，我在这遇到一个坑，在mybatis拦截器中不能进行sql查询的动作，否则会报空指针异常；原因是SQL查询时有一个本地线程变量，在mybatis拦截器查询会导致线程变量被清空，包装过的SQL再执行时就空指针了
我的解决办法是：在Controller层加注解，注解用aop的方式将权限列表数据查询出来放在静态变量里，进行SQL包装时直接拿过来用就行

import com.*.admin_log_web.common.annotation.PermissionsForAppName;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.util.List;
import java.util.Properties;

/**
 * @author ***
 */

@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
public class AppNamePermissionsInterceptor implements Interceptor {
    private final String COUNT_STR = "_COUNT";

    public static List<String> appNameList = null;
    public static boolean isSuperAdmin = false;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        //判断用户是否为超级管理员，为true直接放行
        if (isSuperAdmin) {
            return invocation.proceed();
        }
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
        //先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        //id为执行的mapper方法的全路径名，如com.*.dao.UserMapper.insertUser
        String id = mappedStatement.getId();
        //sql语句类型 select、delete、insert、update
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        BoundSql boundSql = statementHandler.getBoundSql();

        //获取到原始sql语句
        String sql = boundSql.getSql();
        String mSql = sql;

        //TODO 修改位置
        //注解逻辑判断  添加注解了才拦截
        Class<?> classType = Class.forName(mappedStatement.getId().substring(0, mappedStatement.getId().lastIndexOf(".")));
        //获取接口方法名
        String methodName = mappedStatement.getId().substring(mappedStatement.getId().lastIndexOf(".") + 1, mappedStatement.getId().length());
        boolean isPageCount = false;
        //判断是否是分页查询，用于检查是否是加过权限注解
        int exist = methodName.indexOf(COUNT_STR);
        if (exist != -1) {
            //检查mName尾部是否是 “_COUNT”
            String temp = methodName.substring(methodName.length() - COUNT_STR.length());
            if (COUNT_STR.equals(temp)) {
                isPageCount = true;
                methodName = methodName.substring(0, methodName.length() - COUNT_STR.length());
            }
        }
        for (Method method : classType.getDeclaredMethods()) {
            if (method.isAnnotationPresent(PermissionsForAppName.class) && methodName.equals(method.getName())) {
                PermissionsForAppName interceptorAnnotation = method.getAnnotation(PermissionsForAppName.class);
                if (interceptorAnnotation.flag()) {
                    if (isPageCount) {
                        mSql = pageSqlRemould(sql);
                    } else {
                        mSql = sqlRemould(sql);
                    }
                }
            }
        }

        //通过反射修改sql语句
        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, mSql);
        return invocation.proceed();

    }

    /**
     * 改造sql，添加权限
     *
     * @param sql
     * @return
     */
    private String sqlRemould(String sql) {
        int limitIndex = sql.indexOf("LIMIT ?");
        String limitStr = sql.substring(sql.indexOf("LIMIT"));
        if (limitIndex != -1) {
            sql = sql.substring(0, limitIndex);
        }
        sql = "SELECT * FROM (" + sql + ") as temp where app_name in (" + joinAppName(appNameList) + ") "+limitStr;
        return sql;
    }

    /**
     * 改造分页sql(count)，添加权限
     *
     * @param sql
     * @return
     */
    private String pageSqlRemould(String sql) {
        final String COUNT_SQL = "SELECT count(0) FROM";
        sql = sql.substring(COUNT_SQL.length());
        sql = COUNT_SQL + "(select app_name from " + sql + ")as temp where app_name in (" + joinAppName(appNameList) + ")";
        return sql;
    }


    /**
     * 拼接sql函数in的参数（appName）
     *
     * @param appNameList
     * @return
     */
    private String joinAppName(List<String> appNameList) {
        String result = "";
        if (appNameList == null || appNameList.size() == 0) {
            return "''";
        }
        for (String appName : appNameList) {
            result += "'" + appName + "'" + ",";
        }
        return result.substring(0, result.lastIndexOf(","));
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }

    }

    @Override
    public void setProperties(Properties properties) {

    }
}

3.以AOP的方式更新用户权限列表

import com.vivo.admin_log_web.common.VivoFilter.GetSsoUserUtil;
import com.vivo.admin_log_web.service.impl.AdminAppNameServiceImpl;
import com.vivo.admin_log_web.service.impl.AdminUserServiceImpl;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.util.List;

/**
 * 更新访问用户权限和PermissionsForAppName注解搭配使用
 *
 * @author 高金山
 */
@Aspect
@Component
public class UpdateUserPermissions {
    @Autowired
    private AdminAppNameServiceImpl appNameService;
    @Autowired
    private AdminUserServiceImpl userService;
	//括号中填入注解所在的全路径
    @Before("@annotation(com.*.admin_log_web.common.annotation.UpdateUserPermissions)")
    public void updateUserPermission(){
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        //判断用户是否为超级管理员,并更新到appName权限拦截器
        AppNamePermissionsInterceptor.isSuperAdmin = userService.getUserComment(GetSsoUserUtil.getCurrentUserId(request));
        //更新用户appName权限列表到appName权限拦截器
        AppNamePermissionsInterceptor.appNameList = appNameService.getAppNameByUserId(GetSsoUserUtil.getCurrentUserId(request));;
    }

}