Dynamically filter SQL query fields MyBatis

In the permission system, the visible permission of data is usually controlled. Generally, there will be the control of the number of visible data rows and the control of data return columns. This paper mainly designs the technical design of column control.

requirement analysis

Filter the data fields of SQL query results in MyBatis. For example, the main execution of SQL is as follows

select a.name,a.id,b.name,b.id from user a left join dept b on a.dept_id=b.id 

In the business scenario, the id of the dept table should not be returned as a result.

According to the above requirements, you can modify the SQL statements to be executed and remove unnecessary data fields. Technology to be used: SQL parsing (net.sf.jsqlparser).

Technical realization

This section describes how to parse sql and filter sql query results. First, you need to convert sql statements (strings) into objects in jsqlparser. The specific code is as follows.

    String sql = "select a.name,a.id,b.name,b.id from user a left join dept b on a.dept_id=b.id ";
    Select select = (Select) CCJSqlParserUtil.parse(sql);
    PlainSelect selectBody = (PlainSelect) select.getSelectBody();

Through the above code, we get the selectBody object, in which there are two key attributes

1. fromItem indicates the table information after the from keyword in the sql statement
2. joins represents the table information after the join keyword in the sql statement

In this example, the data information is shown in the figure.

It can be found that there are Table type data in the elements of joins and the fromItem object. The Table name can be obtained from the Table type. The specific acquisition code is as follows.

private static void extracted(Map<String, String> aliasMapTable, FromItem fromItem) {
  if (fromItem instanceof Table) {
    String name = ((Table) fromItem).getName();
    Alias alias = fromItem.getAlias();

    if (alias.getName() != null) {
      aliasMapTable.put(alias.getName(), name);
    } else {
      aliasMapTable.put(name, name);
    }
  }
}

So far, the relationship between alias and table real name in sql statement can be resolved. The subsequent problem to be solved is to filter the query fields in sql statement. First, you need to obtain the query fields in the sql statement. The acquisition code is as follows.

        List<SelectItem> selectItems = selectBody.getSelectItems();

In this example, the execution result of the above code is shown in the figure

It can be seen from the figure that the query results in the sql statement have been obtained successfully. Next, you need to remove the fields and design a storage container to store the visible fields of the data table. The design is as follows.

  static Map<String, List<String>> tableIncludeCol = new HashMap<>();

  static {
    List<String> a = new ArrayList<>();
    a.add("name");
    tableIncludeCol.put("user", a);
    List<String> b = new ArrayList<>();
    b.add("id");
    tableIncludeCol.put("dept", b);
  }

key: table name

value: visible field name

After the above contents are determined, the data needs to be removed. The removal operation code is as follows

    Iterator<SelectItem> iterator = selectItems.iterator();
    while (iterator.hasNext()) {
      SelectItem next = iterator.next();

      if (next instanceof SelectExpressionItem) {
        Alias alias = ((SelectExpressionItem) next).getAlias();
        Expression expression = ((SelectExpressionItem) next).getExpression();

        if (expression instanceof Column) {
          Table table = ((Column) expression).getTable();

          String columnName = ((Column) expression).getColumnName();

          String fullTableName = aliasMapTable.get(table.getName());
          if (!tableIncludeCol.get(fullTableName).contains(columnName)) {
            iterator.remove();
          }
        }
      }
    }

At this point, execute the code to view the sql statement, as follows.

SELECT a.name, b.id FROM user a LEFT JOIN dept b ON a.dept_id = b.id

It can be found that this effect is consistent with the previous configuration of visible fields.

In this way, we can also add fields, but it is not recommended. The author suggests to list all the fields at the beginning and then filter them. If you add fields dynamically, it will be more troublesome in the process (the change cost of Java objects).

With the above technical preparations, you can then combine with Mybatis. The core of the combination is to realize the Interceptor interface in Mybatis through interceptors. The specific implementation code is as follows.

@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Object.class})})
public class PermissionInterceptor implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger(PermissionInterceptor.class);


    static Map<String, List<String>> tableIncludeCol = new HashMap<>();

    static {
        List<String> a = new ArrayList<>();
        a.add("name");
        tableIncludeCol.put("user", a);
        List<String> b = new ArrayList<>();
        b.add("id");
        tableIncludeCol.put("dept", b);
    }

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // Determine whether filtering is required
        PermissionObject permissionObject = PermissionHelper.getLocalPermission();
        if (!permissionObject.getPermission()) {
            return invocation.proceed();
        } else {
            PermissionHelper.reset();
        }
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        String sql = (String) metaObject.getValue("delegate.boundSql.sql");
        Select select = (Select) CCJSqlParserUtil.parse(sql);
        PlainSelect selectBody = (PlainSelect) select.getSelectBody();

        Map<String, String> aliasMapTable = new HashMap<>(8);

        FromItem fromItem = selectBody.getFromItem();
        extracted(aliasMapTable, fromItem);

        List<Join> joins = selectBody.getJoins();

        for (Join join : joins) {
            FromItem rightItem = join.getRightItem();
            extracted(aliasMapTable, rightItem);
        }

        List<SelectItem> selectItems = selectBody.getSelectItems();

        Iterator<SelectItem> iterator = selectItems.iterator();

        while (iterator.hasNext()) {
            SelectItem next = iterator.next();

            if (next instanceof SelectExpressionItem) {
                Alias alias = ((SelectExpressionItem) next).getAlias();
                Expression expression = ((SelectExpressionItem) next).getExpression();

                if (expression instanceof Column) {
                    Table table = ((Column) expression).getTable();

                    String columnName = ((Column) expression).getColumnName();

                    String fullTableName = aliasMapTable.get(table.getName());
                    if (!tableIncludeCol.get(fullTableName).contains(columnName)) {
                        iterator.remove();
                    }
                }
            }
        }
        selectBody.setSelectItems(selectItems);
        System.out.println(select);
        metaObject.setValue("delegate.boundSql.sql", select.toString());
        return invocation.proceed();
    }

    private void extracted(Map<String, String> aliasMapTable, FromItem fromItem) {
        if (fromItem instanceof Table) {
            String name = ((Table) fromItem).getName();
            Alias alias = fromItem.getAlias();

            if (alias.getName() != null) {
                aliasMapTable.put(alias.getName(), name);
            } else {
                aliasMapTable.put(name, name);
            }
        }
    }

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

    @Override
    public void setProperties(Properties properties) {

    }
}