From 24867bdaf1dbdcc855e86ab0284d2836252de768 Mon Sep 17 00:00:00 2001
From: 赵文轩 <1652863494@qq.com>
Date: 星期二, 04 六月 2024 21:05:20 +0800
Subject: [PATCH] 去除查询、每次插入前删除表重建、修改字段类型
---
src/main/java/com/example/utils/DictionaryUtil.java | 56 +++++++++++++++++++++++++++++---------------------------
1 files changed, 29 insertions(+), 27 deletions(-)
diff --git a/src/main/java/com/example/utils/DictionaryUtil.java b/src/main/java/com/example/utils/DictionaryUtil.java
index 1f4399b..c3f4269 100644
--- a/src/main/java/com/example/utils/DictionaryUtil.java
+++ b/src/main/java/com/example/utils/DictionaryUtil.java
@@ -25,9 +25,9 @@
@Slf4j
@Component
public class DictionaryUtil {
-// private static final String CONFIG_PATH = "/Users/chacca/寮�鍙戠浉鍏�/浠ｇ爜/ltkj_peis_sjpt/src/main/resources/config.properties";
+ private static final String CONFIG_PATH = "/Users/chacca/寮�鍙戠浉鍏�/浠ｇ爜/ltkj_peis_sjpt/src/main/resources/config.properties";
// private static final String CONFIG_PATH = "/Users/chacca/寮�鍙戠浉鍏�/浠ｇ爜/ltkj_peis_sjpt/src/main/resources/ltkj.properties";
- private static final String CONFIG_PATH = "D:\\ltkjprojectconf\\config.properties";
+// private static final String CONFIG_PATH = "D:\\ltkjprojectconf\\config.properties";
private static String BASE_API_URL = "";
// 璇锋眰浠ｇ爜鎬婚泦鍚�
private static List<String> methods = new ArrayList<>();
@@ -139,13 +139,13 @@
}
public static void main(String[] args) {
- DictionaryUtil util = new DictionaryUtil();
- try {
- util.exec1();
- } catch (IOException e) {
- e.printStackTrace();
- log.error("寮傚父");
- }
+// DictionaryUtil util = new DictionaryUtil();
+// try {
+// util.exec1();
+// } catch (IOException e) {
+// e.printStackTrace();
+// log.error("寮傚父");
+// }
}
/**
@@ -380,7 +380,7 @@
if (!responseColums.isEmpty()) {
// 闇�瑕佸鍔犲瓧娈� 骞舵彃鍏ユ暟鎹�
for (String colum : responseColums) {
- String sql = "alter table " + tabName + " add column " + colum + " text null";
+ String sql = "alter table " + tabName + " add column " + colum + " VARCHAR(200) null";
// log.info("淇敼瀛楁 ->{}",sql.toString());
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
@@ -402,38 +402,39 @@
*/
private void insertData(String tabName, Connection connection, JSONObject jsonObject) throws SQLException {
// 鎻掑叆鏁版嵁鍓� 鍏堟煡璇㈡暟鎹槸鍚﹀瓨鍦�
- StringBuilder selectSqlBuilder = new StringBuilder();
+// 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 ");
+// selectSqlBuilder.append("select count(1) as count from ").append(tabName).append(" where ");
for (Map.Entry<String, Object> entry : jsonObject.entrySet()) {
- if (entry.getValue().toString().equals("null"))
+ String str = entry.getValue().toString().replaceAll("\\\\", "");
+ if (str.equals("null"))
continue;
insertSqlBuilder.append(entry.getKey()).append(", ");
- valueBuilder.append("'").append(entry.getValue().toString().replaceAll("'","鈥�")).append("', ");
- selectSqlBuilder.append(entry.getKey()).append(" = '").append(entry.getValue().toString().replaceAll("'","鈥�")).append("' and ");
+ valueBuilder.append("'").append(str.replaceAll("'","鈥�")).append("', ");
+// selectSqlBuilder.append(entry.getKey()).append(" = '").append(entry.getValue().toString().replaceAll("'","鈥�")).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());
+// 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();
+// 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)) {
+// if (!"0".equals(string)) {
// log.info("鏁版嵁瀛樺湪涓嶉渶瑕佹彃鍏� {}", jsonObject);
- return;
- }
+// return;
+// }
// 鎻掑叆鏁版嵁
- statement = connection.createStatement();
+ Statement statement = connection.createStatement();
// log.info("鎻掑叆鏁版嵁 sql-> {}", insertSqlBuilder.toString());
statement.execute(insertSqlBuilder.toString());
statement.close();
@@ -469,9 +470,9 @@
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(entry.getKey()).append(" VARCHAR(200) null,");
}
- sql.append("insert_time").append(" text null,");
+ sql.append("insert_time").append(" VARCHAR(100) null,");
sql = new StringBuilder(sql.substring(0, sql.length() - 1));
sql.append(");");
// log.info("鍒涘缓琛ㄦ牸 -> {}",sql.toString());
@@ -489,5 +490,6 @@
String sql = "DROP TABLE IF EXISTS " + tabName;
Statement statement = connection.createStatement();
statement.executeUpdate(sql);
+ statement.close();
}
}
--
Gitblit v1.8.0