package com.ltkj.common.utils;
|
|
import cn.hutool.core.util.RandomUtil;
|
import cn.hutool.json.JSONArray;
|
import cn.hutool.json.JSONObject;
|
import cn.hutool.json.JSONUtil;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
|
import java.io.BufferedReader;
|
import java.io.IOException;
|
import java.io.InputStreamReader;
|
import java.io.OutputStream;
|
import java.net.HttpURLConnection;
|
import java.net.URL;
|
import java.nio.charset.StandardCharsets;
|
import java.sql.*;
|
import java.util.ArrayList;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @Company: 西安路泰科技有限公司
|
* @Author: zhaowenxuan
|
* @Date: 2024/5/21 16:41
|
*/
|
public class DictionaryUtil {
|
private static final Logger log = LoggerFactory.getLogger(DictionaryUtil.class);
|
private static final String BASE_API_URL = "https://mock.mengxuegu.com/mock/664d3dbce45d2156fa209c80/example/api/His/HisRequest";
|
private static List<String> methods = new ArrayList<>();
|
private static final String url = "jdbc:mysql://localhost:3306/test_urlreq";
|
private static final String user = "root";
|
private static final String password = "Root_ltkj123";
|
|
static {
|
methods.add("JGDM");
|
// methods.add("ZDZYZ");
|
}
|
|
public static void main(String[] args) {
|
DictionaryUtil util = new DictionaryUtil();
|
util.exec();
|
}
|
|
private void exec() {
|
JSONObject entries = execRequest();
|
if (entries != null) {
|
JSONObject response = entries.getJSONObject("Response");
|
// 0 成功 -1失败
|
if ("0".equals(response.getStr("ResultCode"))) {
|
JSONArray jsonArray = response.getJSONArray("ResultData");
|
JSONObject resultDataIndex1 = jsonArray.getJSONObject(0);
|
String method = entries.getStr("method");
|
String tabName = "ltkj_" + method;
|
Connection connection = getConnection();
|
try {
|
if (!tabIsExists(connection, tabName)) {
|
// 创建表
|
creatTable(resultDataIndex1, tabName, connection);
|
}
|
// 对比字段 并插入数据
|
operationTable(jsonArray, tabName, connection);
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
}
|
}
|
}
|
|
/**
|
* 执行http请求
|
*
|
* @return
|
*/
|
private JSONObject execRequest() {
|
URL url = null;
|
HttpURLConnection connection = null;
|
try {
|
url = new URL(BASE_API_URL);
|
connection = (HttpURLConnection) url.openConnection();
|
connection.setRequestMethod("POST");
|
connection.setDoOutput(true);
|
connection.setRequestProperty("Accept", "application/json");
|
connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
|
OutputStream stream = connection.getOutputStream();
|
int index = 0;
|
if (methods.size() > 1) {
|
index = RandomUtil.randomInt(0, methods.size() - 1);
|
}
|
String method = methods.get(index);
|
String param = "method=" + method;
|
stream.write(param.getBytes(StandardCharsets.UTF_8));
|
BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
|
StringBuilder builder = new StringBuilder();
|
String str;
|
while ((str = reader.readLine()) != null) {
|
builder.append(str);
|
}
|
log.info("执行请求响应：{}", builder.toString());
|
JSONObject entries = JSONUtil.parseObj(builder.toString());
|
entries.set("method", method);
|
return entries;
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
if (connection != null) {
|
connection.disconnect();
|
}
|
}
|
return null;
|
}
|
|
private Connection getConnection() {
|
try {
|
return DriverManager.getConnection(url, user, password);
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
return null;
|
}
|
|
/**
|
* 表是否存在
|
*
|
* @param connection
|
* @param tableName
|
* @return
|
* @throws SQLException
|
*/
|
private Boolean tabIsExists(Connection connection, String tableName) throws SQLException {
|
String tabSql = "SELECT table_name FROM information_schema.tables WHERE table_schema = ? AND table_name = ?";
|
PreparedStatement statement = connection.prepareStatement(tabSql);
|
statement.setString(1, "test_urlreq");
|
statement.setString(2, tableName);
|
log.info("判断表是否存在 sql-> {}", statement.toString());
|
ResultSet resultSet = statement.executeQuery();
|
boolean next = resultSet.next();
|
statement.close();
|
return next;
|
}
|
|
/**
|
* 操作表
|
*
|
* @param jsonArray
|
* @param tabName
|
* @param connection
|
* @throws SQLException
|
*/
|
private void operationTable(JSONArray jsonArray, String tabName, Connection connection) throws SQLException {
|
List<String> columns = getColumns(tabName, connection);
|
log.info("当前表字段为 ->{}", columns);
|
ArrayList<String> responseColums = new ArrayList<>();
|
for (int i = 0; i < jsonArray.size(); i++) {
|
JSONObject jsonObject = jsonArray.getJSONObject(i);
|
for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
|
responseColums.add(entry.getKey());
|
}
|
log.info("请求返回的字段为 ->{}", responseColums);
|
responseColums.removeAll(columns);
|
if (!responseColums.isEmpty()) {
|
// 需要增加字段 并插入数据
|
for (String colum : responseColums) {
|
String sql = "alter table " + tabName + " add column " + colum + " text null";
|
Statement statement = connection.createStatement();
|
statement.executeUpdate(sql);
|
statement.close();
|
}
|
insertData(tabName, connection, jsonObject);
|
} else {
|
insertData(tabName, connection, jsonObject);
|
}
|
}
|
}
|
|
/**
|
* 插入数据
|
*
|
* @param tabName
|
* @param connection
|
* @param jsonObject
|
* @throws SQLException
|
*/
|
private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
|
// 插入数据前 先查询数据是否存在
|
StringBuilder selectSqlBuilder = new StringBuilder();
|
StringBuilder insertSqlBuilder = new StringBuilder();
|
StringBuilder valueBuilder = new StringBuilder();
|
insertSqlBuilder.append("insert into ").append(tabName).append(" (");
|
selectSqlBuilder.append("select count(1) as count from ").append(tabName).append(" where ");
|
for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
|
insertSqlBuilder.append(entry.getKey()).append(", ");
|
valueBuilder.append("'").append(entry.getValue()).append("', ");
|
selectSqlBuilder.append(entry.getKey()).append(" = '").append(entry.getValue()).append("' and ");
|
}
|
insertSqlBuilder.delete(insertSqlBuilder.length() - 2, insertSqlBuilder.length());
|
valueBuilder.delete(valueBuilder.length() - 2, valueBuilder.length());
|
selectSqlBuilder.delete(selectSqlBuilder.length() - 5, selectSqlBuilder.length());
|
insertSqlBuilder.append(") values (").append(valueBuilder).append(")");
|
log.info("插入前查询 sql ->{}", selectSqlBuilder.toString());
|
Statement statement = connection.prepareStatement(selectSqlBuilder.toString());
|
ResultSet resultSet = statement.executeQuery(selectSqlBuilder.toString());
|
resultSet.next();
|
String string = resultSet.getString("count");
|
statement.close();
|
// 如果不为0 则这条数据存在 不进行插入
|
if (!"0".equals(string)) {
|
log.info("数据存在不需要插入 {}", jsonObject);
|
return;
|
}
|
// 插入数据
|
statement = connection.createStatement();
|
log.info("插入数据 sql-> {}", insertSqlBuilder.toString());
|
statement.execute(insertSqlBuilder.toString());
|
statement.close();
|
}
|
|
/**
|
* 获取表的列
|
*
|
* @param tabName
|
* @param connection
|
* @return
|
* @throws SQLException
|
*/
|
private List<String> getColumns(String tabName, Connection connection) throws SQLException {
|
DatabaseMetaData metaData = connection.getMetaData();
|
ResultSet columns = metaData.getColumns(null, null, tabName, null);
|
ArrayList<String> tabColumns = new ArrayList<>();
|
while (columns.next()) {
|
String columnName = columns.getString("column_name");
|
tabColumns.add(columnName);
|
}
|
return tabColumns;
|
}
|
|
/**
|
* 创建表
|
*
|
* @param resultDataIndex1 返回数据中的第一个参数
|
* @param tabName
|
* @param connection
|
* @throws SQLException
|
*/
|
private void creatTable(JSONObject resultDataIndex1, String tabName, Connection connection) throws SQLException {
|
StringBuilder sql = new StringBuilder("CREATE TABLE " + tabName + " (");
|
for (Map.Entry<String, Object> entry : resultDataIndex1.entrySet()) {
|
sql.append(entry.getKey()).append(" text null,");
|
}
|
sql = new StringBuilder(sql.substring(0, sql.length() - 1));
|
sql.append(");");
|
Statement statement = connection.createStatement();
|
statement.execute(sql.toString());
|
}
|
}
|
