MybatisPlus动态拼接sql和动态配置表名

最近有个需求，需要支持动态配置表别名和动态逻辑拼接，我是使用mybatis-plus+SpringSecurity完成的。
首先自定义注解@DataColumn
主要属性alias()和operator(),需要设置默认值default，这样可以灵活选择需要使用默认值还是需要动态配置

@Documented
@Inherited
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.TYPE})
@Repeatable(DataScope.class)
public @interface DataColumn {
    String alias() default "";

    String name() default "";

    String[] deptNames() default {};

    SqlColumnTypeEnum type();

    LogicOperatorEnum operator() default LogicOperatorEnum.OR;
}

添加逻辑运算符枚举类LogicOperatorEnum

/**
 * 逻辑运算符
 *
 * @author zlw
 **/
public enum LogicOperatorEnum {
    AND, OR
}

接下来需要自定义一个mybatis-plus拦截器，拦截参数值并设置到security的公共域，方便在数据权限过滤器sql拼接时获取

/**
 * 获取方法参数
 *
 * @author zlw
 **/
public class ParamInterceptor implements InnerInterceptor {
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
        // 获取 @Param 注解标记的参数值
        if (parameter instanceof Map) {
            Map<String, Object> paramMap = (Map<String, Object>) parameter;
            String alias = (String) paramMap.get("alias");
            LogicOperatorEnum operator = (LogicOperatorEnum) paramMap.get("operator");
            System.out.println("Alias: " + alias);
            System.out.println("Operator: " + operator);
            //存储到SpringSecurityContext
            SecurityConstants.getLocalMap().put("alias", alias);
            SecurityConstants.getLocalMap().put("operator", operator);
        }

    }
}

添加数据权限过滤器，根据方法参数值动态设置表别名，以及动态设置拼接的逻辑运算符。并且如果方法参数没传值的话，是可以用注解里的固定属性的，实现灵活配置。

/**
 * 数据权限过滤器
 *
 * @author zlw
 **/
public class DataScopeInterceptor implements DataPermissionHandler {
    private static final Logger log = LoggerFactory.getLogger(DataScopeInterceptor.class);

    public static final Long APP_ID = 11L;

    private static final int LIST_SIZE = 0;

    @Override
    public Expression getSqlSegment(Expression where, String mappedStatementId) {
        Expression expression = null;
        //获取接口上数据配置
        DataScopeProperty dataScopeProperty = AuthContextHolder.getDataScopeProperty();
        if (null == dataScopeProperty || BeanUtil.isEmpty(dataScopeProperty) || dataScopeProperty.isIgnore()) {
            return where;
        }
        Assert.isFalse(BeanUtil.isEmpty(SecurityConstants.getOperationDTO()), "无法从上下文获取用户信息");
        UserRoleAssist userRoleAssist = new UserRoleAssist();
        if (SecurityConstants.getLocalMap().containsKey("header-role-assist")) {
            userRoleAssist = JSON.parseObject(String.valueOf(SecurityConstants.get("header-role-assist")), UserRoleAssist.class);
        }
        //1.判断上下文权限情况authDeptInfoList -- 当前用户
        AuthDeptInfoVO authDeptInfo = SecurityConstants.getOperationDTO().getAuthDeptInfo();
        List<Long> workerDeptInfoList = authDeptInfo.getDeptId();
        if (workerDeptInfoList.size() <= LIST_SIZE) {
            log.info(mappedStatementId + "该用户没有相关部门权限，返回空值");
            EqualsTo equalsTo = new EqualsTo();
            equalsTo.setLeftExpression(new Column("1"));
            equalsTo.setRightExpression(new LongValue(0));
            if (where != null) {
                return new AndExpression(where, equalsTo);
            } else {
                return equalsTo;
            }
        }
        //2.获取当前用户
        try {
            String employeeNum = SecurityConstants.getOperationDTO().getPersonInfoVO().getEmployeeNum();
            if ("78252".equals(employeeNum) || "98491".equals(employeeNum) || "110485".equals(employeeNum) || "87358".equals(employeeNum) || "55482".equals(employeeNum) || "100772".equals(employeeNum) || "104181".equals(employeeNum) || "61577".equals(employeeNum) || "101883".equals(employeeNum)) {
                return where;
            }
        } catch (Exception e) {
            log.error("DataScopeInterceptor::getSqlSegment::上下文无法获取人员工号！！{}", e.getMessage());
            e.printStackTrace();
        }
        String token = SecurityConstants.getLocalMap().containsKey("header-access-token") ? SecurityConstants.get("header-access-token") : "无token";
        Long userId = SecurityConstants.getOperationDTO().getUserId();
        log.info("该用户:{}-{},返回token信息：{}", userId, SecurityConstants.getOperationDTO().getUserName(), token);
        // 获取动态参数
        String alias = (String) SecurityConstants.getLocalMap().get("alias");
        LogicOperatorEnum operator = (LogicOperatorEnum) SecurityConstants.getLocalMap().get("operator");
        //3.sql组装
        if (null != dataScopeProperty.getColumns() && !dataScopeProperty.getColumns().isEmpty()) {
            for (DataColumnProperty dataColumnProperty : dataScopeProperty.getColumns()) {
                DataColumnProperty col = dataColumnProperty;
                // 如果方法参数中设置了表别名和运算符则动态配置，否则直接用注解固定值
                if (StringUtils.hasText(alias)) {
                    col.setAlias(alias);
                }
                if (ObjectUtils.isNotEmpty(operator)) {
                    col.setOperator(operator);
                }
                StringBuffer strBuf = new StringBuffer();
                String name = strBuf.append(dataColumnProperty.getAlias()).append(".").append(dataColumnProperty.getName()).toString();
                // 执行部门”、“最新所属部门"数组
                String[] nameArr = col.getDeptNames();
                if (SqlColumnTypeEnum.AUTH_DEPT.equals(dataColumnProperty.getType()) && workerDeptInfoList.size() > LIST_SIZE && CollectionUtils.isNotEmpty(Arrays.asList(nameArr))) {
                    //部门权限-new-支持按“执行部门”、“最新所属部门”权限查询，逻辑为或关系
                    ParenthesedExpressionList itemsList = new ParenthesedExpressionList(workerDeptInfoList);
                    log.info("该用户:{}权限配置部门数为：{}-{}，转list后个数为：{}", userId, SecurityConstants.getOperationDTO().getUserName(), workerDeptInfoList.size(), workerDeptInfoList.size());
                    for (String colName : nameArr) { // foreach列名
                        Column column = new Column(col.getAlias() + "." + colName);
                        InExpression inExpr = new InExpression(column, itemsList);

                        if (expression == null) {
                            expression = inExpr;
                        } else {
                            if (LogicOperatorEnum.OR.equals(dataColumnProperty.getOperator())) {
                                expression = new OrExpression(expression, inExpr); // OR连接多列
                            } else {
                                expression = new AndExpression(expression, inExpr); // AND连接多列
                            }
//                            expression = new OrExpression(expression, inExpr); // OR连接多列
                        }
                        log.debug("部门列[{}]添加IN条件", colName);
                    }
                    //部门权限-old-废弃
//                    ParenthesedExpressionList itemsList = new ParenthesedExpressionList(workerDeptInfoList);
//                    log.info("该用户:{}权限配置部门数为：{}-{}，转list后个数为：{}", userId, SecurityConstants.getOperationDTO().getUserName(), workerDeptInfoList.size(), workerDeptInfoList.size());
//                    InExpression inExpression = new InExpression(new Column(name), itemsList);
//                    expression = ObjectUtils.isNotEmpty(expression) ? new OrExpression(expression, inExpression) : inExpression;
                } 
            }
        }
        if (ObjectUtils.isNotEmpty(expression) && ObjectUtils.isNotEmpty(where)) {
            return new AndSubWhereExpression(where, expression);
        } else if (ObjectUtils.isNotEmpty(expression) && ObjectUtils.isEmpty(where)) {
            return expression;
        }
        return where;
    }
}

最后一步就是配置MP的Bean添加拦截器插件了，这里要注意参数拦截插件要添加在数据权限插件之前，因为要在sql拼接之前设置方法参数，sql拼接才能获取到。

@Configuration
public class MybatisPlusPageConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisResultDataInterceptor interceptor = new MybatisResultDataInterceptor();
        //数据权限
        DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor();
        DataScopeInterceptor dataScopeInterceptor = new DataScopeInterceptor();
        dataPermissionInterceptor.setDataPermissionHandler(dataScopeInterceptor);
        //自定义参数拦截插件
        interceptor.addInnerInterceptor(new ParamInterceptor());
        //自定义数据权限插件
        interceptor.addInnerInterceptor(dataPermissionInterceptor);
        //分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL));
        // 乐观锁
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        // 防止全表更新与删除
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
        return interceptor;
    }
}

使用：
mapper加上注解@DataColumn设置拼接的部门数组
参数添加 @Param(“alias”) String alias, @Param(“operator”) LogicOperatorEnum operator，动态配置表别名和逻辑运算符
mapper.java

@DataScope(value = {@DataColumn(deptNames = {"dept_id", "last_dept_id"}, type = SqlColumnTypeEnum.AUTH_DEPT)})
    List<OperationInsuranceBaseVo> page(@Param("bo") OperationInsuranceBaseBo bo, @Param("alias") String alias, @Param("operator") LogicOperatorEnum operator);

service调用的时候，动态配置表别名和逻辑运算符
service.java

    public String page(OperationInsuranceBaseBo bo) {
        Page<OperationInsuranceBaseVo> page = new Page<>(bo.getPageNum(), bo.getPageSize());
        //如果外部用户,则数据过滤
        if (SecurityConstants.getOperationDTO().getPersonInfoVO().getEmployeeNum().startsWith("WB")) {
            bo.setInsuranceCompany(SecurityConstants.getOperationDTO().getPersonInfoVO().getDisplayName());
        }
        page = operationInsuranceBaseDao.page(page, bo,"base",LogicOperatorEnum.OR);
        return JSONUtil.toJsonStr(R.ok(page));
    }