找到sql里面参数字段占位符的位置，方便对字段进行加密存储

CCJSqlParserUtil工具是强大，难用也是真的，得分析sql的各种各样的表达式。从中递归找出业务需要的。

public class SqlExpressionAnalyzer {


    public static void main(String[] args) {
        String sql = "select id,user_name from sys_user t1,sys_office t2 where t1.id=t2.id and mobile =? and user_name like '%abc%' and user_name like ? and user_name like ?  and position=? and bbb in ('1','2') and ccc in (?,?) and sex='1' and (photo='abc' or headImg='abc' or user_name like '%abc%')  and nation='2'" +
                " and id in (select id from relation_user where acc=?) and user_name in (select user_name from table_3 where user_name in (?,?,?,?,?) and user_name in ('1','3','4')) " +
                "and mobile =(case when is_using=1 then '1' when is_using=0 then '2' else '3' end)";

        String updateSql = "update sys_user set mobile=?,user_name='张三' where mobile='1' and id='1'";

        String insertSql = "insert into sys_user (id,mobile,user_name) values (?,?,?)";

        String delSql = "delete from sys_user where mobile=? and user_name=? and mobile in (?,?,?,?,?) and user_name in ('李四','张三')";
        AnalyzerResult analyzerResult = parse(delSql);
        System.out.println(analyzerResult);
    }

    public static AnalyzerResult parse(BoundSql boundSql) {
        return null != boundSql ? parse(boundSql.getSql()) : AnalyzerResult.empty();
    }

    public static AnalyzerResult parse(String sql) {
        Statement statement;
        try {
            statement = CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            throw new LocalException(e.getMessage());
        }
        if (statement instanceof Select) {
            return analyzeSelect((Select) statement);
        } else if (statement instanceof Update) {
            return analyzeUpdate((Update) statement);
        } else if (statement instanceof Insert) {
            return analyzerInsert((Insert) statement);
        } else if (statement instanceof Delete) {
            return analyzerDelete((Delete) statement);
        }
        return AnalyzerResult.empty();

    }

    private static AnalyzerResult analyzerInsert(Insert insert) {
        AnalyzerResult analyzerResult = new AnalyzerResult(insert.getTable());
        List<Column> columns = insert.getColumns();
        List<Expression> setExpressionList = insert.getSetExpressionList();
        if (null != setExpressionList && setExpressionList.size() == columns.size()) {
            analyzerResult.putResults(columns, setExpressionList);
        } else {
            ItemsList itemsList = insert.getItemsList();
            if (itemsList instanceof ExpressionList) {
                ExpressionList expressionList = (ExpressionList) itemsList;
                List<Expression> expressions = expressionList.getExpressions();
                analyzerResult.putResults(columns, expressions);
            } else if (itemsList instanceof MultiExpressionList) {
                MultiExpressionList multiExpressionList = (MultiExpressionList) itemsList;
                List<ExpressionList> exprList = multiExpressionList.getExprList();
                for (ExpressionList expressionList : exprList) {
                    List<Expression> expressions = expressionList.getExpressions();
                    analyzerResult.putResults(columns, expressions);
                }
            }
        }
        return analyzerResult;
    }

    private static AnalyzerResult analyzerDelete(Delete delete) {
        AnalyzerResult result = new AnalyzerResult(delete.getTable());
        Expression where = delete.getWhere();
        analyzeWhere(where, result);
        if (result.isExitReplaceStr()) {
            result.setReplaceSql(delete.toString());
        }
        return result;
    }

    private static AnalyzerResult analyzeUpdate(Update update) {
        List<Table> tables = update.getTables();
        if (tables.isEmpty()) {
            return AnalyzerResult.empty();
        }
        AnalyzerResult result = new AnalyzerResult(tables.get(FIRST));

        List<Column> columns = update.getColumns();
        List<Expression> expressions = update.getExpressions();
        result.putResults(columns, expressions);

        Expression where = update.getWhere();
        analyzeWhere(where, result);
        if (result.isExitReplaceStr()) {
            result.setReplaceSql(update.toString());
        }
        return result;
    }

    private static AnalyzerResult analyzeSelect(Select select) {
        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
        // 分析表达式
        AnalyzerResult result = new AnalyzerResult((Table) plainSelect.getFromItem());

        analyzeWhere(plainSelect.getWhere(), result);
        // 替换sql
        if (result.isExitReplaceStr()) {
            result.setReplaceSql(plainSelect.toString());
        }
        return result;
    }

    private static void analyzeWhere(Expression expression, AnalyzerResult result) {
        if (null == expression) {
            return;
        }
        // 正常xxx xxx xxx
        if (expression instanceof BinaryExpression) {
            BinaryExpression binaryExpression = (BinaryExpression) expression;
            Expression leftExpression = binaryExpression.getLeftExpression();
            Expression rightExpression = binaryExpression.getRightExpression();

            boolean isColumn = leftExpression instanceof Column;
            boolean isString = rightExpression instanceof StringValue;
            boolean isJdbc = rightExpression instanceof JdbcParameter;
            boolean isValue = isString || isJdbc;
            if (isColumn && isValue) {
                Column column = (Column) leftExpression;
                boolean isLike = expression instanceof LikeExpression;
                result.putResult(column, rightExpression, isLike);
            } else {
                analyzeWhere(leftExpression, result);
                analyzeWhere(rightExpression, result);
            }
        // in查询
        } else if (expression instanceof InExpression) {
            InExpression binaryExpression = (InExpression) expression;
            Expression leftExpression = binaryExpression.getLeftExpression();
            ItemsList rightItemsList = binaryExpression.getRightItemsList();
            ItemsList leftItemsList = binaryExpression.getLeftItemsList();
            if (leftExpression instanceof Column) {
                Column column = (Column) leftExpression;
                boolean isInValue = false;
                ExpressionList expressionList = null;
                if (rightItemsList instanceof ExpressionList) {
                    isInValue = true;
                    expressionList = (ExpressionList) rightItemsList;
                } else if (leftItemsList instanceof ExpressionList) {
                    isInValue = true;
                    expressionList = (ExpressionList) leftItemsList;
                }
                if (isInValue) {
                    result.putResults(column, expressionList.getExpressions());
                } else {
                    if (rightItemsList instanceof Expression) {
                        analyzeWhere((Expression) rightItemsList, result);
                    }
                    if (leftItemsList instanceof Expression) {
                        analyzeWhere((Expression) leftItemsList, result);
                    }
                }
            }
        // 子语句
        } else if (expression instanceof Parenthesis) {
            Parenthesis parenthesis = (Parenthesis) expression;
            Expression parenthesisExpression = parenthesis.getExpression();
            analyzeWhere(parenthesisExpression, result);
        // 子查询
        } else if (expression instanceof SubSelect) {
            SubSelect subSelect = (SubSelect) expression;
            PlainSelect selectBody = (PlainSelect) subSelect.getSelectBody();
            Expression where = selectBody.getWhere();
            analyzeWhere(where, result);
        // case when里面的when条件
        } else if (expression instanceof CaseExpression) {
            CaseExpression caseExpression = (CaseExpression) expression;
            List<WhenClause> whenClauses = caseExpression.getWhenClauses();
            if (ZYListUtils.isNotEmptyList(whenClauses)) {
                for (WhenClause whenClause : whenClauses) {
                    Expression whenExpression = whenClause.getWhenExpression();
                    analyzeWhere(whenExpression, result);
                }
            }
        } else {
            log.warn("not handle expression" + expression.getClass());
        }
    }

}

@Data
public class AnalyzerResult {

    private boolean exitReplaceStr = false;

    private String replaceSql;

    private String tableName;

    private List<ColumnInfo> jdbcColumnInfos = new ArrayList<>();

    private List<ColumnInfo> signJdbcColumnInfos = new ArrayList<>();

    public AnalyzerResult(Table table) {
        this.tableName = table.getName();
    }

    public AnalyzerResult() {
    }

    public Set<Integer> getCryptIndexs() {
        Set<Integer> cryptIndexs = new HashSet<>();
        for (ColumnInfo jdbcColumnInfo : jdbcColumnInfos) {
            cryptIndexs.add(jdbcColumnInfo.getJdbcIndex());
        }
        return cryptIndexs;
    }

    public void putResults(Column column, List<Expression> expressions) {
        if (null != expressions) {
            for (Expression expression : expressions) {
                putResult(column, expression, false);
            }
        }
    }

    public void putResults(List<Column> columns, List<Expression> expressions) {
        if (ZYListUtils.isSingletonList(columns) || ZYListUtils.isEmptyList(expressions)) {
            return;
        }
        for (int i = 0; i < columns.size(); i++) {
            Column column = columns.get(i);
            Expression expression = expressions.get(i);
            this.putResult(column, expression);
        }
    }

    public void putResult(Column column, Expression expression) {
        putResult(column, expression, false);
    }

    public void putResult(Column column, Expression expression, boolean isLike) {
        if (SecurityColumnProviders.matchSecret(this.tableName, column)) {
            if (expression instanceof JdbcParameter) {
                JdbcParameter jdbcParameter = (JdbcParameter) expression;
                this.jdbcColumnInfos.add(new ColumnInfo(column, jdbcParameter));
            } else if (expression instanceof StringValue) {
                this.setExitReplace();
                toReplaceString((StringValue) expression, isLike);
            }
        }

        if (SecurityColumnProviders.matchSign(this.tableName, column)) {
            if (expression instanceof JdbcParameter) {
                JdbcParameter jdbcParameter = (JdbcParameter) expression;
                this.signJdbcColumnInfos.add(new ColumnInfo(column, jdbcParameter));
            }
        }

    }

    private void toReplaceString(StringValue rightExpression, boolean isLike) {
        String value = rightExpression.getValue();
        if (ZYStrUtils.isNull(value)) {
            return;
        }
        SecretProviders secretProviders = ZYSpringUtils.getBean(SecretProviders.class);
        if (null == secretProviders) {
            throw new LocalException("secretProviders is null");
        }
        if (isLike && value.startsWith("%") && value.endsWith("%")) {
            String valueItem = value.substring(1, value.length() - 2);
            String encryptValue = secretProviders.encrypt(valueItem);
            rightExpression.setValue("%" + encryptValue + "%");
        } else {
            rightExpression.setValue(secretProviders.encrypt(value));
        }
    }


    private void setExitReplace() {
        this.exitReplaceStr = true;
    }

    public static AnalyzerResult empty() {
        return new AnalyzerResult();
    }
}