自动解析api接口数据并生成数据库表和插入数据

一、业务需求

根据api接口返回的数据进行自动解析，将解析的数据自动生成数据库的建表和插入数据的sql语句同时自动执行操作生成数据表和插入api接口的数据

二、技术架构和实现

1.技术架构

spring cloud微服务框架、Mybatis、MySQL数据库

2.实现思路

1）调用HttpClientUtil访问第三方api接口并返回json数据

2）将数据通过json解析工具进行解析，采用的是递归方式解数据结构

3）将解析好的数据结构保存到实体类，主要有json的kev和value及value的数据类型

4）将实体类进行遍历解析生成数据库表的建表和插入数据的sql语句

5）通过mybatis执行建表和插入数据的sql来完成在MySQL数据库中建表和插入数据的操作

三、核心代码

1.生成sql语句工具

import com.juncdt.platform.grab.dao.SqlMapper;
import com.juncdt.platform.grab.entity.JsonMetaNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @Auther: yaohongan
 * @Description //生成sql语句工具类
 * @Date： 2021/3/29 18:25
 */
@Service
public class GenerationSQLUtil {
    @Resource
   private SqlMapper sqlMapping;

    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";




    /**
     * @Auther: yaohongan
     * @Description //建表语句
     * @Date： 2021/4/8 11:18
     */
    public  void createTable(String tableName, List<JsonMetaNode> jsonMetaNodeList) {
        String sqlCreate = "CREATE TABLE " + tableName + "(\n" + getRowName(jsonMetaNodeList)+");";
        //检查数据库表是否存在
        Map<String,String> sqlMap = sqlMapping.checkTableExistsWithShow(tableName);
        //数据库表不存在创建表
        if(sqlMap==null) {
            sqlMapping.jsontosql(sqlCreate);
        }else {
            insertSql(tableName,jsonMetaNodeList);
        }
        System.out.println(sqlCreate);
    }
    /**
     * @Auther: yaohongan
     * @Description //获取插入sql语句
     * @Date： 2021/4/2 15:32
     */
    public void insertSql(String tableName,List<JsonMetaNode> jsonMetaNodeList){
        String insertSql ="";
        insertSql = "INSERT INTO " + tableName + " (\n"+getColumnName(jsonMetaNodeList)+")VALUES(\n"+getValue(jsonMetaNodeList)+");";
        sqlMapping.jsontosql(insertSql);
        System.out.println(insertSql);
    }

    /**
     * 获取建表语句的列名
     *
     * @author sql
     * @date 10/23/17 3:43 PM
     */
    public  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 = "";
            //判断id是否存在
            if (key.equals("id")){
                hasId = true;
            }
            if (INTEGER.equals(valueType)) {
                type = "int(10) NULL";
            } else if (LONG.equals(valueType)) {
                type = "bigint(100)";
            } else if (STRING.equals(valueType)) {
                type = "varchar(255)";
            } 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(255)";
            }
            sqlRowNameBuffer.append(key).append(" ").append(type);
            sqlRowNameBuffer.append(",");
        }
        if (!hasId){
            sqlRowNameBuffer.append("id").append(" ").append("int(10)").append(" ").append("NOT NULL").append(" ").append("AUTO_INCREMENT,").append(" PRIMARY KEY (`id`)\n");
        }
        if(sqlRowNameBuffer!=null&&sqlRowNameBuffer.length()>0){
            sqlRowNameBuffer.deleteCharAt(sqlRowNameBuffer.length() - 1);
        }
        String sqlRowName = sqlRowNameBuffer.toString();
        return sqlRowName;
    }

   /**
    * @Auther: yaohongan
    * @Description //获取插入数据表列名语句
    * @Date： 2021/4/6 10:49
    */
   public  String getColumnName(List<JsonMetaNode> jsonMetaNodes) {
        StringBuffer sqlColumnNameBuffer = new StringBuffer();
        for (JsonMetaNode jsonMetaNode : jsonMetaNodes) {
            String key = jsonMetaNode.getKey();
            sqlColumnNameBuffer.append(key).append(",");
        }
       if(sqlColumnNameBuffer!=null&&sqlColumnNameBuffer.length()>0){
           sqlColumnNameBuffer.deleteCharAt(sqlColumnNameBuffer.length() - 1);
       }
        String sqlRowName = sqlColumnNameBuffer.toString();
        return sqlRowName;
    }

        /**
         * @Auther: yaohongan
         * @Description //获取插入数据表数据语句
         * @Date： 2021/4/6 10:49
         */
        public  String getValue(List<JsonMetaNode> jsonMetaNodeList) {
            StringBuffer sqlValueBuffer = new StringBuffer();
            for (JsonMetaNode jsonMetaNode : jsonMetaNodeList) {
                Object value = jsonMetaNode.getValue();
                String valueType = jsonMetaNode.getValueType();
                String type = "";
                if (INTEGER.equals(valueType)) {
                    type = "int(10) NULL";
                } else if (LONG.equals(valueType)) {
                    type = "bigint(100)";
                } else if (STRING.equals(valueType)) {
                    type = "varchar(255)";
                } 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(255)";
                }
                if(type.equals("varchar(255)")){
                    sqlValueBuffer.append("'"+value+"'").append(",");
                }else{
                    sqlValueBuffer.append(value).append(",");
                }

            }
            if(sqlValueBuffer!=null&&sqlValueBuffer.length()>0){
                sqlValueBuffer.deleteCharAt(sqlValueBuffer.length() - 1);
            }
            String sqlRowName = sqlValueBuffer.toString();
            return sqlRowName;
        }

}

2.调用三方api接口http工具

import org.apache.http.NameValuePair;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.utils.URIBuilder;
import org.apache.http.entity.ContentType;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;

import java.io.IOException;
import java.net.URI;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class HttpClientUtil {

    /**
     * 带参数的get请求
     * @param url
     * @param param
     * @return String
     */
    public static String doGet(String url, Map<String, String> param) {
        // 创建Httpclient对象
        CloseableHttpClient httpclient = HttpClients.createDefault();

        String resultString = "";
        CloseableHttpResponse response = null;
        try {
            // 创建uri
            URIBuilder builder = new URIBuilder(url);
            if (param != null) {
                for (String key : param.keySet()) {
                    builder.addParameter(key, param.get(key));
                }
            }
            URI uri = builder.build();
            // 创建http GET请求
            HttpGet httpGet = new HttpGet(uri);
            // 执行请求
            response = httpclient.execute(httpGet);
            // 判断返回状态是否为200
            if (response.getStatusLine().getStatusCode() == 200) {
                resultString = EntityUtils.toString(response.getEntity(), "UTF-8");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (response != null) {
                    response.close();
                }
                httpclient.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return resultString;
    }

    /**
     * 不带参数的get请求
     * @param url
     * @return String
     */
    public static String doGet(String url) {
        return doGet(url, null);
    }

    /**
     * 带参数的post请求
     * @param url
     * @param param
     * @return String
     */
    public static String doPost(String url, Map<String, String> param) {
        // 创建Httpclient对象
        CloseableHttpClient httpClient = HttpClients.createDefault();
        CloseableHttpResponse response = null;
        String resultString = "";
        try {
            // 创建Http Post请求
            HttpPost httpPost = new HttpPost(url);
            // 创建参数列表
            if (param != null) {
                List<NameValuePair> paramList = new ArrayList<>();
                for (String key : param.keySet()) {
                    paramList.add(new BasicNameValuePair(key, param.get(key)));
                }
                // 模拟表单
                UrlEncodedFormEntity entity = new UrlEncodedFormEntity(paramList);
                httpPost.setEntity(entity);
            }
            // 执行http请求
            response = httpClient.execute(httpPost);
            resultString = EntityUtils.toString(response.getEntity(), "utf-8");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                response.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return resultString;
    }

    /**
     * 不带参数的post请求
     * @param url
     * @return String
     */
    public static String doPost(String url) {
        return doPost(url, null);
    }

    /**
     * 传送json类型的post请求
     * @param url
     * @param json
     * @return String
     */
    public static String doPostJson(String url, String json) {
        // 创建Httpclient对象
        CloseableHttpClient httpClient = HttpClients.createDefault();
        CloseableHttpResponse response = null;
        String resultString = "";
        try {
            // 创建Http Post请求
            HttpPost httpPost = new HttpPost(url);
            // 创建请求内容
            StringEntity entity = new StringEntity(json, ContentType.APPLICATION_JSON);
            httpPost.setEntity(entity);
            // 执行http请求
            response = httpClient.execute(httpPost);
            resultString = EntityUtils.toString(response.getEntity(), "utf-8");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                response.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return resultString;
    }
}

3.字段实体类

import lombok.Data;

import java.util.List;

/**
 * @Auther: yaohongan
 * @Date: 2021/3/29 18:11
 * @Description:
 */
@Data
public  class JsonMetaNode {
    private String key;
    private Object value;
    private String valueType;
    //数据库中的列名
    private String dbColName;
    private List<JsonMetaNode> children;

}

4.JSON解析工具

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONException;
import com.alibaba.fastjson.JSONObject;
import com.juncdt.platform.api.utils.HttpClientUtil;
import com.juncdt.platform.grab.entity.JsonMetaNode;
import com.juncdt.platform.grab.service.JsonParsingService;
import com.juncdt.platform.grab.service.WmDataService;
import com.juncdt.platform.grab.util.GenerationSQLUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * @Auther: yaohongan
 * @Date: 2021/4/7 10:21
 * @Description:JSON解析工具类
 */
@Service
public class JsonParsingServiceImpl {
    @Autowired
    GenerationSQLUtil generationSQLUtil;
    @Autowired
    WmDataService wmDataService;
    @Autowired
    RedisTemplate<String,Object> redisTemplate;

    /**
     * @Auther: yaohongan
     * @Description //根据api接口数据创建数据库表和插入数据
     * @Date： 2021/4/1 15:15
     */
    public JSONObject getJsonData(String url, String tableName, String code, String json) {
        //获取access_token
        String data = (String) redisTemplate.opsForValue().get(code);
        JSONObject jsonObject1 = JSONObject.parseObject(data);
        String accessToken = jsonObject1.getString("access_token");
        //1.调用API接口，获取到数据
        String str = HttpClientUtil.doPostJson(url+accessToken,json);
        JSONObject jsonObject = JSON.parseObject(str);
        //2.将获取到的数据转换为JSONObject格式
        //JSONObject jsonObject = JSON.parseObject("{\"code\":{\"errcode\":\"0\",\"errmsg\":\"处理成功\"},\"data\":{\"pageList\":[{\"goodsId\":44640100,\"isPutAway\":0,\"isExistEmptyStock\":false,\"salesNum\":0,\"isAllStockEmpty\":false,\"maxPrice\":100,\"sortNum\":0,\"title\":\"商品测试\",\"avaliableStockNum\":12,\"putAwayDate\":1514629080000,\"defaultImageUrl\":\"https://image-c-dev.weimobwmc.com/dev-saas-wxbiz/a28337e179d64b90a539f41c0cf19b54.jpg\",\"isMultiSku\":0,\"minPrice\":100}],\"totalCount\":1,\"pageNum\":1}}");
        //3.拿到data数据层
        JSONObject dataJson = (JSONObject) jsonObject.get("data");
        //4.创建数据库表
        List<JsonMetaNode> jsonMetaNodeList = new ArrayList<>();
        jsonMetaNodeList = analysisJsonKey(dataJson,jsonMetaNodeList);
        Set<JsonMetaNode> jsonMetaNodeHashSet = new HashSet<>(jsonMetaNodeList);
        List<JsonMetaNode> list = new ArrayList<>(jsonMetaNodeHashSet);
        generationSQLUtil.createTable(tableName,list);
        //5.插入数据
        analysisJsonValue(dataJson,tableName);
        return jsonObject;
    }

    /**
     * @Auther: yaohongan
     * @Description //解析json取出kev创建数据库表
     * @Date： 2021/4/9 14:54
     */
    public List<JsonMetaNode> analysisJsonKey(JSONObject jsonObject,List<JsonMetaNode> jsonMetaNodeList) {
        Set<String> strings = jsonObject.keySet();
        for (String str : strings) {
            //获取value值
            Object o = jsonObject.get(str);
            JsonMetaNode jsonMetaNode = new JsonMetaNode();
            jsonMetaNode.setKey(str);
            //存入value值的类型
            String name = "";
            if(o!=null){
                name = o.getClass().getName();
            }
            if(name.equals("com.alibaba.fastjson.JSONArray")||name.equals("com.alibaba.fastjson.JSONObject")){
                jsonMetaNode.setValueType("java.lang.String");
            }else{
                jsonMetaNode.setValueType(name);
            }

            //将数据存入list中
            jsonMetaNodeList.add(jsonMetaNode);

            if(isJson(o)) {
                jsonMetaNodeList=recursionKey(o, jsonMetaNodeList);
            }
        }
        return jsonMetaNodeList;
    }
    /**
     * @Auther: yaohongan
     * @Description //判断json类型
     * @Date： 2021/4/9 14:55
     */
    public boolean isJson(Object o){
        if(o instanceof JSONObject){
            return true;
        }else if(o instanceof JSONArray){
            return true;
        }
        return false;
    }
    /**
     * @Auther: yaohongan
     * @Description //递归遍历
     * @Date： 2021/4/9 14:56
     */
    public  List<JsonMetaNode> recursionKey(Object o,List<JsonMetaNode> jsonMetaNodeList){
        if(o instanceof JSONObject){
            jsonMetaNodeList=analysisJsonKey((JSONObject) o,jsonMetaNodeList);
        }else{
            jsonMetaNodeList=paseArray(o,jsonMetaNodeList);
        }
        return jsonMetaNodeList;
    }
    /**
     * @Auther: yaohongan
     * @Description //解析jsonArray
     * @Date： 2021/4/9 14:59
     */
    public List<JsonMetaNode> paseArray(Object o, List<JsonMetaNode> jsonMetaNodeList){
        JSONArray jsonArray = (JSONArray) o;
        for (int i = 0; i < jsonArray.size(); i++){
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            if(jsonObject!=null){
                jsonMetaNodeList=analysisJsonKey(jsonObject,jsonMetaNodeList);
            }
        }
        return jsonMetaNodeList;
    }




    /**
     * @Auther: yaohongan
     * @Description //解析json插入数据
     * @Date： 2021/4/2 14:05
     */
    public void analysisJsonValue(JSONObject jsonObject, String tableName) {
        List<JsonMetaNode> jsonMetaNodeList = new ArrayList<>();
        Set<String> strings = jsonObject.keySet();
        for (String str : strings) {
            JsonMetaNode jsonMete = new JsonMetaNode();
            //获取value值
            Object o = jsonObject.get(str);
            if (recursion(o, tableName)) {
                if(str!=null&&str!="null"){
                    //存入key值
                    jsonMete.setKey(str);
                }
                if(o!=null&&o!="null"){
                    //存入value值的类型
                    String name = o.getClass().getName();
                    jsonMete.setValueType(name);
                    jsonMete.setValue(o);
                }
                if(str!=null&&str!="null" &&o!=null&&o!="null"){
                    jsonMetaNodeList.add(jsonMete);
                }

            }
        }
        generationSQLUtil.insertSql(tableName,jsonMetaNodeList);
    }

    /**
     * @Auther: yaohongan
     * @Description //递归解析json
     * @Date： 2021/4/2 14:03
     */
    public  boolean recursion(Object o,String tableName){
        if(o instanceof JSONObject){
            analysisJsonValue((JSONObject) o,tableName);
            return false;
        }else if(o instanceof JSONArray){
            paseArray(o,tableName);
            return false;
        }
        return true;
    }



    /**
     * @Auther: yaohongan
     * @Description //解析 jsonArray
     * @Date： 2021/4/2 14:04
     */
    public  void paseArray(Object o, String tableName){
        JSONArray jsonArray = (JSONArray) o;
        for (int i = 0; i < jsonArray.size(); i++){
            JSONObject jsonObject = (JSONObject) jsonArray.get(i);
            if(jsonObject!=null){
                analysisJsonValue(jsonObject,tableName);
            }
        }
    }



}

5.sql执行接口

import org.apache.ibatis.annotations.Mapper;

import javax.annotation.Resource;
import java.util.Map;

/**
 * @Auther: yaohongan
 * @Date: 2021/3/31 15:35
 * @Description:
 */
@Mapper
public interface SqlMapper {

    //创建数据库表
    void jsontosql(String sql);

    //检查数据库表是否存在
    Map<String, String> checkTableExistsWithShow(String tableName);

}

6.sql执行映射xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="xxx.dao.SqlMapper">

    <select id="jsontosql"  resultType="java.lang.Integer">
        ${sql}
    </select>

    <select id="checkTableExistsWithShow"
            resultType="java.util.Map">
        show tables like #{tableName}
    </select>



</mapper>

7.业务外层

import com.alibaba.fastjson.JSONObject;
import com.juncdt.platform.api.utils.R;
import com.juncdt.platform.grab.service.JsonParsingService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.Map;

/**
 * @Auther: yaohongan
 * @Date: 2021/4/8 14:50
 * @Description:
 */
@RestController
@RequestMapping("json")
@Api(value = "json解析",tags = "json解析")
public class AnalysisJsonController {
    @Autowired
    private JsonParsingServiceImpl jsonParsingService;


    /**
     * @Auther: yaohongan
     * @Description //解析Json
     * @Date： 2021/4/8 14:52
     */
    @PostMapping("/info")
    @ApiOperation(value = "json解析",notes = "json解析")
    public R info(String url, String tableName, String code, @RequestBody String map){
        JSONObject jsonObject = jsonParsingService.getJsonData(url,tableName,code,map);
        return R.ok().put("data", jsonObject);
    }
}