公众号的微网站怎么做,镇江手机网站建设,wordpress xcache,4昌平区网站建设sql解析#xff0c;转换列表和表名
1、 jsqlparse介绍 JSqlParse是一款很精简的sql解析工具#xff0c;它可以将常用的sql文本解析成具有层级结构的“语法树”#xff0c;我们可以针对解析后的“树节点#xff08;也即官网里说的有层次结构的java类#xff09;”进行处理…sql解析转换列表和表名
1、 jsqlparse介绍 JSqlParse是一款很精简的sql解析工具它可以将常用的sql文本解析成具有层级结构的“语法树”我们可以针对解析后的“树节点也即官网里说的有层次结构的java类”进行处理进而生成符合我们要求的sql形式。 官网给的介绍很简洁JSqlParser 解析 SQL 语句并将其转换为 Java 类的层次结构。生成的层次结构可以使用访问者模式进行访问官网地址JSqlParser - Home。
2 pom坐标 dependencygroupIdcom.github.jsqlparser/groupIdartifactIdjsqlparser/artifactIdversion4.3/version
/dependency3 解析工具类
private static String convertSql(String sql, Class? clazz) throws Exception {Select select (Select) CCJSqlParserUtil.parse(sql);SelectBody selectBody select.getSelectBody();if (selectBody instanceof PlainSelect) {PlainSelect plainSelect (PlainSelect) selectBody;Table table (Table) plainSelect.getFromItem();String originTableName table.getName().toLowerCase();Matcher matcher pattern.matcher(originTableName);//如果不符合原来的的表名格式就返回原sqlif (!matcher.find()) {return sql;}String day matcher.group().replace(_, -);long start 0;long end 10;//转换where条件Expression where plainSelect.getWhere();where.accept(new MyWhereExpressionVisitorAdapter());// 不管原来有没有时间条件,都拼接上当天的时间条件String condExpr where and time start and time end;Expression newCondition CCJSqlParserUtil.parseCondExpression(condExpr);plainSelect.setWhere(newCondition);//设置表名TableName annotation clazz.getAnnotation(TableName.class);table.setName(annotation.value());//转换select的列for (SelectItem selectItem : plainSelect.getSelectItems()) {SelectExpressionItem item (SelectExpressionItem) selectItem;item.accept(new MySelectExpressionVisitorAdapter(item, KEY_MAP.get(clazz)));}}//加时间排序return select order by time asc;}3 列名转换
public class MySelectExpressionVisitorAdapter extends ExpressionVisitorAdapter {private SelectExpressionItem item;private MapString, String columnMap;public MySelectExpressionVisitorAdapter(SelectExpressionItem item, MapString, String columnMap) {this.item item;this.columnMap columnMap;}Overridepublic void visit(Column column) {Expression expression item.getExpression();String originColumnName column.getColumnName();String changeColumnName columnMap.get(originColumnName.toUpperCase());if (changeColumnName ! null) {column.setColumnName(changeColumnName);//把原列名当做别名if (!originColumnName.equals(changeColumnName) expression instanceof Column item.getAlias() null) {item.setAlias(new Alias(originColumnName));}}}Overridepublic void visit(Function function) {String name function.getName();if (round.equalsIgnoreCase(name)) {function.getParameters().getExpressions().remove(1);function.setName();}super.visit(function);}4 条件转换
public class MyWhereExpressionVisitorAdapter extends ExpressionVisitorAdapter {Overrideprotected void visitBinaryExpression(BinaryExpression expression) {if (!(expression instanceof ComparisonOperator)) {super.visitBinaryExpression(expression);return;}Column column (Column) expression.getLeftExpression();String columnName column.getColumnName();if (isAgain.equalsIgnoreCase(columnName)) {column.setColumnName(isAgain);Expression rightExpression expression.getRightExpression();if (rightExpression instanceof LongValue) {LongValue tmp (LongValue) rightExpression;tmp.setStringValue(String.valueOf(tmp.getValue()0));}} else if (time.equalsIgnoreCase(columnName)) {column.setColumnName(time);changeTime((ComparisonOperator) expression);} else if (changeWhereSerialColumName(columnName)) {column.setColumnName(serialNum);}}/*** 转换时间条件列* param comparisonOperator*/private void changeTime(ComparisonOperator comparisonOperator) {Expression rightExpression comparisonOperator.getRightExpression();if (rightExpression instanceof StringValue) {long value DateTimeUtilJdk8.getTimeMilli(((StringValue) rightExpression).getValue());LongValue longValue new LongValue();longValue.setValue(value);comparisonOperator.setRightExpression(longValue);}}private boolean changeWhereSerialColumName(String column) {return LindormUtil.getOriginalSerialNumName().contains(column.toUpperCase());}测试
public static void main(String[] args) throws Exception {String sql select ID, username,password from Tbl_db_2023_06_26 where id007;System.out.println(convertSql(sql, Object.class));}
