JSQLParser解析SQL神器

JsqlParserUtils
sql解析通用工具

/** * SQL解析通用工具 **/@Slf4jpublicclassJsqlParserUtils{publicstaticStringassembleDeriveQuerySql(Stringsql,Expressionexpression){if(expression==null){returnsql;}Statementparse=null;try{parse=CCJSqlParserUtil.parse(sql);if(!(parseinstanceofSelect)){returnsql;}Selectselect=(Select)parse;if(selectinstanceofPlainSelect){PlainSelectplainSelect=select.getPlainSelect();Expressionwhere=plainSelect.getWhere();if(where!=null){plainSelect.setWhere(newAndExpression(where,expression));}else{plainSelect.setWhere(expression);}}returnparse.toString();}catch(JSQLParserExceptione){log.error("解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}}/** * 获取sql语句的最外层select对象 * * @param sql 解析的sql语句 */publicstaticList<SelectItemDto>getOutermostSelectItems(Stringsql,Statementparse){if(StrUtil.isBlank(sql)&&parse==null){thrownewBusinessException(ErrorEnum.DAL_SQL_IS_NULL);}try{if(parse==null){parse=CCJSqlParserUtil.parse(sql);}if(!(parseinstanceofSelect)){thrownewBusinessException(ErrorEnum.DAL_IS_NOT_SELECT_SQL);}SelectselectBody=((Select)parse);if(selectBodyinstanceofPlainSelect){PlainSelectplainSelect=selectBody.getPlainSelect();List<SelectItem<?>>selectItems=plainSelect.getSelectItems();if(CollUtil.isEmpty(selectItems)){returnnewArrayList<>();}FromItemfromItem=plainSelect.getFromItem();StringoriginalTableName=null;StringtableAlias=null;StringtableName=null;if(fromItem!=null){originalTableName=fromItem.toString();Aliasalias=fromItem.getAlias();if(alias!=null){tableAlias=alias.getName();}tableName=StrUtil.isBlank(tableAlias)?originalTableName:tableAlias;}List<SelectItemDto>selectItemDtos=newArrayList<>();List<MdpSqlFieldCommentData>feildCommentDataList=newArrayList<>();for(SelectItem<?>selectItem:selectItems){SqlCommentUtil.extractColumnComments(sql,selectItem,feildCommentDataList);SelectItemDtoselectItemDto=newSelectItemDto();selectItemDto.setOriginalTableName(originalTableName);selectItemDto.setTableAlias(tableAlias);selectItemDto.setTableName(tableName);Aliasalias=selectItem.getAlias();selectItemDto.setColumnName(selectItem.getExpression().toString());if(alias!=null){selectItemDto.setAliasName(alias.getName());}selectItemDto.setSelectItemName(StrUtil.isBlank(selectItemDto.getAliasName())?selectItemDto.getColumnName():selectItemDto.getAliasName());selectItemDtos.add(selectItemDto);}if(CollUtil.isNotEmpty(feildCommentDataList)){MdpSqlFieldCommentDataServicemdpSqlFieldCommentDataService=SpringUtil.getBean(MdpSqlFieldCommentDataService.class);mdpSqlFieldCommentDataService.saveOrUpdateEntityBatch(feildCommentDataList);}returnselectItemDtos;}}catch(Exceptione){thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_SELECT_ITEM_ERROR);}returnnewArrayList<>();}/** * 获取sql语句的最外层select字段 * * @param sql 解析的sql语句 */publicstaticList<String>getOutermostSelectItemNames(Stringsql,Statementparse){returngetOutermostSelectItems(sql,parse).stream().map(SelectItemDto::getSelectItemName).collect(Collectors.toList());}publicstaticbooleanhasGroupBy(Stringsql){try{Statementparse=CCJSqlParserUtil.parse(sql);if(parseinstanceofSelect){Selectselect=(Select)parse;PlainSelectplainSelect=select.getPlainSelect();GroupByElementgroupBy=plainSelect.getGroupBy();returngroupBy!=null;}else{returnfalse;}}catch(JSQLParserExceptione){log.error("addSelectItem 解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}}/** * 在sql中新增select 字段 * <p> * 如果select字段已经存在，则不重复添加 * * @param sql 需要处理的sql * @param selectItemColumns 需要新增的select字段集合 * @return 处理完成之后的sql */publicstaticStringaddSelectItem(Stringsql,List<String>selectItemColumns){if(CollectionUtils.isEmpty(selectItemColumns)){returnsql;}Statementparse=null;PlainSelectplainSelect=null;try{parse=CCJSqlParserUtil.parse(sql);}catch(JSQLParserExceptione){log.error("addSelectItem 解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}if(parseinstanceofSelect){Selectselect=(Select)parse;plainSelect=select.getPlainSelect();List<SelectItem<?>>selectItems=select.getPlainSelect().getSelectItems();List<String>originalSelectItemColumns=newArrayList<>();for(SelectItem<?>selectItem:selectItems){Aliasalias=selectItem.getAlias();if(alias!=null){originalSelectItemColumns.add(alias.getName());}else{originalSelectItemColumns.add(selectItem.getExpression().toString());}}//获取两个集合的单差集（selectItemColumns有，originalSelectItemColumns没有的元素）List<String>subtractList=CollUtil.subtractToList(selectItemColumns,originalSelectItemColumns);if(CollUtil.isNotEmpty(subtractList)){//新增select字段for(StringaddSelectItem:subtractList){SelectItem<Column>selectExpressionItem=newSelectItem<>();Columncolumn=newColumn();column.setColumnName(addSelectItem);selectExpressionItem.setExpression(column);selectItems.add(selectExpressionItem);}}}returnplainSelect==null?sql:plainSelect.toString();}/** * 在sql中删除select 字段 * <p> * 删除select 字段 * * @param sql 需要处理的sql * @param removeSelecretItemColumns 需要删除的select字段集合 * @return 处理完成之后的sql */publicstaticStringremoveSelectItem(Stringsql,List<String>removeSelecretItemColumns){if(CollectionUtils.isEmpty(removeSelecretItemColumns)){returnsql;}Statementparse=null;PlainSelectplainSelect=null;try{parse=CCJSqlParserUtil.parse(sql);}catch(JSQLParserExceptione){log.error("removeSelectItem 解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}if(parseinstanceofSelect){Selectselect=(Select)parse;plainSelect=select.getPlainSelect();List<SelectItem<?>>selectItems=plainSelect.getSelectItems();Iterator<SelectItem<?>>iterator=selectItems.iterator();while(iterator.hasNext()){SelectItem<?>selectItem=iterator.next();StringselectItemStr=selectItem.toString();Aliasalias=selectItem.getAlias();if(alias!=null){selectItemStr=alias.getName();}if(removeSelecretItemColumns.contains(selectItemStr)){iterator.remove();}}}returnplainSelect==null?sql:plainSelect.toString();}/** * 在sql中新增group by字段 * <p> * 如果group by字段已经存在，则不重复添加 * * @param sql 需要处理的sql * @param groupByColumns 需要新增的group by字段集合 * @return 处理完成之后的sql */publicstaticStringaddGroupBy(Stringsql,List<String>groupByColumns){if(CollectionUtils.isEmpty(groupByColumns)){returnsql;}Statementparse=null;PlainSelectplainSelect=null;try{parse=CCJSqlParserUtil.parse(sql);}catch(JSQLParserExceptione){log.error("addGroupBy 解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}if(parseinstanceofSelect){Selectselect=(Select)parse;plainSelect=select.getPlainSelect();GroupByElementgroupBy=plainSelect.getGroupBy();if(groupBy==null){//如果没有groupBy元素，则创建一个groupBy=newGroupByElement();plainSelect.setGroupByElement(groupBy);}ExpressionListgroupByExpressionList=groupBy.getGroupByExpressionList();List<Expression>expressions=groupByExpressionList.getExpressions();if(CollUtil.isNotEmpty(expressions)){for(Expressionexpression:expressions){//只考虑列的情况if(expressioninstanceofColumn){Columncolumn=(Column)expression;StringcolumnName=column.getColumnName();//如果新增的列名已经有了则不重复添加groupByColumns.remove(columnName);}}}groupByExpressionList.addExpressions(groupByColumns.stream().distinct().map(Column::new).toArray(Column[]::new));}returnplainSelect==null?sql:plainSelect.toString();}/** * 在sql中删除group by字段 * <p> * * @param sql 需要处理的sql * @param removeGroupreByColumns 需要删除的group by字段集合 * @return 处理完成之后的sql */publicstaticStringremoveGroupBy(Stringsql,List<String>removeGroupreByColumns){if(CollectionUtils.isEmpty(removeGroupreByColumns)){returnsql;}Statementparse=null;PlainSelectplainSelect=null;try{parse=CCJSqlParserUtil.parse(sql);}catch(JSQLParserExceptione){log.error("removeGroupBy 解析sql失败,msg={}",e.getMessage(),e);thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}if(parseinstanceofSelect){Selectselect=(Select)parse;plainSelect=select.getPlainSelect();GroupByElementgroupBy=plainSelect.getGroupBy();if(groupBy==null){returnsql;}ExpressionListgroupByExpressionList=groupBy.getGroupByExpressionList();List<Expression>expressions=groupByExpressionList.getExpressions();if(CollUtil.isNotEmpty(expressions)){Iterator<Expression>iterator=expressions.iterator();while(iterator.hasNext()){Expressionexpression=iterator.next();StringcolumnStr=expression.toString();if(removeGroupreByColumns.contains(columnStr)){iterator.remove();}}}}returnplainSelect==null?sql:plainSelect.toString();}/** * 判断输入的SQL语句是否为SELECT语句，如果不是则报错。 * * @param sql 输入的SQL语句 */publicstaticvoidcheckSelectStatement(Stringsql){if(StrUtil.isBlank(sql)){thrownewBusinessException(ErrorEnum.DAL_IS_NOT_SELECT_SQL);}try{if(!(CCJSqlParserUtil.parse(sql)instanceofSelect)){thrownewBusinessException(ErrorEnum.DAL_IS_NOT_SELECT_SQL);}}catch(JSQLParserExceptione){thrownewBusinessException(ErrorEnum.DAL_SQL_ANALYSIS_ERROR);}}}