为了获取到对应的sql数据，分了三步骤
第一步，获取日志文件，解析日志文件中的查询sql，递归解析sql，获取表关系集合
递归解析sql，获取表与表之间的关系
输出得到的对应关联关系数据
第二步，根据获取到的表关系集合，提取表名和列名集合查出对应的中文备注，组装数据
建表的映射关系以及表的列字段信息，封装成json对象
第三步：创建我们的文件，将我们的原始数据和组装好的json对象数据存储在文本里
原始文件
组装后生成的json文件的内容，已经经过格式化了，包含两个部分内容，一是表之间的关系集合，二是各表集合，表包含我们里面用到的关联列
用的一个UI插件的效果
UI插件下载地址：https://download.youkuaiyun.com/download/u012269637/90366907
，只要生成对应的数据格式填充就可以了，这个UI效果不行，理不清复杂的表关系图，手动调也很多交叉的，后期看下有什么可以替代的
java代码
这个是解析sql语法的 https://download.youkuaiyun.com/download/u012269637/90366917
解析后生成对应的json文件，下面的仅供参考，可以根据自己的需求，封装自己的方法
public static void handleSql(List<String> tables,List<String> cols,List<String> relation, Map<String,String> nameSchema,Map<String,Integer> tableIDs){
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 建立数据库连接
connection = DriverManager.getConnection(JDBC_URL);
// 创建SQL语句
statement = connection.createStatement();
//String numer="STM06C65233,STS02E96210";
String numer="111210A40XX,110918A00XX";
List<String> deletesqls=new ArrayList<>();
List<String> deletesqls1=new ArrayList<>();
cols.remove("ModifiedBy");
//cols.remove("CreatedBy");
//cols.remove("FactoryId");
int i=0;
String sql = " select \n" +
" CAST( d.name AS VARCHAR(100)) as tableName ,\n" +
" CAST( f.value AS VARCHAR(100)) as tableRemark ,\n" +
" CAST( a.colorder AS VARCHAR(100)) as colorder ,\n" +
" CAST( a.name AS VARCHAR(100)) as colName,\n" +
" CAST( g.value AS VARCHAR(100)) as colRemark " +
"from syscolumns a\n" +
"left join systypes b on a.xusertype=b.xusertype\n" +
"inner join sysobjects d on a.id=d.id and d.xtype='U' \n" +
"left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id \n" +
"left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0\n" +
"where 1=1 and " + Util.setIn(tables, "d.name")+" and "+ Util.setIn(cols, "a.name");
// 执行SQL语句
resultSet = statement.executeQuery(sql);
// 处理结果集
List<TableCol> tableCols=new ArrayList<>();
while (resultSet.next()) {
String tableName = resultSet.getString("tableName");
String tableRemark = resultSet.getString("tableRemark");
String colName = resultSet.getString("colName");
String colRemark = resultSet.getString("colRemark");
tableCols.add(new TableCol(tableName,tableRemark,colName,colRemark));
}
/* Map<String, String> workNameMap = new HashMap<>();
workNameMap = tableCols.stream()
.collect(Collectors.groupingBy(
record->record.getTableName(),
Collectors.mapping(record -> record.getColName(), Collectors.joining("+"))
));*/
String filePath = "D://example.txt"; // 文件路径
String filePath1 = "E:\\jsplumb-dataLineage-master\\public\\js\\json.js"; // 文件路径
//String content = "这是要写入文件的文本内容。"; // 要写入的内容
JSONObject tableandcol=new JSONObject();
JSONArray colsArry=new JSONArray();
String sourceTablename=null;
String TargerTablename=null;
JSONArray allmappingRelationship=new JSONArray();
List<String> top10Table=new ArrayList<>();
for(String sqlrelation:relation){
JSONObject jsonObject1=new JSONObject();
JSONObject jsonObject=new JSONObject();
if (sqlrelation.split("#")[0].contains(".")&&sqlrelation.split("#")[0].split("\\.").length==3){
sourceTablename=sqlrelation.split("#")[0].split("\\.")[1];
jsonObject.put("parentName",sourceTablename);
if (!top10Table.contains(sourceTablename)){
top10Table.add(sourceTablename);
}
if (tableIDs.containsKey(sourceTablename)){
//jsonObject.put("sourceTableId",tableIDs.get(sourceTablename));
}
jsonObject.put("column",sqlrelation.split("#")[0].split("\\.")[2]);
// nameSchema.put(sqlrelation.split("#")[0].split("\\.")[1],sqlrelation.split("#")[0].split("\\.")[0]);
}
jsonObject1.put("source",jsonObject);
//JSONObject jsonObject2=new JSONObject();
JSONObject jsonObject3=new JSONObject();
if (sqlrelation.split("#")[1].contains(".")&&sqlrelation.split("#")[1].split("\\.").length==3){
TargerTablename=sqlrelation.split("#")[1].split("\\.")[1];
jsonObject3.put("parentName",TargerTablename);
if (tableIDs.containsKey(TargerTablename)){
//jsonObject.put("targetTableId",tableIDs.get(TargerTablename));
}
if (!top10Table.contains(TargerTablename)){
top10Table.add(TargerTablename);
}
jsonObject3.put("column",sqlrelation.split("#")[1].split("\\.")[2]);
// nameSchema.put(sqlrelation.split("#")[1].split("\\.")[1],sqlrelation.split("#")[1].split("\\.")[0]);
//relation.add(sql);
}
jsonObject1.put("target",jsonObject3);
allmappingRelationship.add(jsonObject1);
}
tableandcol.put("relations",allmappingRelationship);
JSONArray alltableInfo=new JSONArray();
JSONArray colInfo=new JSONArray();
Set<String> tableMap=new HashSet<>();
JSONObject singletableColInfo=new JSONObject();
int j=1;
int top=100;
int left=50;
for (TableCol tableCol:tableCols){
if (!top10Table.contains(tableCol.getTableName())){
continue;
}
j++;
if (tableMap.contains(tableCol.getTableName())){
JSONObject tableColInfo=new JSONObject();
tableColInfo.put("name",tableCol.getColName());
// tableColInfo.put("name",tableCol.getColRemark());
colInfo.add(tableColInfo);
}else{
singletableColInfo=new JSONObject();
if (tableIDs.containsKey(tableCol.getTableName())){
//singletableColInfo.put("id",tableIDs.get(tableCol.getTableName()));
singletableColInfo.put("id",tableCol.getTableName());
}
singletableColInfo.put("type","Middle");
/* if (j>2){
singletableColInfo.put("type","Middle");
}else if (j<tableCols.size()-1){
singletableColInfo.put("type","RS");
}else{
singletableColInfo.put("type","Origin");
}*/
singletableColInfo.put("name",tableCol.getTableName());
//singletableColInfo.put("tableRemark",tableCol.getTableRemark());
colInfo=new JSONArray();
JSONObject tableColInfo=new JSONObject();
tableColInfo.put("name",tableCol.getColName());
// tableColInfo.put("name",tableCol.getColRemark());
colInfo.add(tableColInfo);
tableMap.add(tableCol.getTableName());
singletableColInfo.put("columns",colInfo);
singletableColInfo.put("top",top);
singletableColInfo.put("left",left);
if (j%2==0){
left=left+100;
}else if(j%3==0){
left=left+100;
}else{
if (j>3){
left=left-100;
}
top=top+100;
}
alltableInfo.add(singletableColInfo);
}
}
tableandcol.put("nodes",alltableInfo);
try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath1))) {
writer.write(" var json = "+JSONObject.toJSONString(tableandcol));
System.out.println("文件创建成功，并写入了多行内容。");
} catch (IOException e) {
System.err.println("创建文件或写入文件时出错: " + e.getMessage());
}
System.out.println(JSONObject.toJSONString(tableandcol));
try (BufferedWriter writer = new BufferedWriter(new FileWriter(filePath))) {
relation.forEach(string-> {
try {
writer.write(string);
writer.newLine();
} catch (IOException e) {
throw new RuntimeException(e);
}
});
Set<String> tableNames=new HashSet<>();
tableCols.forEach(tableCol -> {
try {
if (tableNames.contains(tableCol.getTableName())){
writer.write(tableCol.getColName()+":"+tableCol.getColRemark());
}else{
writer.write(tableCol.getTableName()+":"+tableCol.getTableRemark());
writer.newLine();
writer.write(tableCol.getColName()+":"+tableCol.getColRemark());
tableNames.add(tableCol.getTableName());
}
writer.newLine();
} catch (IOException e) {
throw new RuntimeException(e);
}
});
System.out.println("文件创建成功，并写入了多行内容。");
} catch (IOException e) {
System.err.println("创建文件或写入文件时出错: " + e.getMessage());
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} finally {
// 关闭资源
try {
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
