package com.example.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.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.Date;
|
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 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";
|
// 需要分页的代码集合
|
private static List<String> limits = new ArrayList<>();
|
|
/**
|
* 暂时不做
|
* 1.18 计费明细项目 MXJFXMZD
|
* 1.90 门诊明细计费及组合计费项目 MZJFZHMXXM
|
*
|
*/
|
|
// 分页每次1000条
|
static {
|
methods.add("JGDM");
|
methods.add("ZDZYZ");
|
// methods.add("YQZD");
|
// methods.add("KSFLZD");
|
// methods.add("BQZD");
|
// methods.add("ZKKSZD");
|
// methods.add("KSZD");
|
// methods.add("YWCKZD");
|
// methods.add("GHZLZD");
|
// methods.add("YHZD");
|
// methods.add("XYZDZD");
|
// methods.add("ZYZDZD");
|
// methods.add("SSZD");
|
// methods.add("ZLXTXZD");
|
// methods.add("ZYZZZFZD");
|
// methods.add("YJJFXMZD");
|
// methods.add("EJJFXMZD");
|
// methods.add("ZHFLXMZD");
|
// methods.add("JFZHXMZD");
|
// methods.add("JFZHGLMXJFXMZD");
|
// methods.add("CFLXZD");
|
// methods.add("YPZLZD");
|
// methods.add("YPJXZD");
|
// methods.add("YPGXZD");
|
// methods.add("YPLXZD");
|
// methods.add("JLDWZD");
|
// methods.add("YFZD");
|
// methods.add("YPZD");
|
// methods.add("JYKSZD");
|
// methods.add("JYYSZD");
|
// methods.add("JYFZZD");
|
// methods.add("JYFLZD");
|
// methods.add("JYFFZD");
|
// methods.add("JYBBZD");
|
// methods.add("JYXMZD");
|
// methods.add("JYZBZD");
|
// methods.add("JYXMZBZD");
|
// methods.add("JFXMGLJYXMZD");
|
// methods.add("JFZHGLJYXMZD");
|
// methods.add("JCSBZD");
|
// methods.add("JCBWFLZD");
|
// methods.add("JCBWZD");
|
// methods.add("JCBWMSZD");
|
// methods.add("JFXMGLJCBWZD");
|
// methods.add("JFZHXMGLJCBWZD");
|
//
|
// methods.add("TJDWXZZD");
|
// methods.add("TJRYLBZD");
|
// methods.add("TJLBZD");
|
// methods.add("TJFQZD");
|
// methods.add("TJKSZD");
|
// methods.add("TJYSZD");
|
// methods.add("TJXMZD");
|
// methods.add("TJZHXMZD");
|
// methods.add("TJZHGLMXZD");
|
// methods.add("TJTCZD");
|
// methods.add("TJTCGLTJZHZD");
|
//
|
// methods.add("HTDWZD");
|
// methods.add("ZFLXZD");
|
// methods.add("MZJCJYSQD");
|
// methods.add("MZJCJYSQXMFL");
|
// methods.add("MZJCJYSQDDYXM");
|
// methods.add("BRFB");
|
|
limits.add("ZDZYZ");
|
limits.add("KSZD");
|
limits.add("YHZD");
|
limits.add("XYZDZD");
|
limits.add("ZYZDZD");
|
limits.add("SSZD");
|
limits.add("ZLXTXZD");
|
limits.add("ZYZZZFZD");
|
limits.add("JFZHGLMXJFXMZD");
|
limits.add("YPZD");
|
limits.add("JYKSZD");
|
limits.add("JYYSZD");
|
limits.add("JYXMZD");
|
limits.add("JYZBZD");
|
limits.add("JYXMZBZD");
|
limits.add("JFXMGLJYXMZD");
|
limits.add("JFZHGLJYXMZD");
|
limits.add("JFXMGLJCBWZD");
|
limits.add("TJKSZD");
|
limits.add("TJYSZD");
|
limits.add("TJXMZD");
|
limits.add("TJZHXMZD");
|
limits.add("TJZHGLMXZD");
|
limits.add("TJTCZD");
|
limits.add("TJTCGLTJZHZD");
|
}
|
|
public static void main(String[] args) {
|
DictionaryUtil util = new DictionaryUtil();
|
// util.exec();
|
util.exec1();
|
}
|
|
private void exec1() {
|
int size = 1000;
|
Connection connection = getConnection();
|
methods.forEach(method -> {
|
System.out.println("循坏的接口代码 ->" + method);
|
String tabName = "ltkj_" + method;
|
// String params = "method="+method;
|
boolean isLimit = false;
|
int page = 1;
|
int maxPage = 1;
|
JSONObject object = JSONUtil.createObj();
|
object.putOpt("method", method);
|
if (limits.contains(method)) {
|
isLimit = true;
|
// params+="&pagecount="+size+"&page="+page;
|
object.putOpt("pagecount", size);
|
object.putOpt("page", page);
|
}
|
JSONObject entries = execRequest(object.toString());
|
// JSONObject entries = execRequest(params);
|
assert entries != null;
|
JSONObject response = entries.getJSONObject("Response");
|
if ("0".equals(response.getStr("ResultCode"))) {
|
if (isLimit) {
|
maxPage = LimitInsertData(connection, tabName, response);
|
log.info("计算页码为 ->{}", maxPage);
|
if (maxPage > 1) {
|
page += 1;
|
for (int i = page; i <= maxPage; i++) {
|
// params+="&pagecount="+size+"&page="+page;
|
object.clear();
|
object.putOpt("method", method);
|
object.putOpt("pagecount", size);
|
object.putOpt("page", page);
|
// entries = execRequest(params);
|
entries = execRequest(object.toString());
|
assert entries != null;
|
response = entries.getJSONObject("Response");
|
if ("0".equals(response.getStr("ResultCode"))) {
|
LimitInsertData(connection, tabName, response);
|
} else {
|
// log.error("请求失败："+params);
|
log.error("请求失败：" + object.toString());
|
}
|
page++;
|
}
|
}
|
} else {
|
JSONArray resultData = response.getJSONArray("ResultData");
|
JSONObject jsonObject = JSONUtil.toList(resultData, JSONObject.class).get(0);
|
editDataBase(connection, tabName, resultData, jsonObject);
|
}
|
} else {
|
// log.error("请求失败："+params);
|
log.error("请求失败：" + object.toString());
|
}
|
});
|
}
|
|
/**
|
* 针对于分页接口 创建表、插入数据
|
*
|
* @param connection
|
* @param tabName
|
* @param response
|
* @return
|
*/
|
private int LimitInsertData(Connection connection, String tabName, JSONObject response) {
|
int maxPage;
|
// 行数
|
Integer rowNumber = response.getInt("RowNumber");
|
// 总条数
|
Integer totalCount = response.getInt("totalCount");
|
JSONArray jsonArray = response.getJSONArray("ResultData");
|
maxPage = (totalCount + 1000 - 1) / 1000;
|
System.out.println("集合大小 ->" + jsonArray.size());
|
jsonArray.forEach(obj -> {
|
editDataBase(connection, tabName, jsonArray, (JSONObject) obj);
|
});
|
return maxPage;
|
}
|
|
/**
|
* 操作数据库
|
*
|
* @param connection
|
* @param tabName
|
* @param jsonArray
|
* @param obj
|
*/
|
private void editDataBase(Connection connection, String tabName, JSONArray jsonArray, JSONObject obj) {
|
try {
|
if (!tabIsExists(connection, tabName)) {
|
// 创建表
|
creatTable(obj, tabName, connection);
|
}
|
// 对比字段 并插入数据
|
operationTable(obj, tabName, connection);
|
} catch (SQLException throwables) {
|
log.error("数据库操作异常");
|
log.error(throwables.getSQLState());
|
log.error(throwables.getMessage());
|
}
|
}
|
|
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((JSONObject) jsonArray.get(0), tabName, connection);
|
} catch (SQLException throwables) {
|
throwables.printStackTrace();
|
}
|
}
|
}
|
}
|
|
/**
|
* 执行post请求
|
*
|
* @param params 请求参数 例如： param1=val1¶m2=val2
|
* @return 请求返回的json转换后的JSONObject对象
|
*/
|
private JSONObject execRequest(String params) {
|
// System.out.println(params);
|
URL url = null;
|
HttpURLConnection connection = null;
|
try {
|
BASE_API_URL = "http://localhost:5011/api/His/HisRequest";
|
url = new URL(BASE_API_URL);
|
connection = (HttpURLConnection) url.openConnection();
|
connection.setRequestMethod("POST");
|
connection.setRequestProperty("Accept", "application/json");
|
// connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
|
connection.setRequestProperty("Content-Type", "application/json;utf-8");
|
connection.setDoOutput(true);
|
OutputStream stream = connection.getOutputStream();
|
stream.write(params.getBytes(StandardCharsets.UTF_8));
|
// System.out.println(connection.getResponseCode());
|
BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
|
StringBuilder builder = new StringBuilder();
|
String str;
|
while ((str = reader.readLine()) != null) {
|
builder.append(str);
|
}
|
log.info("请求参数 ->{}", params);
|
// log.info("执行请求响应：{}", builder.toString());
|
return JSONUtil.parseObj(builder.toString());
|
} catch (IOException e) {
|
e.printStackTrace();
|
} finally {
|
if (connection != null) {
|
connection.disconnect();
|
}
|
}
|
return null;
|
}
|
|
/**
|
* 执行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 tabName
|
* @param connection
|
* @throws SQLException
|
*/
|
private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
|
List<String> columns = getColumns(tabName, connection);
|
// log.info("当前表字段为 ->{}", columns);
|
ArrayList<String> responseColums = new ArrayList<>();
|
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";
|
// log.info("修改字段 ->{}",sql.toString());
|
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 {
|
// System.out.println("jsonObject = " + jsonObject);
|
// 插入数据前 先查询数据是否存在
|
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.append("insert_time, ");
|
String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
|
valueBuilder.append("'").append(time).append("'").append(", ");
|
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.append("insert_time").append(" text null,");
|
sql = new StringBuilder(sql.substring(0, sql.length() - 1));
|
sql.append(");");
|
// log.info("创建表格 -> {}",sql.toString());
|
Statement statement = connection.createStatement();
|
statement.execute(sql.toString());
|
}
|
}
|
