From 7e5d9856c8f1b6af4fd55706bb974ac469417a74 Mon Sep 17 00:00:00 2001
From: zhaowenxuan <chacca165@163.com>
Date: 星期五, 24 五月 2024 10:11:53 +0800
Subject: [PATCH] HIS读接口创表工具类
---
ltkj-common/src/main/java/com/ltkj/common/utils/DictionaryUtil.java | 255 +++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 255 insertions(+), 0 deletions(-)
diff --git a/ltkj-common/src/main/java/com/ltkj/common/utils/DictionaryUtil.java b/ltkj-common/src/main/java/com/ltkj/common/utils/DictionaryUtil.java
new file mode 100644
index 0000000..525986e
--- /dev/null
+++ b/ltkj-common/src/main/java/com/ltkj/common/utils/DictionaryUtil.java
@@ -0,0 +1,255 @@
+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());
+ }
+}
--
Gitblit v1.8.0