Java中解析前端传的Sql语句

使用Durid解析

• 首先将传递来的sql抽象成AST语法树，之后将表名和字段名存储到list集合中。
• 根据字段名和表名去数据库系统表中查询出对应字段的注释和字段类型。

//mySqlstatementParser为sql语句
	MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sqlStr);
//使用parpser解析生成的AST,这里sqlStatement是AST,AST为抽象语法树
	SQLStatement sqlStatement = mySqlStatementParser.parseStatement();
	MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
	sqlStatement.accept(visitor);
	Collection<TableStat.Column> columns = visitor.getColumns();

//list是存储表名和字段名的集合
	List<FieldVo> list = new ArrayList<>();
       //通过循环将表名和字段名解析出，并存储到list集合中
       columns.stream().forEach(row->{
           if (row.isSelect()){
               FieldVo fieldVo = new FieldVo();
               fieldVo.setTableName(row.getTable());
               fieldVo.setFieldName(row.getName());
               list.add(fieldVo);
           }
       });
//查询字段类型和字段中文名，
     List<FieldVo> FindAllType = iXxglFieldDefineService.getTableAndField(list);

• 以pgsql为例到系统表中查询字段类型和字段中文名

<!--查询语句-->
<select id="getTableAndField" resultType="com.xxgl.hjjxxgl.entity.FieldVo" parameterType="java.util.List">
        select cl.relname as tableName ,
        attr.attname as fieldName,
        col_description ( attr.attrelid, attr.attnum ) AS comment,
        concat_ws ( '', ty.typname, SUBSTRING ( format_type ( attr.atttypid, attr.atttypmod ) FROM '\(.*\)' ) ) AS fieldType
        from pg_class cl,
        pg_attribute attr,
        pg_type ty
        where attr.attrelid = cl.oid
        and   attr.atttypid = ty.oid and
        <foreach collection="list" index="index" item="item" open="(" separator="or" close=")">
            ( cl.relname = #{item.tableName} and attr.attname = #{item.fieldName})
        </foreach>
    </select>

• 解析查询语句中的字段别名（由于获取字段的别名和前面两个不同，这里重新定义了一个list2）

//获取字段别名
     List<FieldVo> list2 = new ArrayList<>();
     List<SQLStatement> stmtList = SQLUtils.parseStatements(String.valueOf(sqlStatement), DbType.mysql);
     SQLStatement stmt = stmtList.get(0);
     List<SQLSelectItem> list1= ((MySqlSelectQueryBlock)((SQLSelect)((SQLSelectStatement)stmt).getSelect()).getQuery()).getSelectList();
        for(SQLSelectItem item : list1){
            FieldVo fieldZo = new FieldVo();
            fieldZo.setJavaName(item.getAlias());
            list2.add(fieldZo);
        }

• 两个list集合的内容进行合并，最终返回{tableName：表名， fieldName：字段名，comment：字段中文名；fieldType：字段类型}

//合并两个list的内容
      Integer len = FindAllType.size() <= list2.size()?FindAllType.size():list2.size();
      for (int i=0;i<len;i++){
          FieldVo a = FindAllType.get(i);
          FieldVo a1 = list2.get(i);
          a.setJavaName(a1.getJavaName());
      }
	return FindAllType;

• 补充
• 将数据中的字段类型转换为Java基本数据类型

//将对应的数据库类型转为Java类型
        FindAllType.forEach(t->{
            t.setJavaType(FieldTypeEnum.getJavaByField(t.getFieldType()));
        });

• 定义枚举值，将Java类型和字段类型进行匹配

package com.xxgl.hjjxxgl.enums;

public enum FieldTypeEnum {
    INT("int2","Integer"),
    INTS("int4","Long"),
    CAHRS("bpchar","String"),
    CAHR("char","String"),
    DATE("date","Date"),
    VARCHAR("varchar","String"),
    TEXT("text","String");

    private String fieldType;
    private String javaType;

    private FieldTypeEnum(String fieldType, String javaType){
        this.fieldType = fieldType;
        this.javaType = javaType;
    }

    public static String getJavaByField(String fieldType){
        for(FieldTypeEnum typeEnum : FieldTypeEnum.values()){
            if(fieldType != null && fieldType.startsWith(typeEnum.fieldType)){
                return typeEnum.javaType;
            }
        }
        return null;
    }
}