美文网首页
JSQLParser 来分析复杂SQL,实现UI业务一次SQL搞

JSQLParser 来分析复杂SQL,实现UI业务一次SQL搞

作者: 凌凌柒LG | 来源:发表于2019-06-19 22:28 被阅读0次

    2007年使用ofbiz entity engine时,为了实现可以通过一个sql,查询到one 2 one, one 2 many, 甚至 one 2 one 2 many 2 one的查询,自己废了不少功夫做了一个SQL引擎,当时自己把他叫OSQL,主要思路是基于CA USVD以及HQL,但HQL不支持更多的关系,而且一不小心性能崩溃。比如:

    Select Project.name, Porject.PM.name, Project.Department.Name, Project.budget, Project .startDate from Project where Project.Members.User.id in (1001, 1002, 1003)。

    今天Github看到一个SQL解析引擎JSQLParser不错。可以讲SQL变成Java对象系列,举个栗子(为方便一路看下去,就不处理各种异常和逻辑了):

    String sql = "select t1 from table1 t1 union all select t2.b,t3.c from table2 t2,table3 t3 where t2.b = t3.c";

    Statement stmt = CCJSqlParserUtil.parse(sql);

    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    PlainSelect plainSelect = (PlainSelect) selectBody;

    //From分析
    FromItem fromItem = plainSelect.getFromItem();
    Table table = (Table) fromItem; // table.getAlias().getName(), table.getName()

    // join分析
    List<Join> joins = plainSelect.getJoins();
    for (Join join : joins) {
    FromItem rightItem = join.getRightItem();
    Table table = (Table) rightItem; // (table.getAlias().getName(), table.getName());

    // on 条件分析
    Expression onExpression = join.getOnExpression();  //和where一样
    

    }

    // where 分析
    plainSelect.getWhere()

    // where 只有两种情况,一种就是简单的a = b,另外就是 a = b or ... and, 这种就是AndExpression
    if (where instanceof EqualsTo) {
    EqualsTo equalsTo = (EqualsTo) expression;
    Expression rightExpression = equalsTo.getRightExpression();
    Expression leftExpression = equalsTo.getLeftExpression();
    if (rightExpression instanceof Column && leftExpression instanceof Column) {
    Column rightColumn = (Column) rightExpression;
    Column leftColumn = (Column) leftExpression;
    System.out.println(tableMap.get(rightColumn.getTable().toString()) + "表的" +rightColumn.getColumnName() + "字段 -> "+ tableMap.get(leftColumn.getTable().toString()) + "表的" + leftColumn.getColumnName() + "字段");
    }
    }else if(where instanceof AndExpression){
    AndExpression andExpression = (AndExpression) expression;
    Expression leftExpression = andExpression.getLeftExpression();
    doExpression(leftExpression);
    Expression rightExpression = andExpression.getRightExpression();
    doExpression(rightExpression);
    }

    相关文章

      网友评论

          本文标题:JSQLParser 来分析复杂SQL,实现UI业务一次SQL搞

          本文链接:https://www.haomeiwen.com/subject/pjnlqctx.html