关于JsonSchema转成Postgres sql ddl的小结

约定不等于承诺〃 2023-07-08 06:58 8阅读 0赞

1. 首先，先了解下[JsonSchema][]吧。

2. 因为最近需要**快速地将json schema转成建表语句**，所以以下整理总结了一个比较方便的工具类。

1) 先假设我们有一个这样的json schema文件：

{
      "$schema": "http://json-schema.org/draft-04/schema#",
      "title": "Commodities.Forward.Forward.InstRefDataReporting.V1",
      "LastModifyDateTime": "2018-07-23T12:56:35",
      "description": "Record template for Commodities.Forward.Forward",
      "copyright": "Copyright © 2017 The Derivatives Service Bureau (BAS) Limited. All Rights Reserved.",
      "type": "object",
      "properties": {
        "TemplateVersion": {
          "title": "Template Version",
          "description": "Template Version",
          "type": "integer"
        },
        "Header": {
          "title": "Header",
          "type": "object",
          "properties": {
            "AssetClass": {
              "title": "Asset Class",
              "description": "As defined by CFI code: ISO 10962 (2015); Character #2",
              "type": "string",
              "enum": [
                "Commodities"
              ]
            },
            "InstrumentType": {
              "title": "Instrument Type",
              "description": "As defined by CFI code: ISO 10962 (2015); Character #1",
              "type": "string",
              "enum": [
                "Forward"
              ]
            }
          },
          "required": [
            "AssetClass",
            "InstrumentType"
          ],
          "additionalProperties": false
        },
        "ISIN": {
          "type": "object",
          "properties": {
            "ISIN": {
              "title": "Identification",
              "description": "International Securities Identification Number (ISO 6166)",
              "type": "string"
            },
            "Status": {
              "title": "Status",
              "description": "Status of the ISIN",
              "type": "string",
              "enum": [
                "New",
                "Updated",
                "Expired",
                "Deleted"
              ],
              "elaboration": {
                "New": "New ISIN added to the database",
                "Updated": "One/several of the fields in the ISIN record is/are updated",
                "Expired": "ISIN that has already matured relative of the specified Expiry Date",
                "Deleted": "The instrument associated with the ISIN is no longer active"
              }
            }
          },
          "required": [
            "ISIN",
            "Status"
          ],
          "additionalProperties": false
        }
      },
      "required": [
        "TemplateVersion",
        "Header",
        "ISIN"
      ],
      "additionalProperties": false
    }

2) 以上，我们需要将这个文件名作为表名，properties里面的一个个object名作为字段名，特别地，object可能包含properties，这个时候就需要遍历子节点获取更深层次的字段名了。description作为字段描述，type作为字段类型。

注：这里没有约束字段长度和主键。

3. 解析json schema的工具类。

1) 读取jsonschema.json文件内容。

public static String readJsonScehma(String filePath) {
    	try (InputStream in = new FileInputStream(filePath);
    			BufferedReader in2 = new BufferedReader(new InputStreamReader(in))) {
    		String content = "", temp = "";
    		while ((temp = in2.readLine()) != null) {
    			content += temp;
    		}
    		return content;
    	} catch (IOException e) {
    		e.printStackTrace();
    	}
    	return null;
    }

2) 生成表名。

public static String generateTableName(File jsonScehmaFile) {
    	String tableName = jsonScehmaFile.getName().replace(".json", "").replace(".V1", "");
    	String sp[] = tableName.split("\\.");
    	String markStr[] = sp[2].split("_");
    	String markString = sp[2];
    	if (markStr.length > 2)
    		markString = markStr[markStr.length - 3] + "_" + markStr[markStr.length - 2] + "_"
    				+ markStr[markStr.length - 1];
    	tableName = sp[0] + "_" + sp[1] + "_" + markString;
    	
    
    	return tableName;
    }

注：其实表名是自定义。而因为我的场景是有许多个诸如Commodities.Bastriver.Forward.InstRefDataReporting.json这样的schema文件，所以截取了文件名的一部分作为表名，因为**postgres的表名长度不能大于63个字符**。

3) 递归遍历所有properties的字段。

public static void getChildJsonObject(String nodeName, JSONObject node, JSONArray requiredObject) {
    	// 遍历子节点下的所有字段和字段类型、注释
    	for (Map.Entry<String, Object> entry : node.entrySet()) {
    		// System.out.println(entry.getKey() + ":" + entry.getValue());
    		JSONObject fieldsJsonObject = JSONObject.parseObject(entry.getValue().toString());
    		if (fieldsJsonObject.containsKey("properties")) {
    			// 有子节点
    			JSONArray temp = fieldsJsonObject.getJSONArray("required");
    			getChildJsonObject(entry.getKey(), fieldsJsonObject.getJSONObject("properties"), temp);
    		} else {
    			String description = fieldsJsonObject.getString("description");
    			String type = fieldsJsonObject.getString("type");
    
    			String fieldKey = nodeName.isEmpty() ? entry.getKey() : nodeName +"_"+ entry.getKey();
    			fieldSet.add(fieldKey);
    			fieldDescriptionMap.put(fieldKey, description);
    			fieldTypeMap.put(fieldKey, typeMap.get(type) == null ? "Varchar(200)" : typeMap.get(type));
    			fieldRequiredMap.put(fieldKey, " NULL");
    			if(requiredObject != null)
    				for (Object requiredName : requiredObject) {
    					if (requiredName.toString().equals(entry.getKey())) {
    						fieldRequiredMap.put(fieldKey, " Not NULL");
    						break;
    					}
    				}
    		}
    	}
    }

4. 主要是以上的遍历工具类，如果时间紧迫，也可以[下载完整代码][Link 1]。**如果有什么不懂的欢迎大家在评论区提出来**。

[JsonSchema]: https://www.jianshu.com/p/a41b540113f9
[Link 1]: https://download.csdn.net/download/BAStriver/12191024