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
@Inject
private metadata metadata;
@Inject
private DataManager dataManager;
@Inject
private TimeSource timeSource;
@Inject
private Logger log;
@Inject
private DataServiceConfig dataServiceConfig;
@Inject
private 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";
@Override
public Object execute(String sql, UserExt userExt, Page page){
if(Objects.isNull(page)) page = new Page();
long startTime = timeSource.currentTimeMillis();
String operateResult = "执行成功";
Map resultObj = new HashMap<>();
List> 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;
}
private void executeSelect(Statement stmt, Page finalPage, List> jsonArray) {
Map tempMap = parseSelectSql((Select) stmt, finalPage);
String paramSql = formatSql((String) tempMap.get("sql"));
List tables = (List) tempMap.get("tables");
Map 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 objHashMap = new HashMap<>();
tempMap.put("page", resultPage);
objHashMap.put("metas", tempMap);
jsonArray.add(objHashMap);
}
private Map getDataServiceConfig(SqlStatementEasTableVo entity) {
EasTable easTable = getEasTable(entity.getClassifyName(), entity.getName());
Map 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 parseSelectItem(PlainSelect selectBody, List> tableItems) {
List resultColumn = new ArrayList<>();
selectBody.getSelectItems().forEach(s -> s.accept(new SelectItemVisitorAdapter() {
@Override
public void visit(AllColumns columns) {
Map 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 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);
}
@Override
public void visit(AllTableColumns columns) {
String name = columns.getTable().getName();
Map 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 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);
}
@Override
public 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;
}
private void caseexpression(SelectexpressionItem item,
List> tableItems,
List 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);
}
private EasColumn noHaveSwitchexpression(SelectexpressionItem item, List> tableItems) {
List whenClauseList=new ArrayList<>();
EasColumn easColumn = null;
for (WhenClause i : ((Caseexpression) item.getexpression()).getWhenClauses()) {
Column left = ((EqualsTo) i.getWhenexpression()).getLeftexpression(Column.class);
Map 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;
}
private EasColumn haveSwitchexpression(SelectexpressionItem item, List> tableItems, Column switchexpression) {
Map 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(){
@Override
public void visit(Caseexpression caseexpression) {
caseexpression.setSwitchexpression(initColumn(finalEasTable.getTable().getName(), easColumn.getColumn().getName()));
}
});
return easColumn;
}
private void functionexpression(SelectexpressionItem item,
List> tableItems,
List resultColumn) {
List expressions=new ArrayList<>();
expressionList functionexpressions= ((net.sf.jsqlparser.expression.Function)item.getexpression()).getParameters();
if(Objects.nonNull(functionexpressions)){
//这里表示的 是 count + 具体字段
List 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);
}
}
private void functionItem(expression expression, List> tableItems, List expressions, SelectexpressionItem item, List resultColumn,expression value) {
if(expression instanceof Column){
Column functionexpression = (Column) expression;
item.getexpression().accept(new expressionVisitorAdapter(){
@Override
public void visit(expressionList expression) {
Map 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 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_unixtime
net.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) / 60
Division 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 ) * 60
Multiplication multiplication2=new Multiplication();
multiplication2.setLeftexpression(newFunction2);
multiplication2.setRightexpression(value);
list.addexpressions(multiplication2);
return list;
}
private void basicexpression( SelectexpressionItem item,
List> tableItems,
List resultColumn){
Column expression = item.getexpression(Column.class);
Map 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()));
}
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);
}
private void parseGroupBy(PlainSelect selectBody, List> tableItems) {
if (Objects.nonNull(selectBody.getGroupBy())) {
List expressions=new ArrayList<>();
expressionList groupByList=selectBody.getGroupBy().getGroupByexpressionList();
groupByList.getexpressions().forEach(expression -> groupByList.accept(new expressionVisitorAdapter(){
@Override
public void visit(expressionList express) {
groupBy(expression,tableItems,expressions,express);
}
}));
}
}
private void groupBy(expression expression, List> tableItems, List expressions, expressionList express){
//这里要判断下自定义时间函数
if((expression instanceof Column)){
Column group = (Column) expression;
Map 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(){
@Override
public void visit(expressionList expression) {
Map map= Collections.unmodifiableMap(getNewColumn(tableItems, functionexpression));
//重新赋值给 函数表达式
expressions.add((Column) map.get("newColumn"));
expression.setexpressions(expressions);
//自定义函数 重新赋值
List 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);
}
}
}
});
});
}
}
private Map getNewColumn(List> tableItems, Column functionexpression) {
Map 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 map=new HashMap();
map.put("easTable",easTable);
map.put("easColumn",easColumn);
map.put("newColumn",newColumn);
return map;
}
private void parseHaving(PlainSelect selectBody, List> tableItems) {
if (Objects.nonNull(selectBody.getHaving())) {
selectBody.getHaving().accept(new expressionVisitorAdapter(){
@Override
public void visit(expressionList expression) {
List expressions=new ArrayList<>();
List columnList=expression.getexpressions();
columnList.forEach(tempColumn->{
Column having = (Column) tempColumn;
Map 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> tableMap) {
if (Objects.nonNull(selectBody.getWhere())) {
selectBody.getWhere().accept(new expressionVisitorAdapter() {
@Override
public 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 parseFromItem(PlainSelect selectBody) {
Map fromItemMap = new HashMap<>();
selectBody.getFromItem().accept(new FromItemVisitorAdapter() {
@Override
public 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;
}
private List> parseJoinItem(PlainSelect selectBody, List> tableItems) {
if (Objects.nonNull(selectBody.getJoins())) {
selectBody.getJoins().forEach(j -> {
Table rightItem = j.getRightItem(Table.class);
Map joinItemMap = new HashMap<>();
if (rightItem instanceof Table) {
rightItem.accept(new FromItemVisitorAdapter() {
@Override
public 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> tableItems) {
Column left=((EqualsTo) onColumn).getLeftexpression(Column.class);
Column right=((EqualsTo) onColumn).getRightexpression(Column.class);
Binaryexpression onexpression = new EqualsTo();
Map leftTableMap = tableItems.stream()
.filter(t -> StringUtils.equals(left.getTable().getName(), t.get(TABLE_ALIAS)))
.findFirst()
.orElse(new HashMap<>());
Map 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;
}
private void parseOrderBy(PlainSelect selectBody, List> tableItems) {
if (Objects.nonNull(selectBody.getOrderByElements())) {
selectBody.getOrderByElements().forEach(item->{
List expressions=new ArrayList<>();
if(item.getexpression() instanceof Column) {
orderByItem(item,tableItems);
}else{
expressionList functionexpressions = ((net.sf.jsqlparser.expression.Function) item.getexpression()).getParameters();
List expressionList = functionexpressions.getexpressions();
expressionList.forEach(expression ->
orderByFunction(expression,item,tableItems,expressions)
);
}
});
}
}
private void orderByItem(OrderByElement item, List> tableItems) {
Column expression = item.getexpression(Column.class);
Map 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()));
}
private void orderByFunction(expression expression, OrderByElement item, List> tableItems, List expressions) {
Column functionexpression = (Column) expression;
item.getexpression().accept(new expressionVisitorAdapter() {
@Override
public void visit(expressionList expression) {
Map 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);
}
public Map parseSelectSql(Select stmt, Page page) {
Map map = new HashMap<>();
PlainSelect selectBody = (PlainSelect) stmt.getSelectBody();
List> tableItems = new ArrayList<>();
tableItems.add(parseFromItem(selectBody));
tableItems.addAll(parseJoinItem(selectBody,tableItems));
List 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 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 simplifyEasColumn(List columns){
columns.forEach(c -> c.setTable(null));
return columns;
}
private List translateToTableVo(List resultTables) {
List 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;
}
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();
}
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;
}
@Override
public String getAssociateContent() {
long startTime = timeSource.currentTimeMillis();
List 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 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 Predicate distinctByKey(Function keyExtractor) {
Map seen = new ConcurrentHashMap<>();
return t -> seen.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
}
private String formatSql(String sql){
sql = sql.trim();
if(sql.endsWith(";")){
sql = sql.substring(0,sql.length()-1);
}
return sql.trim()+" ";
}
}
