java使⽤jsqlparser实现⾃定义转换
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_lumn2 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<String, Object> resultObj =new HashMap<>();
List<HashMap<String, Object>> jsonArray =new ArrayList<>();
try{
Statements stmts =parseStatements(sql);
Page finalPage = page;
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) ("sql"));
List<SqlStatementEasTableVo> tables =(List<SqlStatementEasTableVo>) ("tables");
Map<String, String> config =(0));
String database = ("database");
String schemaName = ("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) ("page");
RunQueryResponse response =invokeDataService(database, schemaName, paramSql);
if(resultPage.isSelectAll()){
resultPage.Total().longValue());
resultPage.setPageCount(Total().longValue(), PageSize()));
}
resultPage.Result());
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 =ClassifyName(), Name());
Map<String,String> resultMap =new HashMap<>();
Null(easTable)){
String database = Objects.Database())?"": Database().getAlias();
String schema = Schema().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<>();
@Override
public void visit(AllColumns columns){
Map<String, String> tableMap = (0))? (0):new HashMap<>();                EasTable easTable =(CLASSIFY_NAME), (TABLE_NAME));
if(Objects.isNull(easTable))return;
List<SelectItem> list =new ArrayList<>();
Column column =Table().getName(), c.getColumn().getName());
SelectExpressionItem selectExpressionItem =new SelectExpressionItem(column);
list.add(selectExpressionItem);
resultColumn.add(c);
});
selectBody.setSelectItems(list);
selectBody.setSelectItems(list);
}
@Override
public void visit(AllTableColumns columns){
String name = Table().getName();
Map<String, String> tableMap = tableItems.stream()
.filter(t -> StringUtils.equals(name, t.get(TABLE_ALIAS)))
.findFirst()
.orElse(new HashMap<>());
EasTable easTable =(CLASSIFY_NAME), (TABLE_NAME));
if(Objects.isNull(easTable))return;
java switch case stringList<SelectItem> list =new ArrayList<>();
Column column =Table().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，否则默认为字段
Expression().getClass().getName()){
case"net.pression.CaseExpression":
caseExpression(item,tableItems,resultColumn);
break;
case"net.pression.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) Expression()).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(Column().getName(),DimensionType.BUSINESS);        resultColumn.add(newEasColumn);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。