json转sql create table

解析json来创建数据库表sql语句

entity层

package org.example.module.jd.entity;

import java.util.List;

/**
 * @Auther: yaohongan
 * @Date: 2021/3/29 18:11
 * @Description:
 */
public  class JsonMetaNode {
    private String key;
    private String valueType;
    //数据库中的列名
    private String dbColName;
    private List<JsonMetaNode> children;
    public JsonMetaNode() {
    }
    public JsonMetaNode(String key, String valueType) {
        this.key = key;
        this.valueType = valueType;
    }
    public String getKey() {
        return key;
    }
    public String getValueType() {
        return valueType;
    }
    public void setKey(String key) {
        this.key = key;
    }
    public void setValueType(String valueType) {
        this.valueType = valueType;
    }
}

server层

package org.example.module.jd.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.example.module.jd.entity.JsonMetaNode;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

/**
 * @Auther: yaohongan
 * @Description //TODO 
 * @Date： 2021/3/29 18:25
 */
@Service
public class CreateTableSQL {
    public static String INTEGER = "java.lang.Integer";
    public static String LONG = "java.lang.Long";
    public static String STRING = "java.lang.String";
    public static String JSONOBJECT = "com.alibaba.fastjson.JSONObject";
    public static String FLOAT = "java.lang.Float";
    public static String DOUBLE = "java.lang.Double";
    public static String BIG_DECIMAL = "java.math.BigDecimal";
    public static String DATE = "java.util.Date";

    

    /**
     * 建表语句
     *
     * @author sql
     * @date 10/23/17 3:43 PM
     */
    public  String createTable(String tableName, List<JsonMetaNode> jsonMetaNodeList) {
        String sqlCreate = "CREATE TABLE " + tableName + "(\n" + getRowName(jsonMetaNodeList);
        return sqlCreate;

    }

    /**
     * 获取建表语句的列名
     *
     * @author sql
     * @date 10/23/17 3:43 PM
     */
    private  String getRowName(List<JsonMetaNode> jsonMetaNodeList) {
        StringBuffer sqlRowNameBuffer = new StringBuffer();
        boolean hasId = false;
        for (JsonMetaNode jsonMetaNode : jsonMetaNodeList) {
            String key = jsonMetaNode.getKey();
            String valueType = jsonMetaNode.getValueType();
            String type = "";
            if (INTEGER.equals(valueType)) {
                type = "int(100)";
            } else if (LONG.equals(valueType)) {
                type = "bigint(100)";
            } else if (STRING.equals(valueType)) {
                type = "varchar(100)";
            } else if (BIG_DECIMAL.equals(valueType)) {
                type = "decimal(18,8)";
            } else if (FLOAT.equals(valueType)) {
                type = "float(100,10)";
            } else if (DOUBLE.equals(valueType)) {
                type = "double(100,10)";
            } else if (DATE.equals(valueType)) {
                type = "datetime";
            } else {
                type = "varchar(100)";
            }
            sqlRowNameBuffer.append(key).append(" ").append(type).append(" ").append("CHARACTER SET utf8 NULL");
            if (key.equals("id")){
                hasId = true;
                sqlRowNameBuffer.append(" ").append("NOT NULL AUTO_INCREMENT,");
            }else {
                sqlRowNameBuffer.append(",");
            }
        }
        if (!hasId){
            sqlRowNameBuffer.append("id").append(" ").append("int(100)").append(" ").append(" ")
                    .append("NOT NULL AUTO_INCREMENT,").append(" PRIMARY KEY (`id`),");
        }
        sqlRowNameBuffer.deleteCharAt(sqlRowNameBuffer.length() - 1);
        sqlRowNameBuffer.append(") ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;");
        String sqlRowName = sqlRowNameBuffer.toString();
        return sqlRowName;
    }


}

contort测试

package org.example.module.jd.controller;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.example.common.exception.GeneralException;
import org.example.common.io.Output;
import org.example.module.jd.entity.JsonMetaNode;
import org.example.module.jd.service.CreateTableSQL;
import org.example.module.jd.service.JdService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

/**
 * @Auther: yaohongan
 * @Description //TODO 
 * @Date： 2021/3/29 18:30
 */
@RestController
@RequestMapping("/jd")
public class JdController {
    @Autowired
    CreateTableSQL createTableSQL;

    /**
     * @Auther: yaohongan
     * @Description //TODO 
     * @Date： 2021/3/29 18:31
     */
    @GetMapping("/jsontosql")
    public String jsontosql() throws GeneralException {
        List<JsonMetaNode> jsonMetaNodeList = new ArrayList<>();
        String s = "{\"p_id\":\"73\",\"p_title\":\"微光波炉\\/烤箱保养\",\"p_mode\":\"1\",\"p_summary\":\"健康生活\",\"p_icon\":\"1491634953186.jpeg\",\"p_imageUrl\":null,\"p_priceA\":\"80\",\"p_priceB\":null,\"p_duration\":\"60\",\"p_introduce\":\"微波炉、烤箱等长时间不清洁会造成机器内汤汁堆积，对加热食物带来种种异味，滋生细菌，直接会导致肠道疾病。我们的高温深层次消毒服务，将微波炉进行深度清洁，去异味，高温杀菌，保障健康生活，延长微波炉使用寿命。\",\"p_fit_people\":\"微波炉\\/烤箱\",\"p_service_introduce\":\"\",\"p_pubtime\":\"1491634954\",\"p_pv\":\"1927\",\"c_id\":\"30\",\"m_id\":\"100\"}";
        // 转换成json对象
        JSONObject jsonObject = (JSONObject) JSON.parse(s);
        Set<String> strings = jsonObject.keySet();
        Iterator<String> iterator = strings.iterator();
        // 遍历json对象，根据key获取value并获取value的类型
        while (iterator.hasNext()) {
            JsonMetaNode jsonMete = new JsonMetaNode();
            String next = iterator.next();
            jsonMete.setKey(next);
            Object o = jsonObject.get(next);
            if (o != null) {
                String name = o.getClass().getName();
                jsonMete.setValueType(name);
            }
            jsonMetaNodeList.add(jsonMete);
        }
        //　调用建表语句的方法
        String sqlCreateTable = createTableSQL.createTable("sql_test", jsonMetaNodeList);
        System.out.println(sqlCreateTable);
        return sqlCreateTable;
    }

}

结果