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);
}
网友评论