From 49f9bf946cc43ee5a3803da08f6ab30365ce2989 Mon Sep 17 00:00:00 2001
From: zhaowenxuan <chacca165@163.com>
Date: 星期五, 31 五月 2024 18:08:19 +0800
Subject: [PATCH] 创建表和插入数据时增加记录时间
---
src/main/java/com/example/utils/DictionaryUtil.java | 309 ++++++++++++++++++++++++++++++++++++++++++++++----
1 files changed, 280 insertions(+), 29 deletions(-)
diff --git a/src/main/java/com/example/utils/DictionaryUtil.java b/src/main/java/com/example/utils/DictionaryUtil.java
index 363c9f1..cc581e0 100644
--- a/src/main/java/com/example/utils/DictionaryUtil.java
+++ b/src/main/java/com/example/utils/DictionaryUtil.java
@@ -15,7 +15,9 @@
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;
@@ -26,20 +28,225 @@
*/
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 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("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.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() {
@@ -59,12 +266,53 @@
creatTable(resultDataIndex1, tabName, connection);
}
// 瀵规瘮瀛楁 骞舵彃鍏ユ暟鎹�
- operationTable(jsonArray, tabName, connection);
+ operationTable((JSONObject) jsonArray.get(0), tabName, connection);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
+ }
+
+ /**
+ * 鎵цpost璇锋眰
+ *
+ * @param params 璇锋眰鍙傛暟 渚嬪锛� param1=val1¶m2=val2
+ * @return 璇锋眰杩斿洖鐨刯son杞崲鍚庣殑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;
}
/**
@@ -132,7 +380,7 @@
PreparedStatement statement = connection.prepareStatement(tabSql);
statement.setString(1, "test_urlreq");
statement.setString(2, tableName);
- log.info("鍒ゆ柇琛ㄦ槸鍚﹀瓨鍦� sql-> {}", statement.toString());
+// log.info("鍒ゆ柇琛ㄦ槸鍚﹀瓨鍦� sql-> {}", statement.toString());
ResultSet resultSet = statement.executeQuery();
boolean next = resultSet.next();
statement.close();
@@ -142,34 +390,31 @@
/**
* 鎿嶄綔琛�
*
- * @param jsonArray
* @param tabName
* @param connection
* @throws SQLException
*/
- private void operationTable(JSONArray jsonArray, String tabName, Connection connection) throws SQLException {
+ private void operationTable(JSONObject jsonObject, String tabName, Connection connection) throws SQLException {
List<String> columns = getColumns(tabName, connection);
- log.info("褰撳墠琛ㄥ瓧娈典负 ->{}", columns);
+// 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());
+ 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();
}
- 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);
- }
+ insertData(tabName, connection, jsonObject);
+ } else {
+ insertData(tabName, connection, jsonObject);
}
}
@@ -182,6 +427,7 @@
* @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();
@@ -193,11 +439,14 @@
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());
+// log.info("鎻掑叆鍓嶆煡璇� sql ->{}", selectSqlBuilder.toString());
Statement statement = connection.prepareStatement(selectSqlBuilder.toString());
ResultSet resultSet = statement.executeQuery(selectSqlBuilder.toString());
resultSet.next();
@@ -205,12 +454,12 @@
statement.close();
// 濡傛灉涓嶄负0 鍒欒繖鏉℃暟鎹瓨鍦� 涓嶈繘琛屾彃鍏�
if (!"0".equals(string)) {
- log.info("鏁版嵁瀛樺湪涓嶉渶瑕佹彃鍏� {}", jsonObject);
+// log.info("鏁版嵁瀛樺湪涓嶉渶瑕佹彃鍏� {}", jsonObject);
return;
}
// 鎻掑叆鏁版嵁
statement = connection.createStatement();
- log.info("鎻掑叆鏁版嵁 sql-> {}", insertSqlBuilder.toString());
+// log.info("鎻掑叆鏁版嵁 sql-> {}", insertSqlBuilder.toString());
statement.execute(insertSqlBuilder.toString());
statement.close();
}
@@ -247,8 +496,10 @@
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());
}
--
Gitblit v1.8.0