sql解析，转换列表和表名

1、 jsqlparse介绍

JSqlParse是一款很精简的sql解析工具，它可以将常用的sql文本解析成具有层级结构的“语法树”，我们可以针对解析后的“树节点（也即官网里说的有层次结构的java类）”进行处理进而生成符合我们要求的sql形式。

官网给的介绍很简洁：JSqlParser 解析 SQL 语句并将其转换为 Java 类的层次结构。生成的层次结构可以使用访问者模式进行访问（官网地址：JSqlParser - Home）。

2 pom坐标

  <dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.3</version>
</dependency>

3 解析工具类

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 Map<String, String> columnMap;public MySelectExpressionVisitorAdapter(SelectExpressionItem item, Map<String, 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 id='007'";System.out.println(convertSql(sql, Object.class));}