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

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);
}