springboot+mybatis拦截器+自定义注解实现业务数据权限控制、逻辑删除，支持多表

对中途接手一个项目进行改造。

需求1业务数据权限控制：表的数据权限由多个字段进行控制，并不是固定的部门、角色、用户id等，并且是否进行过滤因角色而异。例：表A，角色1查询时根据字段a、字段b过滤，角色2查询时根据字段c过滤；字段a、b、c可以是部门id、用户id、岗位id、部门编码等等，假设字段a存储是部门id，字段b存储的是岗位id，那么角色1查询时候，就只能查询到其自身部门及岗位的信息。

实现思路：自定义一个注解加mybatis拦截器，拦截实体类带有自定义注解的查询，在sql语句的where追加过滤条件。

需考虑到多表查询及某些特殊情况下不需要进行过滤的情况。

多表问题可以获取到主表的别名再进行追加条件；某些特殊情况不进行过滤的问题，（1）可以再自定义一个方法级的注解，在方法上标记注解，然后在过滤器中判断是否含有该标记，优点是对代码无侵入性，缺点是不够灵活（2）在构造sql时候，在sql上添加特殊标记，在过滤时判断是否存在特殊标记，存在则去除该标记，并且不追加过滤条件，优点是灵活，颗粒度细，缺点是对代码存在一定的侵入性。

需求2逻辑删除：业务数据的删除需要改为逻辑删除，不改变原有代码实现功能。

实现思路：表新增delete_flag字段，在mybatis拦截器中，拦截删除操作将删除sql修改为修改sql，变成update table_name set delete_flag = 1 where id = #{id}，同时拦截查询操作，在查询sql的where中添加delete_flag = 0，从而实现既不修改删除方法，也不修改查询方法。（某些多租户系统也是使用这种方式实现，在新增数据的时候通过自动注入租户id，在查询的时候拼接租户ID条件，不过仅仅这样并不完善，存在如redis未进行数据隔离等问题）

代码：
1、业务数据过滤注解

/**
 * 业务数据过滤注解
 * 如果实体类字段添加了该注解，但是某些查询需要不进行业务数据过滤，可以进行以下操作：
 * 1、如果是QueryWrapper或LambdaQueryWrapper查询，可以追加【.last(CommonConstant.AND_NO_BUSINESS_DATA_AUTO)】
 * 2、如果是编写sql语句查询，可以在where条件中添加【and noBusinessDataAuto】，如果sql定义了表别名，同样添加【and noBusinessDataAuto】，不要需要在【noBusinessDataAuto】之前使用别名
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface BusinessDataAuto {

    /**
     * 数据库列名
     */
    String column();

    /**
     * 登录用户为该角色列表中的一个时才进行过滤
     */
    RoleEnum[] role() default {RoleEnum.SECURITY_ADMIN,RoleEnum.INSTITUTIONAL_LEADER,RoleEnum.FIRST_LEVEL_AUDIT,RoleEnum.SECOND_LEVEL_AUDIT,RoleEnum.EXPERT};

    /**
     * 数据类型
     */
    DateTypeEnum dateType() default DateTypeEnum.USER_ID;

    /**
     * 过滤类型
     */
    QueryWrapperEnum queryType() default QueryWrapperEnum.EQ;

}

2、枚举

/**
 * 角色枚举（枚举最好不要id，这里有id是因为半途接手，历史遗留原因。）
 */
@Getter
@AllArgsConstructor
public enum RoleEnum {
    ADMIN("f6817f48af4fb3af11b9e8bf182f618b","admin", "管理员"),
    OPERATION_MAINTENANCE("1816313867255328769","operation_maintenance", "运维"),
    USER_ADMIN("1851580121292726273","user_admin", "账号管理员"),
    SECURITY_ADMIN("1816313756261462018","security_admin", "保司管理员"),
    INSTITUTIONAL_LEADER("1816313610584895489","institutional_leader", "机构负责人"),
    FIRST_LEVEL_AUDIT("1816379310624133122","first_level_audit", "一级审核人员"),
    SECOND_LEVEL_AUDIT("1828680858725187586","second_level_audit", "二级审核人员"),
    EXPERT("1816313268711370753","expert", "专家");

    private final String id;
    private final String code;
    private final String name;

    public static String getCodeByID(String id){
        for (RoleEnum e : RoleEnum.values()) {
            if (id.startsWith(e.getId())) {
                return e.getCode();
            }
        }
        return null;
    }
}


/**
 * 数据类型枚举
 */
@Getter
@AllArgsConstructor
public enum DateTypeEnum {
    // 部门ID
    DEPART_ID,
    // 部门名称
    DEPART_NAME,
    //部门编码
    DEPART_CODE,
    // 用户ID
    USER_ID,
    // 用户名称
    USER_NAME,
    //角色ID
    ROLE_ID,
    //角色名称
    ROLE_NAME,
    //角色编码
    ROLE_CODE
}

/**
 * QueryWrapper枚举
 */
@Getter
public enum QueryWrapperEnum {
    EQ(EqualsTo::new),
    NE(NotEqualsTo::new),
    GT((column, value) -> new GreaterThan().withLeftExpression(column).withRightExpression(value)),
    LT((column, value) -> new GreaterThan().withLeftExpression(value).withRightExpression(column)),
    GTE((column, value) -> new GreaterThanEquals().withLeftExpression(column).withRightExpression(value)),
    LTE((column, value) -> new GreaterThanEquals().withLeftExpression(value).withRightExpression(column)),
    LIKE((column, value) -> new LikeExpression().withLeftExpression(column).withRightExpression(value));
    //其他类型用到再添加

    private final BiFunction<Column, StringValue, Expression> operation;

    QueryWrapperEnum(BiFunction<Column, StringValue, Expression> operation) {
        this.operation = operation;
    }

    public Expression apply(String field, String value) {
        Column column = new Column(field);
        StringValue stringValue = new StringValue(value);
        return operation.apply(column, stringValue);
    }

}

3、常量

    /**
     * 逻辑删除(0：未删除，1：已删除)
     */
    String DELETE_FLAG = "delete_flag";

	/**
	 * 删除标志
	 */
	Integer DEL_FLAG_1 = 1;

	/**
	 * 未删除
	 */
	Integer DEL_FLAG_0 = 0;

    /**
     * 不进行业务数据权限过滤
     */
    String AND_NO_BUSINESS_DATA_AUTO = " and noBusinessDataAuto";
    String NO_BUSINESS_DATA_AUTO = "noBusinessDataAuto";

4、通用实体类

@Data
public class CommonEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    /** 创建人 */
    @ApiModelProperty(value = "创建人")
    private String createBy;

    /** 创建日期 */
    @ApiModelProperty(value = "创建日期")
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    /** 更新人 */
    @ApiModelProperty(value = "更新人")
    private String updateBy;

    /** 更新日期 */
    @ApiModelProperty(value = "更新日期")
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date updateTime;

    /** 逻辑删除标识(0：未删除，1：已删除) */
    @ApiModelProperty(value = "逻辑删除标识(0：未删除，1：已删除)")
    private String deleteFlag;

}

5、mybatis拦截器

/**
 * 业务过滤-mybatis拦截器
 */
@Slf4j
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})
})
@Component
public class BusinessInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        // 过滤select查询
        if (SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
            return select(invocation);
        }
        // 过滤delete操作
        if (SqlCommandType.DELETE.equals(mappedStatement.getSqlCommandType())){
            return delete(invocation);
        }
        return invocation.proceed();
    }

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

    /**
     * 查询过滤
     * 1、过滤带有BusinessDataAuto注解的实体类查询
     * 2、逻辑删除过滤
     * 3、后续可以添加其他过滤
     */
    public Object select(Invocation invocation)throws Throwable {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
        // 获取当前查询的实体类类型
        Class<?> clazz = getEntityClass(mappedStatement);
        if (clazz == null) {
            return invocation.proceed();
        }

        // 解析和修改 SQL
        BoundSql boundSql = statementHandler.getBoundSql();
        String originalSql = boundSql.getSql();
        try {
            Statement statement = CCJSqlParserUtil.parse(originalSql);
            if (statement instanceof Select) {
                Select selectStatement = (Select) statement;
                SelectBody selectBody = selectStatement.getSelectBody();
                if (selectBody instanceof PlainSelect) {
                    PlainSelect plainSelect = (PlainSelect) selectBody;
                    //主表别名
                    Alias mainTableAlias = plainSelect.getFromItem().getAlias();
                    Expression originalWhere = plainSelect.getWhere();

                    //添加逻辑删除过滤逻辑
                    originalWhere = addDeleteFlag(mainTableAlias ,plainSelect,originalWhere,clazz);

                    //添加业务数据权限过滤
                    originalWhere = addBusinessDataAuto(mainTableAlias ,plainSelect,originalWhere,clazz);

                    // 使用反射更新 BoundSql 中的 SQL
                    updateBoundSqlSql(boundSql, selectStatement.toString());
                }
            }
        } catch (JSQLParserException e) {
            throw new SQLException("解析SQL失败!", e);
        }
        return invocation.proceed();
    }

    /**
     * 将删除sql改为修改sql，物理删除改为逻辑删除
     * 条件：
     * 1、继承CommonEntity类
     * 2、后续如果有要物理删除的业务数据，又继承了CommonEntity类，可以新增一个@NoDeleteFlag注解，存在该注解的实体类不进行逻辑删除
     */
    public Object delete(Invocation invocation)throws Throwable{
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        BoundSql boundSql = statementHandler.getBoundSql();
        String originalSql = boundSql.getSql();
        MappedStatement mappedStatement = (MappedStatement) SystemMetaObject.forObject(statementHandler).getValue("delegate.mappedStatement");
        Class<?> clazz = getEntityClass(mappedStatement);

        try {
            Statement statement = CCJSqlParserUtil.parse(originalSql);
            if (statement instanceof Delete) {
                Delete deleteStatement = (Delete) statement;
                Expression originalWhere = deleteStatement.getWhere();

                // 构造逻辑删除的update语句
                Update updateStatement = new Update();
                updateStatement.setTable(deleteStatement.getTable());

                // 设置逻辑删除字段和值
                if (clazz != null && CommonEntity.class.isAssignableFrom(clazz)) {
                    updateStatement.addUpdateSet(new Column(CommonConstant.DELETE_FLAG), new StringValue(CommonConstant.DEL_FLAG_1.toString()));
                } else {
                    return invocation.proceed();
                }

                // 设置原来的where条件
                updateStatement.setWhere(originalWhere);

                // 更新BoundSql中的SQL
                updateBoundSqlSql(boundSql, updateStatement.toString());
            }
        } catch (JSQLParserException e) {
            log.error("解析SQL失败!", e);
            throw new RuntimeException("解析SQL失败!", e);
        }
        return invocation.proceed();
    }

    /**
     * 添加逻辑删除过滤逻辑
     * 在where条件中拼接delete_flag = 0的条件
     * 条件：
     * 1、继承CommonEntity类
     */
    public Expression addDeleteFlag(Alias alias,PlainSelect plainSelect,Expression originalWhere,Class<?> clazz){
        //判断clazz是否继承CommonEntity类
        if (!CommonEntity.class.isAssignableFrom(clazz)) {
            return originalWhere;
        }

        EqualsTo equals = new EqualsTo();
        String column = CommonConstant.DELETE_FLAG;
        if (StrUtils.isNotEmpty(alias)){
            column = alias + "." + column;
        }
        equals.setLeftExpression(new Column(column));
        equals.setRightExpression(new StringValue(CommonConstant.DEL_FLAG_0.toString()));

        if (originalWhere == null) {
            plainSelect.setWhere(equals);
        } else {
            plainSelect.setWhere(new AndExpression(originalWhere, equals));
        }

        return plainSelect.getWhere();
    }

    /**
     * 添加业务数据权限过滤
     * 条件：
     * 1、已登录
     * 2、实体类存在BusinessDataAuto注解
     * 3、原where中不存在noBusinessDataAuto条件
     */
    public Expression addBusinessDataAuto(Alias alias,PlainSelect plainSelect,Expression originalWhere,Class<?> clazz){
        // 检查是否登录，未登录不再进行业务数据权限过滤
        if (!checkLogin()) {
            return originalWhere;
        }

        //校验原where中是否存在noBusinessDataAuto条件
        if (originalWhere != null) {
            Expression newWhere = checkAndRemoveNoBusinessDataAuto(originalWhere);
            //如果原where中存在noBusinessDataAuto条件，则去除该条件，更新where条件，不再进行业务数据权限过滤
            if (!Objects.equals(originalWhere.toString(), newWhere.toString())) {
                plainSelect.setWhere(newWhere);
                return plainSelect.getWhere();
            }
        }

        // 获取实体BusinessDataAuto注解集合
        List<BusinessDataAuto> businessDataAutoList = getBusinessDataAutoList(clazz);
        if (businessDataAutoList.isEmpty()) {
            return originalWhere;
        }
        // 构建数据权限条件
        Expression dataPermissionCondition = buildDataPermissionConditions(alias,businessDataAutoList);
        if (dataPermissionCondition == null) {
            return originalWhere;
        }

        if (originalWhere == null) {
            plainSelect.setWhere(dataPermissionCondition);
        } else {
            plainSelect.setWhere(new AndExpression(originalWhere, dataPermissionCondition));
        }
        return plainSelect.getWhere();
    }

    /**
     * 获取当前查询的实体类类型
     */
    private Class<?> getEntityClass(MappedStatement mappedStatement) {
        try {
            String id = mappedStatement.getId();
            String className = id.substring(0, id.lastIndexOf("."));
            Class<?> mapperClass = Class.forName(className);
            if (!BaseMapper.class.isAssignableFrom(mapperClass)) {
                return null;
            }
            //获取带泛型的父接口
            Type[] interfaces = mapperClass.getGenericInterfaces();
            Type anInterface = interfaces[0];
            ParameterizedType paramType = (ParameterizedType) anInterface;

            //获取父接口的泛型
            Type[] actualTypeArguments = paramType.getActualTypeArguments();
            return (Class<?>) actualTypeArguments[0];
        } catch (ClassNotFoundException e) {
            log.error("获取实体类类型失败!", e);
            return null;
        }
    }

    /**
     * 判断是否登录
     */
    private boolean checkLogin() {
        try {
            SecurityUtils.getSubject().getPrincipal();
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    /**
     * 获取实体类中的BusinessDataAuto注解集合
     */
    private List<BusinessDataAuto> getBusinessDataAutoList(Class<?> entityClass) {
        List<BusinessDataAuto> businessDataAutos = new ArrayList<>();
        for (Field field : entityClass.getDeclaredFields()) {
            BusinessDataAuto businessDataAuto = field.getAnnotation(BusinessDataAuto.class);
            if (businessDataAuto != null) {
                businessDataAutos.add(businessDataAuto);
            }
        }
        return businessDataAutos;
    }

    /**
     * 构建数据过滤表达式
     */
    private Expression buildDataPermissionConditions(Alias alias,List<BusinessDataAuto> businessDataAutoList) {
        Expression expression = null;

        String roleCode = LoginUtil.getRoleCode();
        // 收集数据权限条件
        for (BusinessDataAuto businessDataAuto : businessDataAutoList) {
            RoleEnum[] role = businessDataAuto.role();
            boolean flag = false;//标记是否存在符合的角色
            for (RoleEnum roleEnum : role) {
                if (roleEnum.getCode().equals(roleCode)) {
                    flag = true;
                    break;
                }
            }
            //不存在符合的角色
            if (!flag) {
                continue;
            }
            String column = businessDataAuto.column();
            if (StrUtils.isNotEmpty(alias)){
                column = alias + "." + column;
            }
            QueryWrapperEnum queryWrapperEnum = businessDataAuto.queryType();
            String value;
            switch (businessDataAuto.dateType()) {
                case USER_ID:
                    value = LoginUtil.getUserId();
                    break;
                case USER_NAME:
                    value = LoginUtil.getUserName();
                    break;
                case DEPART_ID:
                    value = LoginUtil.getDepartId();
                    break;
                case DEPART_CODE:
                    value = LoginUtil.getDepartCode();
                    break;
                case DEPART_NAME:
                    value = LoginUtil.getDepartName();
                    break;
                case ROLE_ID:
                    value = LoginUtil.getRoleId();
                    break;
                case ROLE_CODE:
                    value = LoginUtil.getRoleCode();
                    break;
                case ROLE_NAME:
                    value = LoginUtil.getRoleName();
                    break;
                default:
                    throw new ServiceException("错误数据类型枚举!");
            }
            expression = queryWrapperEnum.apply(column, value);
        }
        return expression;
    }

    /**
     * 校验原where中是否存在noBusinessDataAuto条件，存在则去除该条件
     */
    private static Expression checkAndRemoveNoBusinessDataAuto(Expression expression) {
        if (expression == null) {
            return null;
        }
        // 括号
        else if (expression instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) expression;
            Expression parenthesisExpression = checkAndRemoveNoBusinessDataAuto(parenthesis.getExpression());
            return new Parenthesis(parenthesisExpression);
        } else
            // and
            if (expression instanceof AndExpression) {
            AndExpression andExpression = (AndExpression) expression;
            Expression left = checkAndRemoveNoBusinessDataAuto(andExpression.getLeftExpression());
            Expression right = checkAndRemoveNoBusinessDataAuto(andExpression.getRightExpression());
            if (left instanceof ItemsList || right instanceof ItemsList) {
                return new AndExpression(left, right);
            } else if (left == null) {
                return right;
            } else if (right == null) {
                return left;
            } else {
                return new AndExpression(left, right);
            }
        } else
            // or
            if (expression instanceof OrExpression) {
            OrExpression orExpression = (OrExpression) expression;
            Expression left = checkAndRemoveNoBusinessDataAuto(orExpression.getLeftExpression());
            Expression right = checkAndRemoveNoBusinessDataAuto(orExpression.getRightExpression());
            if (left instanceof ItemsList || right instanceof ItemsList) {
                return new OrExpression(left, right);
            } else if (left == null) {
                return right;
            } else if (right == null) {
                return left;
            } else {
                return new OrExpression(left, right);
            }
        } else {
            if (CommonConstant.NO_BUSINESS_DATA_AUTO.equals(expression.toString())) {
                return null;
            }
        }
        return expression;
    }

    /**
     * 修改sql
     */
    private void updateBoundSqlSql(BoundSql boundSql, String newSql) {
        try {
            Field sqlField = BoundSql.class.getDeclaredField("sql");
            sqlField.setAccessible(true);
            sqlField.set(boundSql, newSql);
        } catch (Exception e) {
            throw new RuntimeException("更新BoundSql SQL失败!", e);
        }
    }

}

6、使用示例

1、@BusinessDataAuto注解使用示例1
public class InsurancePolicy extends CommonEntity {
    private static final long serialVersionUID = 1L;

    /**
     * 出单机构id
     */
    @BusinessDataAuto(column = "issue_organ_id",role = RoleEnum.SECURITY_ADMIN,dateType =                 DateTypeEnum.DEPART_ID)
    private String issueOrganId;

    /**
     * 服务机构id
     */
    @BusinessDataAuto(column = "service_organ_id",role = RoleEnum.INSTITUTIONAL_LEADER,dateType = DateTypeEnum.DEPART_ID)
    private String serviceOrganId;

    /**
     * 专家id
     */
    @BusinessDataAuto(column = "specialist_id",role = RoleEnum.EXPERT)
    private String specialistId;

    ......
}

2、@BusinessDataAuto注解使用示例2
public class ExpertLog extends CommonEntity {
    private static final long serialVersionUID = 1L;

    /**
     * 所属部门
     */
    @BusinessDataAuto(column = "depart_id",dateType = DateTypeEnum.DEPART_ID)
    private java.lang.String departId;

    ......
}

3、不进行过滤查询示例1
DangerReport dangerReport = dangerReportService.getOne(new LambdaQueryWrapper<DangerReport>()
                .eq(DangerReport::getInsurancePolicyId, insuranceServiceItem.getInsurancePolicyId())
                .last(CommonConstant.AND_NO_BUSINESS_DATA_AUTO));

4、不进行过滤查询示例2
select * from danger_report where insurance_policy_id = #{insurancePolicyId} and noBusinessDataAuto