使用Durid解析
- 首先将传递来的sql抽象成AST语法树,之后将表名和字段名存储到list集合中。
- 根据字段名和表名去数据库系统表中查询出对应字段的注释和字段类型。
MySqlStatementParser mySqlStatementParser = new MySqlStatementParser(sqlStr);
SQLStatement sqlStatement = mySqlStatementParser.parseStatement();
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
sqlStatement.accept(visitor);
Collection<TableStat.Column> columns = visitor.getColumns();
List<FieldVo> list = new ArrayList<>();
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:字段类型}
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;
FindAllType.forEach(t->{
t.setJavaType(FieldTypeEnum.getJavaByField(t.getFieldType()));
});
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;
}
}