一、业务需求
根据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);
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
