SQL解析(Jsqlparser)

Posted on

SQL解析(Jsqlparser)

前段时间主要研究了一下SQL语句的解析，主要的几个开源产品试用了一下。本文大概总结一下个人体会。 首先是ZQL，ZQL有个比较突出的优点是使用起来比较简单，基本上属于拿上手就可以用的。但支持的功能有限，selectItem可以是简单的表达式，但不支持查询作为一个selectItem，对于其他的子查询同样也不支持。 最终我选择使用了Jsqlparser，主要原因有两点： 1）功能强大，基本上能够覆盖所有的SQL语法（没有研究是不是包含了数据库特殊的关键字，如LIMIT ），包含UNION,GROUP BY,HAVING,ORDER BY,JOIN,SUB JOIN,SUB SELECT,FUNCTION等。支持SQL深层嵌套。 2）本身设计不错，使用起来感觉很灵活。Jsqlparser对于SQL的遍历采用了VISITOR模式可以很方便的遍历SQL语句。 下面主要介绍一下Jsqlparser在我的项目中的应用情况。 背景：通过SQL语句的解析，从而增强SQL语句增加特定的查询过滤条件（如：状态过滤、权限过滤等） 1）解析SQL 2）根据SQL中涉及的表增强SQL 3）重新生成ORACLE数据库的SQL Java代码 收藏代码

  1. CCJSqlParserManager parserManager = new CCJSqlParserManager();
  2. try {
  3. Select select = (Select) parserManager.parse(new StringReader(sql)); //解析SQL语句
  4. SelectBody body = select.getSelectBody();
  5. VisitContext vc = new VisitContext(filterContext, params);
  6. vc.setTableFilterFactory(tableFilterFactory);//表的字段过滤
  7. body.accept(new SelectVisitorImpl(vc)); //访问SQL并根据SQL中涉及的表来增强SQL
  8. ExpressionDeParser expressionDeParser = new ExpressionDeParser();
  9. StringBuffer stringBuffer = new StringBuffer();
  10. SelectDeParser deParser = new OracleSelectDeParser(expressionDeParser, stringBuffer); //针对ORACLE的SQL生成
  11. expressionDeParser.setSelectVisitor(deParser);
  12. expressionDeParser.setBuffer(stringBuffer);
  14. body.accept(deParser);
  15. return new FilterResult(deParser.getBuffer().toString(), vc.getResultSqlParams());
  16. } catch (JSQLParserException e) {
  17. throw new FilterException(e);

  18. } 

     CCJSqlParserManager parserManager = new CCJSqlParserManager();

 try {
     Select select = (Select) parserManager.parse(new StringReader(sql)); //解析SQL语句

     SelectBody body = select.getSelectBody();
     VisitContext vc = new VisitContext(filterContext, params);

     vc.setTableFilterFactory(tableFilterFactory);//表的字段过滤
     body.accept(new SelectVisitorImpl(vc)); //访问SQL并根据SQL中涉及的表来增强SQL

     ExpressionDeParser expressionDeParser = new ExpressionDeParser();
     StringBuffer stringBuffer = new StringBuffer();

     SelectDeParser deParser = new OracleSelectDeParser(expressionDeParser, stringBuffer); //针对ORACLE的SQL生成
     expressionDeParser.setSelectVisitor(deParser);

     expressionDeParser.setBuffer(stringBuffer);

        body.accept(deParser);
        return new FilterResult(deParser.getBuffer().toString(), vc.getResultSqlParams());

    } catch (JSQLParserException e) {
        throw new FilterException(e);

    }

接下去是各个VISITOR，用来访问解析后的SQL Java代码 收藏代码

  1. public class SelectVisitorImpl extends AbstractVisitor implements SelectVisitor {
  3. public SelectVisitorImpl(VisitContext ctx) {
  4. super(ctx);
  5. }
  7. @SuppressWarnings("unchecked")
  8. public void visit(PlainSelect ps) {
  9. //SELECT ITEM访问
  10. List selectItems = ps.getSelectItems();
  11. for (SelectItem item : selectItems) {
  12. item.accept(new SelectItemVisitorImpl(this.getContext()));
  13. }
  15. //FROM访问
  16. FromItem from = ps.getFromItem();
  17. FromItemVisitorImpl fv = new FromItemVisitorImpl(context);
  18. from.accept(fv);
  20. //查询条件访问
  21. if (ps.getWhere() != null) {
  22. ps.getWhere().accept(new ExpressionVisitorImpl(this.getContext()));
  23. }
  25. //过滤增强的条件
  26. if (fv.getEnhancedCondition() != null) {
  27. if (ps.getWhere() != null) {
  28. Expression expr = new Parenthesis(ps.getWhere());
  29. AndExpression and = new AndExpression(fv.getEnhancedCondition(), expr);
  30. ps.setWhere(and);
  31. } else {
  32. ps.setWhere(fv.getEnhancedCondition());
  33. }
  34. }
  36. //JOIN表的访问
  37. List joins = ps.getJoins();
  38. if (CollectionUtil.isNotEmpty(joins)) {
  39. for (Join join : joins) {
  40. FromItemVisitorImpl tempfv = new FromItemVisitorImpl(context);
  41. join.getRightItem().accept(tempfv);
  42. if (join.getOnExpression() != null) {
  43. join.getOnExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  44. if (tempfv.getEnhancedCondition() != null) {
  45. Expression expr = new Parenthesis(join.getOnExpression());
  46. AndExpression and = new AndExpression(tempfv.getEnhancedCondition(), expr);
  47. join.setOnExpression(and);
  48. }
  49. }
  50. }
  51. }
  53. //ORDER BY 访问
  54. List elements = ps.getOrderByElements();
  55. if (CollectionUtil.isNotEmpty(elements)) {
  56. for (OrderByElement e : elements) {
  57. e.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  58. }
  59. }
  61. //GROUP BY的HAVING访问
  62. if (ps.getHaving() != null) {
  63. ps.getHaving().accept(new ExpressionVisitorImpl(this.getContext()));
  64. }
  65. }
  67. @SuppressWarnings("unchecked")
  68. public void visit(Union un) {
  69. List selects = un.getPlainSelects();
  70. for (PlainSelect select : selects) {
  71. select.accept(new SelectVisitorImpl(this.getContext()));
  72. }
  73. List elements = un.getOrderByElements();
  74. if (CollectionUtil.isNotEmpty(elements)) {
  75. for (OrderByElement e : elements) {
  76. e.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  77. }
  78. }
  79. }
  81. }

public class SelectVisitorImpl extends AbstractVisitor implements SelectVisitor {

public SelectVisitorImpl(VisitContext ctx) {

    super(ctx);
}


@SuppressWarnings("unchecked")

public void visit(PlainSelect ps) {
    //SELECT ITEM访问

    List<SelectItem> selectItems = ps.getSelectItems();
    for (SelectItem item : selectItems) {

        item.accept(new SelectItemVisitorImpl(this.getContext()));
    }


    //FROM访问

    FromItem from = ps.getFromItem();
    FromItemVisitorImpl fv = new FromItemVisitorImpl(context);

    from.accept(fv);


    //查询条件访问
    if (ps.getWhere() != null) {

        ps.getWhere().accept(new ExpressionVisitorImpl(this.getContext()));
    }


    //过滤增强的条件

    if (fv.getEnhancedCondition() != null) {
        if (ps.getWhere() != null) {

            Expression expr = new Parenthesis(ps.getWhere());
            AndExpression and = new AndExpression(fv.getEnhancedCondition(), expr);

            ps.setWhere(and);
        } else {

            ps.setWhere(fv.getEnhancedCondition());
        }

    }


    //JOIN表的访问
    List<Join> joins = ps.getJoins();

    if (CollectionUtil.isNotEmpty(joins)) {
        for (Join join : joins) {

            FromItemVisitorImpl tempfv = new FromItemVisitorImpl(context);
            join.getRightItem().accept(tempfv);

            if (join.getOnExpression() != null) {
                join.getOnExpression().accept(new ExpressionVisitorImpl(this.getContext()));

                if (tempfv.getEnhancedCondition() != null) {
                    Expression expr = new Parenthesis(join.getOnExpression());

                    AndExpression and = new AndExpression(tempfv.getEnhancedCondition(), expr);
                    join.setOnExpression(and);

                }
            }

        }
    }


    //ORDER BY 访问

    List<OrderByElement> elements = ps.getOrderByElements();
    if (CollectionUtil.isNotEmpty(elements)) {

        for (OrderByElement e : elements) {
            e.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));

        }
    }


    //GROUP BY的HAVING访问

    if (ps.getHaving() != null) {
        ps.getHaving().accept(new ExpressionVisitorImpl(this.getContext()));

    }
}


@SuppressWarnings("unchecked")

public void visit(Union un) {
    List<PlainSelect> selects = un.getPlainSelects();

    for (PlainSelect select : selects) {
        select.accept(new SelectVisitorImpl(this.getContext()));

    }
    List<OrderByElement> elements = un.getOrderByElements();

    if (CollectionUtil.isNotEmpty(elements)) {
        for (OrderByElement e : elements) {

            e.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));
        }

    }
}

} Java代码 收藏代码

  1. public class SelectItemVisitorImpl extends AbstractVisitor implements SelectItemVisitor {
  3. public SelectItemVisitorImpl(VisitContext ctx) {
  4. super(ctx);
  5. }
  7. public void visit(AllColumns ac) {
  8. }
  10. public void visit(AllTableColumns atc) {
  11. }
  13. public void visit(SelectExpressionItem sei) {
  14. sei.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  15. }
  17. }

public class SelectItemVisitorImpl extends AbstractVisitor implements SelectItemVisitor {

public SelectItemVisitorImpl(VisitContext ctx) {

    super(ctx);
}


public void visit(AllColumns ac) {

}


public void visit(AllTableColumns atc) {
}


public void visit(SelectExpressionItem sei) {

    sei.getExpression().accept(new ExpressionVisitorImpl(this.getContext()));
}

} Java代码 收藏代码

  1. public class ItemsListVisitorImpl extends AbstractVisitor implements ItemsListVisitor {
  3. public ItemsListVisitorImpl(VisitContext ctx) {
  4. super(ctx);
  5. }
  7. public void visit(SubSelect ss) {
  8. ss.getSelectBody().accept(new SelectVisitorImpl(context));
  9. }
  11. @SuppressWarnings("unchecked")
  12. public void visit(ExpressionList el) {
  13. List list = el.getExpressions();
  14. if (CollectionUtil.isNotEmpty(list)) {
  15. for (Expression expr : list) {
  16. expr.accept(new ExpressionVisitorImpl(context));
  17. }
  18. }
  19. }
  21. }

public class ItemsListVisitorImpl extends AbstractVisitor implements ItemsListVisitor {

public ItemsListVisitorImpl(VisitContext ctx) {

    super(ctx);
}


public void visit(SubSelect ss) {

    ss.getSelectBody().accept(new SelectVisitorImpl(context));
}


@SuppressWarnings("unchecked")

public void visit(ExpressionList el) {
    List<Expression> list = el.getExpressions();

    if (CollectionUtil.isNotEmpty(list)) {
        for (Expression expr : list) {

            expr.accept(new ExpressionVisitorImpl(context));
        }

    }
}

} 如果FROM的内容是table的话，则根据table的增强配置对SQL增强 Java代码 收藏代码

  1. public class FromItemVisitorImpl extends AbstractVisitor implements FromItemVisitor {
  2. private String varPattern = "@\{\s/?(\w+)\s/?\}";
  3. private Expression enhancedCondition;
  5. public FromItemVisitorImpl(VisitContext ctx) {
  6. super(ctx);
  7. }
  9. public void visit(Table table) {
  10. Set filters = context.getTableFilterFactory().getTableFilter(table.getName());
  11. if (filters == null) {
  12. filters = Collections.emptySet();
  13. }
  14. for (FieldFilter ff : filters) {
  15. Column c = new Column(new Table(null, table.getAlias()), ff.getFieldName());
  16. JdbcParameter param = new JdbcParameter();
  17. Object fieldValue = getRawValue(ff.getFieldValue(), this.context.getFilterContext());
  18. Expression[] exps;
  19. if ("between".equalsIgnoreCase(ff.getOperator()) || "not between".equalsIgnoreCase(ff.getOperator())) {
  20. Object[] objs = (Object[]) fieldValue;
  21. this.getContext().getResultSqlParams().add(objs[0]);
  22. this.getContext().getResultSqlParams().add(objs[1]);
  23. exps = new Expression[] { c, param, param };
  24. } else if ("is null".equalsIgnoreCase(ff.getOperator()) || "is not null".equalsIgnoreCase(ff.getOperator())) {
  25. exps = new Expression[] { c };
  26. } else {
  27. this.getContext().getResultSqlParams().add(fieldValue);
  28. exps = new Expression[] { c, param };
  29. }
  30. Expression operator = this.getOperator(ff.getOperator(), exps);
  31. if (this.enhancedCondition != null) {
  32. enhancedCondition = new AndExpression(enhancedCondition, operator);
  33. } else {
  34. enhancedCondition = operator;
  35. }
  36. }
  37. }
  39. public void visit(SubSelect ss) {
  40. ss.getSelectBody().accept(new SelectVisitorImpl(this.getContext()));
  41. }
  43. public void visit(SubJoin sj) {
  44. Join join = sj.getJoin();
  45. join.getRightItem().accept(new FromItemVisitorImpl(this.getContext()));
  46. join.getOnExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  47. }
  49. private Expression getOperator(String op, Expression[] exp) {
  50. if ("=".equals(op)) {
  51. EqualsTo eq = new EqualsTo();
  52. eq.setLeftExpression(exp[0]);
  53. eq.setRightExpression(exp[1]);
  54. return eq;
  55. } else if (">".equals(op)) {
  56. GreaterThan gt = new GreaterThan();
  57. gt.setLeftExpression(exp[0]);
  58. gt.setRightExpression(exp[1]);
  59. return gt;
  60. } else if (">=".equals(op)) {
  61. GreaterThanEquals geq = new GreaterThanEquals();
  62. geq.setLeftExpression(exp[0]);
  63. geq.setRightExpression(exp[1]);
  64. return geq;
  65. } else if ("<".equals(op)) {
  66. MinorThan mt = new MinorThan();
  67. mt.setLeftExpression(exp[0]);
  68. mt.setRightExpression(exp[1]);
  69. return mt;
  70. } else if ("<=".equals(op)) {
  71. MinorThanEquals leq = new MinorThanEquals();
  72. leq.setLeftExpression(exp[0]);
  73. leq.setRightExpression(exp[1]);
  74. return leq;
  75. } else if ("<>".equals(op)) {
  76. NotEqualsTo neq = new NotEqualsTo();
  77. neq.setLeftExpression(exp[0]);
  78. neq.setRightExpression(exp[1]);
  79. return neq;
  80. } else if ("is null".equalsIgnoreCase(op)) {
  81. IsNullExpression isNull = new IsNullExpression();
  82. isNull.setNot(false);
  83. isNull.setLeftExpression(exp[0]);
  84. return isNull;
  85. } else if ("is not null".equalsIgnoreCase(op)) {
  86. IsNullExpression isNull = new IsNullExpression();
  87. isNull.setNot(true);
  88. isNull.setLeftExpression(exp[0]);
  89. return isNull;
  90. } else if ("like".equalsIgnoreCase(op)) {
  91. LikeExpression like = new LikeExpression();
  92. like.setNot(false);
  93. like.setLeftExpression(exp[0]);
  94. like.setRightExpression(exp[1]);
  95. return like;
  96. } else if ("not like".equalsIgnoreCase(op)) {
  97. LikeExpression nlike = new LikeExpression();
  98. nlike.setNot(true);
  99. nlike.setLeftExpression(exp[0]);
  100. nlike.setRightExpression(exp[1]);
  101. return nlike;
  102. } else if ("between".equalsIgnoreCase(op)) {
  103. Between bt = new Between();
  104. bt.setNot(false);
  105. bt.setLeftExpression(exp[0]);
  106. bt.setBetweenExpressionStart(exp[1]);
  107. bt.setBetweenExpressionEnd(exp[2]);
  108. return bt;
  109. } else if ("not between".equalsIgnoreCase(op)) {
  110. Between bt = new Between();
  111. bt.setNot(true);
  112. bt.setLeftExpression(exp[0]);
  113. bt.setBetweenExpressionStart(exp[1]);
  114. bt.setBetweenExpressionEnd(exp[2]);
  115. return bt;
  116. }
  117. throw new FilterException("Unknown operator:" + op);
  118. }
  120. protected Object getRawValue(Object value, Map context) {
  121. if (context == null) {
  122. return value;
  123. }
  124. if (value instanceof String) {
  125. String v = (String) value;
  126. Pattern pattern = Pattern.compile(varPattern);
  127. Matcher matcher = pattern.matcher(v);
  128. if (matcher.find()) {
  129. return context.get(matcher.group(1));
  130. }
  131. }
  132. return value;
  133. }
  135. public Expression getEnhancedCondition() {
  136. return enhancedCondition;
  137. }
  139. }

public class FromItemVisitorImpl extends AbstractVisitor implements FromItemVisitor {

private String varPattern = "@\\{\\s/*?(\\w+)\\s/*?\\}";
private Expression enhancedCondition;


public FromItemVisitorImpl(VisitContext ctx) {

    super(ctx);
}


public void visit(Table table) {

    Set<FieldFilter> filters = context.getTableFilterFactory().getTableFilter(table.getName());
    if (filters == null) {

        filters = Collections.emptySet();
    }

    for (FieldFilter ff : filters) {
        Column c = new Column(new Table(null, table.getAlias()), ff.getFieldName());

        JdbcParameter param = new JdbcParameter();
        Object fieldValue = getRawValue(ff.getFieldValue(), this.context.getFilterContext());

        Expression[] exps;
        if ("between".equalsIgnoreCase(ff.getOperator()) || "not between".equalsIgnoreCase(ff.getOperator())) {

            Object[] objs = (Object[]) fieldValue;
            this.getContext().getResultSqlParams().add(objs[0]);

            this.getContext().getResultSqlParams().add(objs[1]);
            exps = new Expression[] { c, param, param };

        } else if ("is null".equalsIgnoreCase(ff.getOperator()) || "is not null".equalsIgnoreCase(ff.getOperator())) {
            exps = new Expression[] { c };

        } else {
            this.getContext().getResultSqlParams().add(fieldValue);

            exps = new Expression[] { c, param };
        }

        Expression operator = this.getOperator(ff.getOperator(), exps);
        if (this.enhancedCondition != null) {

            enhancedCondition = new AndExpression(enhancedCondition, operator);
        } else {

            enhancedCondition = operator;
        }

    }
}


public void visit(SubSelect ss) {

    ss.getSelectBody().accept(new SelectVisitorImpl(this.getContext()));
}


public void visit(SubJoin sj) {

    Join join = sj.getJoin();
    join.getRightItem().accept(new FromItemVisitorImpl(this.getContext()));

    join.getOnExpression().accept(new ExpressionVisitorImpl(this.getContext()));
}


private Expression getOperator(String op, Expression[] exp) {

    if ("=".equals(op)) {
        EqualsTo eq = new EqualsTo();

        eq.setLeftExpression(exp[0]);
        eq.setRightExpression(exp[1]);

        return eq;
    } else if (">".equals(op)) {

        GreaterThan gt = new GreaterThan();
        gt.setLeftExpression(exp[0]);

        gt.setRightExpression(exp[1]);
        return gt;

    } else if (">=".equals(op)) {
        GreaterThanEquals geq = new GreaterThanEquals();

        geq.setLeftExpression(exp[0]);
        geq.setRightExpression(exp[1]);

        return geq;
    } else if ("<".equals(op)) {

        MinorThan mt = new MinorThan();
        mt.setLeftExpression(exp[0]);

        mt.setRightExpression(exp[1]);
        return mt;

    } else if ("<=".equals(op)) {
        MinorThanEquals leq = new MinorThanEquals();

        leq.setLeftExpression(exp[0]);
        leq.setRightExpression(exp[1]);

        return leq;
    } else if ("<>".equals(op)) {

        NotEqualsTo neq = new NotEqualsTo();
        neq.setLeftExpression(exp[0]);

        neq.setRightExpression(exp[1]);
        return neq;

    } else if ("is null".equalsIgnoreCase(op)) {
        IsNullExpression isNull = new IsNullExpression();

        isNull.setNot(false);
        isNull.setLeftExpression(exp[0]);

        return isNull;
    } else if ("is not null".equalsIgnoreCase(op)) {

        IsNullExpression isNull = new IsNullExpression();
        isNull.setNot(true);

        isNull.setLeftExpression(exp[0]);
        return isNull;

    } else if ("like".equalsIgnoreCase(op)) {
        LikeExpression like = new LikeExpression();

        like.setNot(false);
        like.setLeftExpression(exp[0]);

        like.setRightExpression(exp[1]);
        return like;

    } else if ("not like".equalsIgnoreCase(op)) {
        LikeExpression nlike = new LikeExpression();

        nlike.setNot(true);
        nlike.setLeftExpression(exp[0]);

        nlike.setRightExpression(exp[1]);
        return nlike;

    } else if ("between".equalsIgnoreCase(op)) {
        Between bt = new Between();

        bt.setNot(false);
        bt.setLeftExpression(exp[0]);

        bt.setBetweenExpressionStart(exp[1]);
        bt.setBetweenExpressionEnd(exp[2]);

        return bt;
    } else if ("not between".equalsIgnoreCase(op)) {

        Between bt = new Between();
        bt.setNot(true);

        bt.setLeftExpression(exp[0]);
        bt.setBetweenExpressionStart(exp[1]);

        bt.setBetweenExpressionEnd(exp[2]);
        return bt;

    }
    throw new FilterException("Unknown operator:" + op);

}


protected Object getRawValue(Object value, Map<String, Object> context) {
    if (context == null) {

        return value;
    }

    if (value instanceof String) {
        String v = (String) value;

        Pattern pattern = Pattern.compile(varPattern);
        Matcher matcher = pattern.matcher(v);

        if (matcher.find()) {
            return context.get(matcher.group(1));

        }
    }

    return value;
}


public Expression getEnhancedCondition() {

    return enhancedCondition;
}

} 1）对JDBC parameter做了处理，如果参数为NULL则自动忽略该parameter，忽略后需要处理and or between 等情况 如：where name=? and age=? ，假如name对应的参数为null，则条件改为where 1=1 and age=?，如果是or的话则改为 where 1=0 or age=? Java代码 收藏代码

  1. public class ExpressionVisitorImpl extends AbstractVisitor implements ExpressionVisitor {
  3. public ExpressionVisitorImpl(VisitContext ctx) {
  4. super(ctx);
  5. }
  7. public void visit(NullValue nv) {
  8. }
  10. public void visit(Function f) {
  11. }
  13. public void visit(InverseExpression ie) {
  14. }
  16. public void visit(JdbcParameter jp) {
  17. this.getContext().getResultSqlParams().add(context.removeFirstParam());
  18. }
  20. public void visit(DoubleValue dv) {
  21. }
  23. public void visit(LongValue lv) {
  24. }
  26. public void visit(DateValue dv) {
  27. }
  29. public void visit(TimeValue tv) {
  30. }
  32. public void visit(TimestampValue tv) {
  33. }
  35. public void visit(Parenthesis parenthesis) {
  36. ExpressionVisitorImpl ev = new ExpressionVisitorImpl(context);
  37. parenthesis.getExpression().accept(ev);
  38. if (ev.isNotValid()) {
  39. parenthesis.setExpression(this.createTrueEquals());
  40. }
  41. }
  43. public void visit(StringValue s) {
  44. }
  46. public void visit(Addition a) {
  47. }
  49. public void visit(Division d) {
  50. }
  52. public void visit(Multiplication m) {
  53. }
  55. public void visit(Subtraction s) {
  56. }
  58. public void visit(AndExpression and) {
  59. ExpressionVisitorImpl left = new ExpressionVisitorImpl(this.getContext());
  60. and.getLeftExpression().accept(left);
  61. if (left.isNotValid()) {
  62. and.setLeftExpression(this.createTrueEquals());
  63. }
  64. ExpressionVisitorImpl right = new ExpressionVisitorImpl(this.getContext());
  65. and.getRightExpression().accept(right);
  66. if (right.isNotValid()) {
  67. and.setRightExpression(this.createTrueEquals());
  68. }
  69. }
  71. public void visit(OrExpression or) {
  72. ExpressionVisitorImpl left = new ExpressionVisitorImpl(this.getContext());
  73. or.getLeftExpression().accept(left);
  74. if (left.isNotValid()) {
  75. or.setLeftExpression(this.createFalseEquals());
  76. }
  77. ExpressionVisitorImpl right = new ExpressionVisitorImpl(this.getContext());
  78. or.getRightExpression().accept(right);
  79. if (right.isNotValid()) {
  80. or.setRightExpression(this.createFalseEquals());
  81. }
  82. }
  84. public void visit(Between btw) {
  85. Expression start = btw.getBetweenExpressionStart();
  86. Expression end = btw.getBetweenExpressionEnd();
  87. if (start instanceof JdbcParameter && end instanceof JdbcParameter) {
  88. Object o1 = this.context.getFirstParam();
  89. Object o2 = this.context.getParam(1);
  90. if (o1 == null || o2 == null) {
  91. this.context.removeFirstParam();
  92. this.context.removeFirstParam();
  93. this.setValid(false);
  94. return;
  95. }
  96. } else if (start instanceof JdbcParameter || end instanceof JdbcParameter) {
  97. Object o1 = this.context.getFirstParam();
  98. if (o1 == null) {
  99. this.context.removeFirstParam();
  100. this.setValid(false);
  101. return;
  102. }
  103. }
  104. btw.getLeftExpression().accept(new ExpressionVisitorImpl(context));
  105. btw.getBetweenExpressionStart().accept(new ExpressionVisitorImpl(context));
  106. btw.getBetweenExpressionEnd().accept(new ExpressionVisitorImpl(context));
  107. }
  109. public void visit(EqualsTo eq) {
  110. if (eq.getRightExpression() instanceof JdbcParameter) {
  111. Object o = this.context.getFirstParam();
  112. if (o == null) {
  113. this.setValid(false);
  114. this.getContext().removeFirstParam();
  115. return;
  116. }
  117. }
  118. eq.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  119. eq.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  120. }
  122. public void visit(GreaterThan gt) {
  123. if (gt.getRightExpression() instanceof JdbcParameter) {
  124. Object o = this.context.getFirstParam();
  125. if (o == null) {
  126. this.setValid(false);
  127. this.getContext().removeFirstParam();
  128. }
  129. }
  130. gt.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  131. gt.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  132. }
  134. public void visit(GreaterThanEquals gte) {
  135. if (gte.getRightExpression() instanceof JdbcParameter) {
  136. Object o = this.context.getFirstParam();
  137. if (o == null) {
  138. this.setValid(false);
  139. this.getContext().removeFirstParam();
  140. }
  141. }
  142. gte.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  143. gte.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  144. }
  146. public void visit(InExpression in) {
  147. ItemsList list = in.getItemsList();
  148. list.accept(new ItemsListVisitorImpl(context));
  149. }
  151. public void visit(IsNullExpression ine) {
  152. }
  154. public void visit(LikeExpression le) {
  155. if (le.getRightExpression() instanceof JdbcParameter) {
  156. Object o = this.context.getFirstParam();
  157. if (o == null) {
  158. this.setValid(false);
  159. this.getContext().removeFirstParam();
  160. }
  161. }
  162. le.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  163. le.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  164. }
  166. public void visit(MinorThan mt) {
  167. if (mt.getRightExpression() instanceof JdbcParameter) {
  168. Object o = this.context.getFirstParam();
  169. if (o == null) {
  170. this.setValid(false);
  171. this.getContext().removeFirstParam();
  172. }
  173. }
  174. mt.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  175. mt.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  176. }
  178. public void visit(MinorThanEquals mte) {
  179. if (mte.getRightExpression() instanceof JdbcParameter) {
  180. Object o = this.context.getFirstParam();
  181. if (o == null) {
  182. this.setValid(false);
  183. this.getContext().removeFirstParam();
  184. }
  185. }
  186. mte.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  187. mte.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  188. }
  190. public void visit(NotEqualsTo neq) {
  191. if (neq.getRightExpression() instanceof JdbcParameter) {
  192. Object o = this.context.getFirstParam();
  193. if (o == null) {
  194. this.setValid(false);
  195. this.getContext().removeFirstParam();
  196. return;
  197. }
  198. }
  199. neq.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  200. neq.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
  201. }
  203. public void visit(Column c) {
  204. }
  206. public void visit(SubSelect ss) {
  207. ss.getSelectBody().accept(new SelectVisitorImpl(context));
  208. }
  210. public void visit(CaseExpression ce) {
  211. }
  213. public void visit(WhenClause wc) {
  214. }
  216. public void visit(ExistsExpression ee) {
  217. }
  219. public void visit(AllComparisonExpression ace) {
  220. }
  222. public void visit(AnyComparisonExpression ace) {
  223. }
  225. public void visit(Concat c) {
  226. }
  228. public void visit(Matches m) {
  229. }
  231. public void visit(BitwiseAnd ba) {
  232. }
  234. public void visit(BitwiseOr bo) {
  235. }
  237. public void visit(BitwiseXor bx) {
  238. }
  240. private EqualsTo createTrueEquals() {
  241. EqualsTo eq = new EqualsTo();
  242. eq.setLeftExpression(new LongValue("1"));
  243. eq.setRightExpression(new LongValue("1"));
  244. return eq;
  245. }
  247. private EqualsTo createFalseEquals() {
  248. EqualsTo eq = new EqualsTo();
  249. eq.setLeftExpression(new LongValue("1"));
  250. eq.setRightExpression(new LongValue("0"));
  251. return eq;
  252. }
  254. }

public class ExpressionVisitorImpl extends AbstractVisitor implements ExpressionVisitor {

public ExpressionVisitorImpl(VisitContext ctx) {

    super(ctx);
}


public void visit(NullValue nv) {

}


public void visit(Function f) {
}


public void visit(InverseExpression ie) {

}


public void visit(JdbcParameter jp) {
    this.getContext().getResultSqlParams().add(context.removeFirstParam());

}


public void visit(DoubleValue dv) {
}


public void visit(LongValue lv) {

}


public void visit(DateValue dv) {
}


public void visit(TimeValue tv) {

}


public void visit(TimestampValue tv) {
}


public void visit(Parenthesis parenthesis) {

    ExpressionVisitorImpl ev = new ExpressionVisitorImpl(context);
    parenthesis.getExpression().accept(ev);

    if (ev.isNotValid()) {
        parenthesis.setExpression(this.createTrueEquals());

    }
}


public void visit(StringValue s) {

}


public void visit(Addition a) {
}


public void visit(Division d) {

}


public void visit(Multiplication m) {
}


public void visit(Subtraction s) {

}


public void visit(AndExpression and) {
    ExpressionVisitorImpl left = new ExpressionVisitorImpl(this.getContext());

    and.getLeftExpression().accept(left);
    if (left.isNotValid()) {

        and.setLeftExpression(this.createTrueEquals());
    }

    ExpressionVisitorImpl right = new ExpressionVisitorImpl(this.getContext());
    and.getRightExpression().accept(right);

    if (right.isNotValid()) {
        and.setRightExpression(this.createTrueEquals());

    }
}


public void visit(OrExpression or) {

    ExpressionVisitorImpl left = new ExpressionVisitorImpl(this.getContext());
    or.getLeftExpression().accept(left);

    if (left.isNotValid()) {
        or.setLeftExpression(this.createFalseEquals());

    }
    ExpressionVisitorImpl right = new ExpressionVisitorImpl(this.getContext());

    or.getRightExpression().accept(right);
    if (right.isNotValid()) {

        or.setRightExpression(this.createFalseEquals());
    }

}


public void visit(Between btw) {
    Expression start = btw.getBetweenExpressionStart();

    Expression end = btw.getBetweenExpressionEnd();
    if (start instanceof JdbcParameter && end instanceof JdbcParameter) {

        Object o1 = this.context.getFirstParam();
        Object o2 = this.context.getParam(1);

        if (o1 == null || o2 == null) {
            this.context.removeFirstParam();

            this.context.removeFirstParam();
            this.setValid(false);

            return;
        }

    } else if (start instanceof JdbcParameter || end instanceof JdbcParameter) {
        Object o1 = this.context.getFirstParam();

        if (o1 == null) {
            this.context.removeFirstParam();

            this.setValid(false);
            return;

        }
    }

    btw.getLeftExpression().accept(new ExpressionVisitorImpl(context));
    btw.getBetweenExpressionStart().accept(new ExpressionVisitorImpl(context));

    btw.getBetweenExpressionEnd().accept(new ExpressionVisitorImpl(context));
}


public void visit(EqualsTo eq) {

    if (eq.getRightExpression() instanceof JdbcParameter) {
        Object o = this.context.getFirstParam();

        if (o == null) {
            this.setValid(false);

            this.getContext().removeFirstParam();
            return;

        }
    }

    eq.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    eq.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(GreaterThan gt) {
    if (gt.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

        }
    }

    gt.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    gt.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(GreaterThanEquals gte) {
    if (gte.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

        }
    }

    gte.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    gte.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(InExpression in) {
    ItemsList list = in.getItemsList();

    list.accept(new ItemsListVisitorImpl(context));
}


public void visit(IsNullExpression ine) {

}


public void visit(LikeExpression le) {
    if (le.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

        }
    }

    le.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    le.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(MinorThan mt) {
    if (mt.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

        }
    }

    mt.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    mt.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(MinorThanEquals mte) {
    if (mte.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

        }
    }

    mte.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));
    mte.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));

}


public void visit(NotEqualsTo neq) {
    if (neq.getRightExpression() instanceof JdbcParameter) {

        Object o = this.context.getFirstParam();
        if (o == null) {

            this.setValid(false);
            this.getContext().removeFirstParam();

            return;
        }

    }
    neq.getLeftExpression().accept(new ExpressionVisitorImpl(this.getContext()));

    neq.getRightExpression().accept(new ExpressionVisitorImpl(this.getContext()));
}


public void visit(Column c) {

}


public void visit(SubSelect ss) {
    ss.getSelectBody().accept(new SelectVisitorImpl(context));

}


public void visit(CaseExpression ce) {
}


public void visit(WhenClause wc) {

}


public void visit(ExistsExpression ee) {
}


public void visit(AllComparisonExpression ace) {

}


public void visit(AnyComparisonExpression ace) {
}


public void visit(Concat c) {

}


public void visit(Matches m) {
}


public void visit(BitwiseAnd ba) {

}


public void visit(BitwiseOr bo) {
}


public void visit(BitwiseXor bx) {

}


private EqualsTo createTrueEquals() {
    EqualsTo eq = new EqualsTo();

    eq.setLeftExpression(new LongValue("1"));
    eq.setRightExpression(new LongValue("1"));

    return eq;
}


private EqualsTo createFalseEquals() {

    EqualsTo eq = new EqualsTo();
    eq.setLeftExpression(new LongValue("1"));

    eq.setRightExpression(new LongValue("0"));
    return eq;

}

} 增强后SQL语句的重新生成，根据ORACLE的语法重写了几个生成的方法 Java代码 收藏代码

  1. public class OracleSelectDeParser extends SelectDeParser {
  3. public OracleSelectDeParser(ExpressionDeParser expressionDeParser, StringBuffer sb) {
  4. super(expressionDeParser, sb);
  5. }
  7. ///
  8. /* 重写父类方法，去掉父类方法中table前的as
  9. /*/
  10. public void visit(Table tableName) {
  11. buffer.append(tableName.getWholeTableName());
  12. String alias = tableName.getAlias();
  13. if (alias != null && StringUtil.isNotEmpty(alias)) {
  14. buffer.append(" ");
  15. buffer.append(alias);
  16. }
  17. }
  19. ///
  20. /* 重写父类方法，在JOIN之前增加空格
  21. /*/
  22. @SuppressWarnings("unchecked")
  23. public void deparseJoin(Join join) {
  24. if (join.isSimple()) {
  25. buffer.append(", ");
  26. } else {
  27. buffer.append(" ");
  28. if (join.isRight()) {
  29. buffer.append("RIGHT ");
  30. } else if (join.isNatural()) {
  31. buffer.append("NATURAL ");
  32. } else if (join.isFull()) {
  33. buffer.append("FULL ");
  34. } else if (join.isLeft()) {
  35. buffer.append("LEFT ");
  36. }
  37. if (join.isOuter()) {
  38. buffer.append("OUTER ");
  39. } else if (join.isInner()) {
  40. buffer.append("INNER ");
  41. }
  42. buffer.append("JOIN ");
  43. }
  45. FromItem fromItem = join.getRightItem();
  46. fromItem.accept(this);
  47. if (join.getOnExpression() != null) {
  48. buffer.append(" ON ");
  49. join.getOnExpression().accept(expressionVisitor);
  50. }
  51. if (join.getUsingColumns() != null) {
  52. buffer.append(" USING ( ");
  53. for (Iterator iterator = join.getUsingColumns().iterator(); iterator.hasNext();) {
  54. Column column = iterator.next();
  55. buffer.append(column.getWholeColumnName());
  56. if (iterator.hasNext()) {
  57. buffer.append(" ,");
  58. }
  59. }
  60. buffer.append(")");
  61. }
  62. }
  64. }

public class OracleSelectDeParser extends SelectDeParser {

public OracleSelectDeParser(ExpressionDeParser expressionDeParser, StringBuffer sb) {

    super(expressionDeParser, sb);
}


//*/*

 /* 重写父类方法，去掉父类方法中table前的as
 /*/

public void visit(Table tableName) {
    buffer.append(tableName.getWholeTableName());

    String alias = tableName.getAlias();
    if (alias != null && StringUtil.isNotEmpty(alias)) {

        buffer.append(" ");
        buffer.append(alias);

    }
}


//*/*

 /* 重写父类方法，在JOIN之前增加空格
 /*/

@SuppressWarnings("unchecked")
public void deparseJoin(Join join) {

    if (join.isSimple()) {
        buffer.append(", ");

    } else {
        buffer.append(" ");

        if (join.isRight()) {
            buffer.append("RIGHT ");

        } else if (join.isNatural()) {
            buffer.append("NATURAL ");

        } else if (join.isFull()) {
            buffer.append("FULL ");

        } else if (join.isLeft()) {
            buffer.append("LEFT ");

        }
        if (join.isOuter()) {

            buffer.append("OUTER ");
        } else if (join.isInner()) {

            buffer.append("INNER ");
        }

        buffer.append("JOIN ");
    }


    FromItem fromItem = join.getRightItem();

    fromItem.accept(this);
    if (join.getOnExpression() != null) {

        buffer.append(" ON ");
        join.getOnExpression().accept(expressionVisitor);

    }
    if (join.getUsingColumns() != null) {

        buffer.append(" USING ( ");
        for (Iterator<Column> iterator = join.getUsingColumns().iterator(); iterator.hasNext();) {

            Column column = iterator.next();
            buffer.append(column.getWholeColumnName());

            if (iterator.hasNext()) {
                buffer.append(" ,");

            }
        }

        buffer.append(")");
    }

}

}

希望本站内容对您有点用处,有什么疑问或建议请在后面留言评论
转载请注明作者(RobinChia)和出处 It so life ，请勿用于任何商业用途
本文链接: SQL解析(Jsqlparser)