jsqlparser描述：

JSqlParser 解析 SQL 语句并将其转换为 Java 类的层次结构。基本上的sql关键字和函数都可以被jsqlparser解析成对象层层包装。

实现的功能：

基础sql查询，条件查询，字段和表得别名，排序，分组，聚合，case when，基本上都是有得。

我的使用场景：

将一个表和字段不是数据库直接查询的表字段，进行通过jsqlparser来转换成elasticsearch中存储的表和字段进行到openlokeng中进行查询

界面输入：

openlookeng 执行：

举例：

首先在页面输入：SELECT t1.公司名称 from gxb.新规则表5 t1
然后通过解析后：SELECT eas_file_197_1_1_1_1_1_2.column2 FROM eas_file_197_1_1_1_1_1_2

解析对象：

自定义函数：

实际使用：

@Injectprivate Metadata metadata;@Injectprivate DataManager dataManager;@Injectprivate TimeSource timeSource;@Injectprivate Logger log;@Injectprivate DataServiceConfig dataServiceConfig;@Injectprivate CustomConfig customConfig;private static final String CLASSIFY_NAME = "classifyName";private static final String TABLE_NAME = "tableName";private static final String TABLE_ALIAS = "tableAlias";private static final String STATUS = "status";private static final String SUCCESS = "success";private static final String FAILED = "failed";@Overridepublic Object execute(String sql, UserExt userExt, Page page){if(Objects.isNull(page)) page = new Page();long startTime = timeSource.currentTimeMillis();String operateResult = "执行成功";Map<String, Object> resultObj = new HashMap<>();List<HashMap<String, Object>> jsonArray = new ArrayList<>();try {Statements stmts = parseStatements(sql);Page finalPage = page;stmts.getStatements().forEach(stmt -> {if (stmt instanceof Select) {executeSelect(stmt,finalPage,jsonArray);}});resultObj.put("data", jsonArray);resultObj.put(STATUS, SUCCESS);} catch (JSQLParserException e) {operateResult = "执行失败,输入SQL语法错误";resultObj.put(STATUS, FAILED);throw new SQLParserException("输入SQL语法错误",e);}catch (Exception e){operateResult = "执行失败,接口处理异常";resultObj.put(STATUS, FAILED);throw new SQLParserException("接口处理异常",e);}finally {saveOperateHistory(sql, userExt, startTime, operateResult);}return resultObj;}/*** 具体执行 select 解析后返回 jsonArray 对象* @param stmt* @param finalPage* @param jsonArray*/private void executeSelect(Statement stmt, Page finalPage, List<HashMap<String, Object>> jsonArray) {Map<String, Object> tempMap = parseSelectSql((Select) stmt, finalPage);String paramSql = formatSql((String) tempMap.get("sql"));List<SqlStatementEasTableVo> tables = (List<SqlStatementEasTableVo>) tempMap.get("tables");Map<String, String> config = getDataServiceConfig(tables.get(0));String database = config.get("database");String schemaName = config.get("schema");log.info("数据服务入参：");log.info("sql: {}", paramSql);log.info("database: {} | schemaName: {}", database, schemaName);if(Objects.isNull(database) || Objects.isNull(schemaName) ){throw new SQLParserException("database 或 schema 为空 ");}Page resultPage = (Page) tempMap.get("page");RunQueryResponse response = invokeDataService(database, schemaName, paramSql);if(resultPage.isSelectAll()){resultPage.setTotalCount(response.getTotal().longValue());resultPage.setPageCount(getPageCount(response.getTotal().longValue(), finalPage.getPageSize()));}resultPage.setList(response.getResult());HashMap<String, Object> objHashMap = new HashMap<>();tempMap.put("page", resultPage);objHashMap.put("metas", tempMap);jsonArray.add(objHashMap);}private Map<String, String> getDataServiceConfig(SqlStatementEasTableVo entity) {EasTable easTable = getEasTable(entity.getClassifyName(), entity.getName());Map<String,String> resultMap = new HashMap<>();if(Objects.nonNull(easTable)){String database = Objects.isNull(easTable.getDatabase()) ? "" : easTable.getDatabase().getAlias();String schema = easTable.getSchema().getSchema().getName();resultMap.put("database", database);resultMap.put("schema", schema);}return resultMap;}private List<EasColumn> parseSelectItem(PlainSelect selectBody, List<Map<String, String>> tableItems) {List<EasColumn> resultColumn = new ArrayList<>();selectBody.getSelectItems().forEach(s -> s.accept(new SelectItemVisitorAdapter() {@Overridepublic void visit(AllColumns columns) {Map<String, String> tableMap = Objects.nonNull(tableItems.get(0)) ? tableItems.get(0) : new HashMap<>();EasTable easTable = getEasTable(tableMap.get(CLASSIFY_NAME), tableMap.get(TABLE_NAME));if (Objects.isNull(easTable)) return;List<SelectItem> list = new ArrayList<>();easTable.getColumn().forEach(c -> {Column column = initColumn(easTable.getTable().getName(), c.getColumn().getName());SelectExpressionItem selectExpressionItem = new SelectExpressionItem(column);list.add(selectExpressionItem);resultColumn.add(c);});selectBody.setSelectItems(list);}@Overridepublic void visit(AllTableColumns columns) {String name = columns.getTable().getName();Map<String, String> tableMap = tableItems.stream().filter(t -> StringUtils.equals(name, t.get(TABLE_ALIAS))).findFirst().orElse(new HashMap<>());EasTable easTable = getEasTable(tableMap.get(CLASSIFY_NAME), tableMap.get(TABLE_NAME));if (Objects.isNull(easTable)) return;List<SelectItem> list = new ArrayList<>();easTable.getColumn().forEach(c -> {Column column = initColumn(easTable.getTable().getName(), c.getColumn().getName());SelectExpressionItem selectExpressionItem = new SelectExpressionItem(column);list.add(selectExpressionItem);resultColumn.add(c);});selectBody.setSelectItems(list);}@Overridepublic void visit(SelectExpressionItem item) {//如果是条件查询的case,否则默认为字段switch (item.getExpression().getClass().getName()){case "net.sf.jsqlparser.expression.CaseExpression":caseExpression(item,tableItems,resultColumn);break;case "net.sf.jsqlparser.expression.Function":functionExpression(item,tableItems,resultColumn);break;default:basicExpression(item,tableItems,resultColumn);break;}}}));return resultColumn;}/*** 带有条件的 查询字段的进行拼接转换* @param item* @param tableItems* @param resultColumn*/private void caseExpression(SelectExpressionItem item,List<Map<String, String>> tableItems,List<EasColumn> resultColumn) {Column switchExpression = ((CaseExpression) item.getExpression()).getSwitchExpression(Column.class);EasColumn easColumn;//这里要处理 ，分情况， 如果是 没有switch 的时候if(Objects.isNull(switchExpression)){easColumn =noHaveSwitchExpression(item,tableItems);}else{easColumn =haveSwitchExpression(item,tableItems,switchExpression);}if (Objects.isNull(easColumn)) return ;//这里构造一个新的自定义的别名 column 给前端，为了展示EasColumn newEasColumn=customAlias(item,easColumn.getColumn().getName(),DimensionType.BUSINESS);resultColumn.add(newEasColumn);}/*** 没有switchExpression 表示*  case when  列名=值 then  ''*/private EasColumn noHaveSwitchExpression(SelectExpressionItem item, List<Map<String, String>> tableItems) {List<WhenClause> whenClauseList=new ArrayList<>();EasColumn easColumn = null;for (WhenClause i : ((CaseExpression) item.getExpression()).getWhenClauses()) {Column left = ((EqualsTo) i.getWhenExpression()).getLeftExpression(Column.class);Map<String, String> leftTableMap = tableItems.stream().filter(t -> StringUtils.equals(left.getTable().getName(), t.get(TABLE_ALIAS))).findFirst().orElse(new HashMap<>());//大于0 表示 匹配到别名 t1， 否则就用默认的 文件夹.表名.列名EasTable easTable;if(leftTableMap.size()>0){//  on  左边  转换的表名easTable = getEasTable(leftTableMap.get(CLASSIFY_NAME), leftTableMap.get(TABLE_NAME));}else{easTable = getEasTable(left.getTable().getSchemaName(), left.getTable().getName());}if (Objects.isNull(easTable)) return null;EasColumn leftColumn = easTable.getColumn().stream().filter(c -> StringUtils.equals(left.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(leftColumn)) return null;EasTable finalEasTable1 = easTable;WhenClause whenClause= i;BinaryExpression onExpression = new EqualsTo();onExpression.setLeftExpression(new Column(new Table(finalEasTable1.getTable().getName()),leftColumn.getColumn().getName()));onExpression.setRightExpression(((EqualsTo) i.getWhenExpression()).getRightExpression());whenClause.setWhenExpression(onExpression);whenClauseList.add(whenClause);//替换原有的case when 中文表名 -> case when es表名((CaseExpression) item.getExpression()).setWhenClauses(whenClauseList);easColumn = easTable.getColumn().stream().filter(c -> StringUtils.equals(left.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(easColumn)) return null;}return easColumn;}/*** 有switchExpression 表示*  case 列名  when 值 then  ''*/private EasColumn haveSwitchExpression(SelectExpressionItem item, List<Map<String, String>> tableItems, Column switchExpression) {Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, switchExpression));//重新赋值给 函数表达式EasColumn easColumn=(EasColumn)map.get("easColumn");EasTable easTable=(EasTable)map.get("easTable");//将获取转化后的表.列，重新赋值给 switchExpression对象EasTable finalEasTable = easTable;item.getExpression().accept(new ExpressionVisitorAdapter(){@Overridepublic void visit(CaseExpression caseExpression) {caseExpression.setSwitchExpression(initColumn(finalEasTable.getTable().getName(), easColumn.getColumn().getName()));}});return easColumn;}/*** 带有条件的 查询字段的进行拼接转换* @param item* @param tableItems* @param resultColumn*/private void functionExpression(SelectExpressionItem item,List<Map<String, String>> tableItems,List<EasColumn> resultColumn) {List<Expression> expressions=new ArrayList<>();ExpressionList functionExpressions= ((net.sf.jsqlparser.expression.Function)item.getExpression()).getParameters();if(Objects.nonNull(functionExpressions)){//这里表示的 是 count + 具体字段List<Expression>  expressionList= functionExpressions.getExpressions();expressionList.forEach(expression -> {//判断是否是自定义日期函数if(((net.sf.jsqlparser.expression.Function)item.getExpression()).getMultipartName().get(0).equals(customConfig.getDoTimeFunction())){functionItem(expression,tableItems,expressions,item,resultColumn,expressionList.get(1));}else{functionItem(expression,tableItems,expressions,item,resultColumn,null);}});}else{//这里表示 的是 count (*)//这里构造一个新的自定义的别名 column 给前端，为了展示EasColumn newEasColumn=customAlias(item,item.getAlias().getName(),DimensionType.BUSINESS);resultColumn.add(newEasColumn);}}/*** 函数 count + 具体字段*/private void functionItem(Expression expression, List<Map<String, String>> tableItems, List<Expression> expressions, SelectExpressionItem item, List<EasColumn> resultColumn,Expression value) {if(expression instanceof Column){Column functionExpression = (Column) expression;item.getExpression().accept(new ExpressionVisitorAdapter(){@Overridepublic void visit(ExpressionList expression) {Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, functionExpression));Column newColumn=(Column) map.get("newColumn");EasColumn easColumn =(EasColumn) map.get("easColumn");//重新赋值给 函数表达式expressions.add(newColumn);expression.setExpressions(expressions);//这里构造一个新的自定义的别名 column 给前端，为了展示EasColumn newEasColumn=customAlias(item,easColumn.getColumn().getName(),easColumn.getDimensionType());resultColumn.add(newEasColumn);net.sf.jsqlparser.expression.Function function=((net.sf.jsqlparser.expression.Function)item.getExpression());List<String> customerFunctionNames=function.getMultipartName();//这里要判断下自定义时间函数for(String customerFunctionName:customerFunctionNames) {if (Objects.nonNull(customerFunctionName) &&customerFunctionName.equals(customConfig.getDoTimeFunction())&& Objects.nonNull(value)) {net.sf.jsqlparser.expression.Function expressFunction = ((net.sf.jsqlparser.expression.Function) item.getExpression());expressFunction.setName("from_unixtime");ExpressionList list= customTimeFunction(value, expression);expressFunction.setParameters(list);}}}});}}/*** 自定义时间段分组函数*/private ExpressionList customTimeFunction(Expression value, ExpressionList expression) {//from_unixtime((to_unixtime(eas_file_493_2.column0) / 60 ) * 60)//1.上面已经拼好 eas_file_493_2.column0//2.然后拼 to_unixtime(eas_file_493_2.column0)ExpressionList list=new ExpressionList();//to_unixtimenet.sf.jsqlparser.expression.Function newFunction=new net.sf.jsqlparser.expression.Function();newFunction.setName("to_unixtime");newFunction.setParameters(expression);//3.再拼  to_unixtime(eas_file_493_2.column0) / 60Division division=new Division();division.setLeftExpression(newFunction);division.setRightExpression(value);//((to_unixtime(eas_file_493_2.column0) / 60 )ExpressionList expressionList1=new ExpressionList();expressionList1.addExpressions(division);net.sf.jsqlparser.expression.Function newFunction2=new net.sf.jsqlparser.expression.Function();newFunction2.setName("");newFunction2.setParameters(expressionList1);//4.最后拼  (to_unixtime(eas_file_493_2.column0) / 60 ) * 60Multiplication multiplication2=new Multiplication();multiplication2.setLeftExpression(newFunction2);multiplication2.setRightExpression(value);list.addExpressions(multiplication2);return list;}/*** 根据 是否有别名，执行对应的操作。*/private  void basicExpression( SelectExpressionItem item,List<Map<String, String>> tableItems,List<EasColumn>  resultColumn){Column expression = item.getExpression(Column.class);Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, expression));//重新赋值给 函数表达式EasColumn easColumn=(EasColumn)map.get("easColumn");EasTable easTable=(EasTable)map.get("easTable");//这里构造一个新的自定义的别名 column 给前端，为了展示EasColumn newEasColumn=customAlias(item,easColumn.getColumn().getName(),easColumn.getDimensionType());resultColumn.add(newEasColumn);item.setExpression(initColumn(easTable.getTable().getName(), easColumn.getColumn().getName()));}/*** 自定义字段别名， COLUMN AS ""* @param item* @param easColumnName* @param dimensionType* @return*/private static EasColumn customAlias (SelectExpressionItem item,String easColumnName,DimensionType dimensionType){//这里构造一个新的自定义的别名 column 给前端，为了展示EasColumn newEasColumn=new EasColumn();String tempName;if(item.getAlias() ==null ){tempName=easColumnName;}else{tempName=item.getAlias().getName().replace("\"", "");}newEasColumn.setOriginalName(tempName);newEasColumn.setName(tempName);newEasColumn.setDimensionType(dimensionType);cn.com.dataocean.metadata.entity.Column column=new cn.com.dataocean.metadata.entity.Column();column.setName(tempName);newEasColumn.setColumn(column);return newEasColumn;}private Column initColumn(String tableName, String columnName ){Table table = new Table(tableName);return new Column(table, columnName);}/*** 分组 group by 处理* @param selectBody* @param tableItems*/private void parseGroupBy(PlainSelect selectBody, List<Map<String, String>> tableItems) {if (Objects.nonNull(selectBody.getGroupBy())) {List<Expression> expressions=new ArrayList<>();ExpressionList groupByList=selectBody.getGroupBy().getGroupByExpressionList();groupByList.getExpressions().forEach(expression -> groupByList.accept(new ExpressionVisitorAdapter(){@Overridepublic void visit(ExpressionList express) {groupBy(expression,tableItems,expressions,express);}}));}}/*** 区别 是否有自定义时间段 函数，否则为普通group by*/private void groupBy(Expression expression, List<Map<String, String>> tableItems, List<Expression> expressions, ExpressionList express){//这里要判断下自定义时间函数if((expression instanceof Column)){Column group = (Column) expression;Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, group));//重新赋值给 函数表达式expressions.add((Column) map.get("newColumn"));express.setExpressions(expressions);}else{net.sf.jsqlparser.expression.Function function= (net.sf.jsqlparser.expression.Function) expression;Expression value=function.getParameters().getExpressions().get(1);function.getParameters().getExpressions().forEach(expression1 -> {if(!(expression1 instanceof Column)){return ;}Column functionExpression = (Column) expression1;expression.accept(new ExpressionVisitorAdapter(){@Overridepublic void visit(ExpressionList expression) {Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, functionExpression));//重新赋值给 函数表达式expressions.add((Column) map.get("newColumn"));expression.setExpressions(expressions);//自定义函数 重新赋值List<String> customerFunctionNames=function.getMultipartName();//这里要判断下自定义时间函数for(String customerFunctionName:customerFunctionNames) {if (Objects.nonNull(customerFunctionName) && customerFunctionName.equals(customConfig.getDoTimeFunction()) && Objects.nonNull(value)) {function.setName("from_unixtime");ExpressionList expressionList=customTimeFunction(value, expression);function.setParameters(expressionList);}}}});});}}/*** 获取重新拼接的 table 和 column* @param tableItems* @param functionExpression* @return*/private Map<String,Object> getNewColumn(List<Map<String, String>> tableItems, Column functionExpression) {Map<String, String> tableMap = tableItems.stream().filter(t -> StringUtils.equals(functionExpression.getTable().getName(), t.get(TABLE_ALIAS))).findFirst().orElse(new HashMap<>());EasTable easTable;//大于0 表示 匹配到别名 t1， 否则就用默认的 文件夹.表名.列名if(tableMap.size()>0){easTable = getEasTable(tableMap.get(CLASSIFY_NAME), tableMap.get(TABLE_NAME));}else{easTable = getEasTable(functionExpression.getTable().getSchemaName(), functionExpression.getTable().getName());}if (Objects.isNull(easTable)) return null;EasColumn easColumn = easTable.getColumn().stream().filter(c -> StringUtils.equals(functionExpression.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(easColumn)) return null;Column newColumn=initColumn(easTable.getTable().getName(), easColumn.getColumn().getName());Map<String,Object> map=new HashMap();map.put("easTable",easTable);map.put("easColumn",easColumn);map.put("newColumn",newColumn);return map;}private void parseHaving(PlainSelect selectBody, List<Map<String, String>> tableItems) {if (Objects.nonNull(selectBody.getHaving())) {selectBody.getHaving().accept(new ExpressionVisitorAdapter(){@Overridepublic void visit(ExpressionList expression) {List<Expression> expressions=new ArrayList<>();List<Expression> columnList=expression.getExpressions();columnList.forEach(tempColumn->{Column having = (Column) tempColumn;Map<String,Object> map= getNewColumn(tableItems,having);//重新赋值给 函数表达式EasColumn easColumn=(EasColumn)map.get("easColumn");EasTable easTable=(EasTable)map.get("easTable");Column newColumn=initColumn(easTable.getTable().getName(), easColumn.getColumn().getName());//重新赋值给 函数表达式expressions.add(newColumn);expression.setExpressions(expressions);});}});}}private Page parseLimit(PlainSelect selectBody, Page page) {Page resultPage = new Page();long limit = page.getPageSize();long offset = (page.getCurrentPage()-1)*limit < 0 ? 0 : (page.getCurrentPage()-1)*limit;if (Objects.isNull(selectBody.getLimit())) {resultPage.setSelectAll(true);}else{long total = selectBody.getLimit().getRowCount(LongValue.class).getValue(); //sql语句中的limit参数resultPage.setSelectAll(false);resultPage.setTotalCount(total);resultPage.setPageCount(getPageCount(total,limit));if(total < offset){limit = 0;}else if(total-offset < limit){limit = total-offset;}}selectBody.setLimit(new Limit().withRowCount(new LongValue(limit)));selectBody.setOffset(new Offset().withOffset(offset));resultPage.setCurrentPage(page.getCurrentPage());resultPage.setLimit(limit);resultPage.setOffset(offset);return resultPage;}private void parseWhere(PlainSelect selectBody,List<Map<String, String>> tableMap) {if (Objects.nonNull(selectBody.getWhere())) {selectBody.getWhere().accept(new ExpressionVisitorAdapter() {@Overridepublic void visit(Column column) {EasTable easTable;//大于0 表示 匹配到别名 t1， 否则就用默认的 文件夹.表名.列名if(!tableMap.isEmpty()){easTable = getEasTable(tableMap.get(0).get(CLASSIFY_NAME), tableMap.get(0).get(TABLE_NAME));}else{easTable = getEasTable(column.getTable().getSchemaName(), column.getTable().getName());}if (Objects.isNull(easTable)) return;EasColumn easColumn = easTable.getColumn().stream().filter(c -> StringUtils.equals(column.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(easColumn)) return;column.setColumnName(easColumn.getColumn().getName());Table table = new Table(easTable.getTable().getName());column.setTable(table);}});}}private Map<String, String> parseFromItem(PlainSelect selectBody) {Map<String, String> fromItemMap = new HashMap<>();selectBody.getFromItem().accept(new FromItemVisitorAdapter() {@Overridepublic void visit(Table table) {fromItemMap.put(CLASSIFY_NAME, table.getSchemaName());fromItemMap.put(TABLE_NAME, table.getName());fromItemMap.put(TABLE_ALIAS, Objects.nonNull(table.getAlias()) ? table.getAlias().getName() : null);EasTable easTable = getEasTable(table.getSchemaName(), table.getName());if (Objects.isNull(easTable)) return;table.setName(easTable.getTable().getName());table.setAlias(null);table.setSchemaName(null);}});return fromItemMap;}/*** 解析连表查询的表** @param selectBody* @return*/private List<Map<String, String>> parseJoinItem(PlainSelect selectBody, List<Map<String, String>> tableItems) {if (Objects.nonNull(selectBody.getJoins())) {selectBody.getJoins().forEach(j -> {Table rightItem = j.getRightItem(Table.class);Map<String, String> joinItemMap = new HashMap<>();if (rightItem instanceof Table) {rightItem.accept(new FromItemVisitorAdapter() {@Overridepublic void visit(Table table) {joinItemMap.put(CLASSIFY_NAME, table.getSchemaName());joinItemMap.put(TABLE_NAME, table.getName());joinItemMap.put(TABLE_ALIAS, Objects.nonNull(table.getAlias()) ? table.getAlias().getName() : null);}});tableItems.add(joinItemMap);}EasTable easTable = getEasTable(rightItem.getSchemaName(), rightItem.getName());j.setRightItem(new Table(easTable.getTable().getName()));Expression onColumn= j.getOnExpression();if(onColumn!=null){//拼接join 后 的on 条件j.setOnExpression(getNewOnExpression(onColumn,tableItems));}});}return tableItems;}private BinaryExpression getNewOnExpression(Expression onColumn,List<Map<String, String>> tableItems) {Column left=((EqualsTo) onColumn).getLeftExpression(Column.class);Column right=((EqualsTo) onColumn).getRightExpression(Column.class);BinaryExpression onExpression = new EqualsTo();Map<String, String> leftTableMap = tableItems.stream().filter(t -> StringUtils.equals(left.getTable().getName(), t.get(TABLE_ALIAS))).findFirst().orElse(new HashMap<>());Map<String, String> rightTableMap = tableItems.stream().filter(t -> StringUtils.equals(right.getTable().getName(), t.get(TABLE_ALIAS))).findFirst().orElse(new HashMap<>());EasTable leftEasTable ;EasTable rightEasTable;//大于0 表示 匹配到别名 t1， 否则就用默认的 文件夹.表名.列名if(leftTableMap.size()>0 && rightTableMap.size()>0){//  on  左边  =  右边leftEasTable = getEasTable(leftTableMap.get(CLASSIFY_NAME), leftTableMap.get(TABLE_NAME));rightEasTable = getEasTable(rightTableMap.get(CLASSIFY_NAME), rightTableMap.get(TABLE_NAME));}else{rightEasTable = getEasTable(right.getTable().getSchemaName(), right.getTable().getName());leftEasTable = getEasTable(left.getTable().getSchemaName(), left.getTable().getName());}if (Objects.isNull(leftEasTable)) return null;EasColumn leftColumn = leftEasTable.getColumn().stream().filter(c -> StringUtils.equals(left.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(leftColumn)) return null;if (Objects.isNull(rightEasTable)) return null;EasColumn rightColumn = rightEasTable.getColumn().stream().filter(c -> StringUtils.equals(right.getColumnName(), c.getName())).findFirst().orElse(null);if (Objects.isNull(rightColumn)) return null;onExpression.setLeftExpression(new Column(new Table(leftEasTable.getTable().getName()),leftColumn.getColumn().getName()));onExpression.setRightExpression(new Column(new Table(rightEasTable.getTable().getName()),rightColumn.getColumn().getName()));return onExpression;}/*** 排序* @param selectBody* @param tableItems*/private void parseOrderBy(PlainSelect selectBody, List<Map<String, String>> tableItems) {if (Objects.nonNull(selectBody.getOrderByElements())) {selectBody.getOrderByElements().forEach(item->{List<Expression> expressions=new ArrayList<>();if(item.getExpression() instanceof Column) {orderByItem(item,tableItems);}else{ExpressionList functionExpressions = ((net.sf.jsqlparser.expression.Function) item.getExpression()).getParameters();List<Expression> expressionList = functionExpressions.getExpressions();expressionList.forEach(expression ->orderByFunction(expression,item,tableItems,expressions));}});}}/*** 根据原字段排序* @param item* @param tableItems*/private void orderByItem(OrderByElement item, List<Map<String, String>> tableItems) {Column expression = item.getExpression(Column.class);Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, expression));//重新赋值给 函数表达式EasColumn easColumn=(EasColumn)map.get("easColumn");EasTable easTable=(EasTable)map.get("easTable");item.setExpression(initColumn(easTable.getTable().getName(), easColumn.getColumn().getName()));}/*** 根据 聚合字段排序* @param expression* @param item* @param tableItems* @param expressions*/private void orderByFunction(Expression expression, OrderByElement item, List<Map<String, String>> tableItems, List<Expression> expressions) {Column functionExpression = (Column) expression;item.getExpression().accept(new ExpressionVisitorAdapter() {@Overridepublic void visit(ExpressionList expression) {Map<String,Object> map= Collections.unmodifiableMap(getNewColumn(tableItems, functionExpression));//重新赋值给 函数表达式EasColumn easColumn=(EasColumn)map.get("easColumn");EasTable easTable=(EasTable)map.get("easTable");Column newColumn=initColumn(easTable.getTable().getName(), easColumn.getColumn().getName());//重新赋值给 函数表达式expressions.add(newColumn);expression.setExpressions(expressions);}});}public EasTable getEasTable(String classifyName, String tableName) {return dataManager.load(EasTable.class).query("select e from eas$EasTable e " +"where e.name = :name " +"and e.classify.classifyName = :classify").parameter("name", tableName).parameter("classify", classifyName).view("easTable-view-sqlAssociate").optional().orElse(null);}/*** 解析select sql语句** @param stmt* @return*/public Map<String, Object> parseSelectSql(Select stmt, Page page) {Map<String, Object> map = new HashMap<>();PlainSelect selectBody = (PlainSelect) stmt.getSelectBody();List<Map<String, String>> tableItems = new ArrayList<>();tableItems.add(parseFromItem(selectBody));tableItems.addAll(parseJoinItem(selectBody,tableItems));List<EasTable> resultTables = new ArrayList<>();tableItems.forEach(t -> {EasTable easTable = getEasTable(t.get(CLASSIFY_NAME), t.get(TABLE_NAME));resultTables.add(easTable);resultTables.stream().filter(distinctByKey(BaseUuidEntity::getId));});List<EasColumn> resultColumn = parseSelectItem(selectBody, tableItems);parseWhere(selectBody,tableItems);parseGroupBy(selectBody,tableItems);parseHaving(selectBody,tableItems);parseOrderBy(selectBody,tableItems);Page resultPage = parseLimit(selectBody,page);map.put("sql", stmt.toString());map.put("column", simplifyEasColumn(resultColumn));map.put("tables", translateToTableVo(resultTables));map.put("id", UUID.randomUUID());map.put("page",resultPage);return map;}private List<EasColumn> simplifyEasColumn(List<EasColumn> columns){columns.forEach(c -> c.setTable(null));return columns;}private List<SqlStatementEasTableVo> translateToTableVo(List<EasTable> resultTables) {List<SqlStatementEasTableVo> resultTableVos = new ArrayList<>();resultTables.forEach(t -> {if(Objects.nonNull(t)){SqlStatementEasTableVo sqlStatementEasTableVo = new SqlStatementEasTableVo();sqlStatementEasTableVo.setId(t.getId());sqlStatementEasTableVo.setName(t.getName());sqlStatementEasTableVo.setDescription(t.getDescription());sqlStatementEasTableVo.setVersion(t.getVersion());sqlStatementEasTableVo.setClassifyId(Objects.isNull(t.getClassify()) ? null : t.getClassify().getId());sqlStatementEasTableVo.setClassifyName(Objects.isNull(t.getClassify()) ? null : t.getClassify().getClassifyName());sqlStatementEasTableVo.setDatabaseId(Objects.isNull(t.getDatabase()) ? null : t.getDatabase().getId());resultTableVos.add(sqlStatementEasTableVo);}});return resultTableVos;}/*** 使用jsqlparser把SQL解析成对象** @param sql* @return* @throws JSQLParserException*/public Statements parseStatements(String sql) throws JSQLParserException {return CCJSqlParserUtil.parseStatements(sql);}private RunQueryResponse invokeDataService(String database, String schema, String paramSql) {return QueryRunner.newInstance().url(dataServiceConfig.getDataServiceUrl()).database(database).schema(schema).statement(paramSql).run();}/*** 保存SQL执行历史** @param sql* @param userExt* @param startTime* @return*/public EasSqlStatementOperateHistory saveOperateHistory(String sql, UserExt userExt, long startTime, String operateResult) {long endTime = timeSource.currentTimeMillis();String elapsedTime = endTime - startTime + "";EasSqlStatementOperateHistory easSqlStatementOperateHistory = metadata.create(EasSqlStatementOperateHistory.class);easSqlStatementOperateHistory.setOperator(userExt);easSqlStatementOperateHistory.setSqlStatement(sql);easSqlStatementOperateHistory.setElapsedTime(elapsedTime);easSqlStatementOperateHistory.setOperateResult(operateResult);easSqlStatementOperateHistory.setOperateTime(timeSource.currentTimestamp());dataManager.commit(easSqlStatementOperateHistory);return easSqlStatementOperateHistory;}/*** 获取SQL查询联想内容** @return*/@Overridepublic String getAssociateContent() {long startTime = timeSource.currentTimeMillis();List<EasTable> easTables = dataManager.load(EasTable.class).query("select e from eas$EasTable e where e.tableType = :type1 or e.tableType = :type2 and e.classify.id is not null ").view("easTable-view-sqlAssociate")
//                .parameter("type1",TableType.DATAFRAME.getId())
//                .parameter("type2",TableType.ESDATAFRAME.getId()).parameter("type1","工作表").parameter("type2","ES工作表").list();long time2 = timeSource.currentTimeMillis();log.info("查询easTable耗时：{} ms",time2-startTime);JSONObject resultJson = new JSONObject();easTables.forEach(easTable -> {List<EasColumn> easColumns = dataManager.load(EasColumn.class).query("select e from eas$EasColumn e where e.table.id = :table").parameter("table",easTable.getId()).list();String classifyName = easTable.getClassify().getClassifyName();String name = easTable.getName();Object[] objects = easColumns.stream().map(EasColumn::getName).toArray();resultJson.put(classifyName + "." + name, objects);});long time3 = timeSource.currentTimeMillis();log.info("查询column及拼接耗时：{} ms", time3-time2);return resultJson.toJSONString();}public long getPageCount(Long total, Long pageSize){return (total+pageSize-1)/pageSize;}private static <T> Predicate<T> distinctByKey(Function<? super T, ?> keyExtractor) {Map<Object, Boolean> seen = new ConcurrentHashMap<>();return t -> seen.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;}/*** 当前数据服务对SQL格式有着很严格的要求，SQL语句最后必须为空格，且不能带分号* 这里做一些特殊转换* @param sql* @return*/private String formatSql(String sql){sql = sql.trim();if(sql.endsWith(";")){sql = sql.substring(0,sql.length()-1);}return sql.trim()+" ";}}