From 8e4b3f3988dc8948c55fa5aecf206e2e181203f8 Mon Sep 17 00:00:00 2001
From: 6c <420680616@qq.com>
Date: 星期五, 18 十月 2024 15:50:50 +0800
Subject: [PATCH] 将数据库表结构字段同步至缓存字段表字段
---
src/main/java/com/product/patch/config/ErrorCode.java | 2
src/main/java/com/product/patch/controller/SyncTableStructureController.java | 48 ++++++++
pom.xml | 4
src/main/java/com/product/patch/service/idel/ISyncTableStructureService.java | 23 +++
src/main/java/com/product/patch/service/SyncTableStructureService.java | 232 ++++++++++++++++++++++++++++++++++++++
5 files changed, 309 insertions(+), 0 deletions(-)
diff --git a/pom.xml b/pom.xml
index 076aac3..e02bb1f 100644
--- a/pom.xml
+++ b/pom.xml
@@ -28,5 +28,9 @@
<groupId>com.lx</groupId>
<artifactId>product-server-util</artifactId>
</dependency>
+ <dependency>
+ <groupId>com.lx</groupId>
+ <artifactId>product-server-tool-table</artifactId>
+ </dependency>
</dependencies>
</project>
diff --git a/src/main/java/com/product/patch/config/ErrorCode.java b/src/main/java/com/product/patch/config/ErrorCode.java
index 596203b..f097226 100644
--- a/src/main/java/com/product/patch/config/ErrorCode.java
+++ b/src/main/java/com/product/patch/config/ErrorCode.java
@@ -29,6 +29,8 @@
EXTRACT_EXTRACT_CONTENT_FAIL("鎻愬彇琛ヤ竵鍐呭澶辫触", ModuleEnum.PATCH.getValue() + "102"),
EXTRACT_UNQUALIFIED("涓嶇鍚堣姹傜殑琛ヤ竵", ModuleEnum.PATCH.getValue() + "103"),
+ SYNC_DB_FIELD_2_CACHE_TABLE("灏嗘暟鎹簱琛ㄧ粨鏋勫瓧娈靛悓姝ヨ嚦缂撳瓨瀛楁琛ㄥ瓧娈靛け璐�", ModuleEnum.PATCH.getValue() + "104"),
+
;
private String text;
diff --git a/src/main/java/com/product/patch/controller/SyncTableStructureController.java b/src/main/java/com/product/patch/controller/SyncTableStructureController.java
new file mode 100644
index 0000000..9526132
--- /dev/null
+++ b/src/main/java/com/product/patch/controller/SyncTableStructureController.java
@@ -0,0 +1,48 @@
+package com.product.patch.controller;
+
+import com.product.core.controller.support.AbstractBaseController;
+import com.product.core.exception.BaseException;
+import com.product.core.spring.context.SpringMVCContextHolder;
+import com.product.module.sys.version.ApiVersion;
+import com.product.patch.config.ErrorCode;
+import com.product.patch.service.SyncTableStructureService;
+import com.product.patch.service.idel.ISyncTableStructureService;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+import javax.servlet.http.HttpServletRequest;
+
+/**
+ * 瀹炵幇鍔熻兘锛�
+ *
+ * @author 浣滆�匸澶滀付鍏塢
+ * @version 1.0.00 2024-10-17 15:57
+ */
+@RestController
+@RequestMapping("/api/patch/sync")
+public class SyncTableStructureController extends AbstractBaseController {
+ @Autowired
+ private SyncTableStructureService syncTableStructureService;
+
+ /**
+ * 灏嗘暟鎹簱琛ㄧ粨鏋勫瓧娈靛悓姝ヨ嚦缂撳瓨瀛楁琛ㄥ瓧娈�
+ * @param request
+ * @return
+ */
+ @RequestMapping(value = "/syncDBField2CacheTable/{version}")
+ @ApiVersion(1)
+ public String syncDBField2CacheTable(HttpServletRequest request) {
+ try {
+ ISyncTableStructureService service = (ISyncTableStructureService) getProxyInstance(syncTableStructureService);
+ service.execute();
+ return OK();
+ } catch (BaseException e) {
+ return error(e);
+ } catch (Exception e) {
+ System.err.println(e);
+ SpringMVCContextHolder.getSystemLogger().error(e);
+ return error(ErrorCode.SYNC_DB_FIELD_2_CACHE_TABLE);
+ }
+ }
+}
diff --git a/src/main/java/com/product/patch/service/SyncTableStructureService.java b/src/main/java/com/product/patch/service/SyncTableStructureService.java
new file mode 100644
index 0000000..7901297
--- /dev/null
+++ b/src/main/java/com/product/patch/service/SyncTableStructureService.java
@@ -0,0 +1,232 @@
+package com.product.patch.service;
+
+import com.google.common.collect.Lists;
+import com.product.common.lang.StringUtils;
+import com.product.core.dao.BaseDao;
+import com.product.core.entity.DataTableEntity;
+import com.product.core.entity.FieldSetEntity;
+import com.product.core.service.support.AbstractBaseService;
+import com.product.core.transfer.Transactional;
+import com.product.patch.service.idel.ISyncTableStructureService;
+import com.product.tool.table.enums.FieldType;
+import com.product.util.BaseUtil;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.stereotype.Service;
+
+import java.util.Arrays;
+import java.util.List;
+import java.util.stream.Collectors;
+
+/**
+ * 瀹炵幇鍔熻兘锛�
+ *
+ * @author 浣滆�匸澶滀付鍏塢
+ * @version 1.0.00 2024-10-16 16:39
+ */
+@Service
+public class SyncTableStructureService extends AbstractBaseService implements ISyncTableStructureService {
+ @Autowired
+ private BaseDao baseDao;
+
+ /**
+ * 鏌ヨ鑾峰彇鍑烘暟鎹簱琛ㄧ粨鏋勫瓧娈靛拰缂撳瓨瀛楁琛ㄥ瓧娈靛瓨鍦ㄥ樊寮傜殑琛�
+ * @return
+ */
+ @Override
+ public List<String> listDiffTable() {
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\nWITH t1 AS (");
+ sql.append("\n SELECT t.table_name,f.field_name");
+ sql.append("\n FROM product_sys_datamodel_field f");
+ sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
+ sql.append("\n),t2 AS (");
+ sql.append("\n SELECT table_name,column_name field_name");
+ sql.append("\n FROM information_schema.`COLUMNS`");
+ sql.append("\n WHERE table_schema='product_db_v3.0.0'");
+ sql.append("\n)");
+ sql.append("\nSELECT DISTINCT table_name");
+ sql.append("\nFROM (");
+ sql.append("\n SELECT t1.table_name table_name,t1.field_name field_name FROM t1 LEFT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t2.field_name IS NULL");
+ sql.append("\n UNION ALL ");
+ sql.append("\n SELECT t2.table_name table_name,t2.field_name field_name FROM t1 RIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name WHERE t1.field_name IS NULL");
+ sql.append("\n) t");
+ sql.append("\nORDER BY 1");
+ DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
+ List<String> list = Lists.newArrayList();
+ for (int i = 0; i < dte.getRows(); i++) {
+ FieldSetEntity fse = dte.getFieldSetEntity(i);
+ list.add(fse.getString("table_name"));
+ }
+ System.out.println(list);
+ return list;
+ }
+
+ /**
+ * 灏嗘暟鎹簱琛ㄧ粨鏋勫瓧娈靛悓姝ヨ嚦缂撳瓨瀛楁琛ㄥ瓧娈�
+ */
+ @Override
+ @Transactional
+ public void execute() {
+ // 灏嗗瓧娈电紦瀛樿〃涓浣欑殑瀛楁娓呯悊锛屽彧鑳界洿鎺ヨ繛鎺ユ暟鎹簱鎵ц
+ clearTableEx();
+
+ // 灏嗘暟鎹簱涓〃缁撴瀯瀛楁鍚屾鍒板瓧娈电紦瀛樿〃涓�
+ syncDB2TableForTable();
+ syncDB2TableForField();
+ }
+
+ /**
+ * 灏嗘暟鎹簱涓〃缁撴瀯瀛楁鍚屾鍒板瓧娈电紦瀛樿〃涓�-琛�
+ */
+ public void syncDB2TableForTable() {
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\nWITH t1 AS (");
+ sql.append("\n SELECT uuid,table_name,table_description,table_type FROM product_sys_datamodel_table");
+ sql.append("\n),t2 AS (");
+ sql.append("\n SELECT TABLE_NAME table_name,TABLE_COMMENT table_description,CASE WHEN TABLE_TYPE='BASE TABLE' THEN 1 ELSE 2 END table_type");
+ sql.append("\n FROM information_schema.`TABLES`");
+ sql.append("\n WHERE table_schema='product_db_v3.0.0'");
+ sql.append("\n)");
+ sql.append("\nSELECT t2.*");
+ sql.append("\nFROM t1");
+ sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name");
+ sql.append("\nWHERE t1.table_name IS NULL");
+ DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
+ for (int i = 0; i < dte.getRows(); i++) {
+ FieldSetEntity fse = dte.getFieldSetEntity(i);
+ FieldSetEntity saveFse = new FieldSetEntity();
+ saveFse.setTableName("product_sys_datamodel_table");
+ saveFse.setValue("table_name", fse.getString("table_name"));
+ saveFse.setValue("table_description", fse.getString("table_description"));
+ saveFse.setValue("table_type", fse.getString("table_type"));
+ saveFse.setValue("sequence", 1);
+ System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
+ baseDao.add(saveFse);
+ }
+ }
+
+ /**
+ * 灏嗘暟鎹簱涓〃缁撴瀯瀛楁鍚屾鍒板瓧娈电紦瀛樿〃涓�-瀛楁
+ */
+ public void syncDB2TableForField() {
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\nWITH t1 AS (");
+ sql.append("\n SELECT t.table_name,f.table_uuid,f.field_name,field_type type,field_length,field_unit,field_description");
+ sql.append("\n FROM product_sys_datamodel_field f");
+ sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
+ sql.append("\n),t2 AS (");
+ sql.append("\n SELECT table_name,column_name field_name,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
+ sql.append("\n FROM information_schema.`COLUMNS`");
+ sql.append("\n WHERE table_schema='product_db_v3.0.0'");
+ sql.append("\n)");
+ sql.append("\nSELECT t.uuid table_uuid,t2.*");
+ sql.append("\nFROM t1 ");
+ sql.append("\nRIGHT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
+ sql.append("\nLEFT JOIN product_sys_datamodel_table t ON t2.table_name=t.table_name");
+ sql.append("\nWHERE t1.field_name IS NULL");
+ sql.append("\nAND t2.table_name NOT LIKE '%v'");
+ sql.append("\nAND t2.table_name NOT LIKE 'qrtz%'");
+ sql.append("\nORDER BY 1");
+ DataTableEntity dte = baseDao.listTable(sql.toString(), new Object[]{});
+ for (int i = 0; i < dte.getRows(); i++) {
+ FieldSetEntity fse = dte.getFieldSetEntity(i);
+ FieldSetEntity saveFse = new FieldSetEntity();
+ saveFse.setTableName("product_sys_datamodel_field");
+ saveFse.setValue("table_uuid", fse.getString("table_uuid"));
+ saveFse.setValue("field_name", fse.getString("field_name"));
+ saveFse.setValue("field_type", getDictType(fse.getString("field_name"), fse.getString("type")));
+ saveFse.setValue("field_show_name", StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
+ saveFse.setValue("field_description", StringUtils.isEmpty(fse.getString("field_description")) ? fse.getString("field_name") : fse.getString("field_description"));
+ saveFse.setValue("field_length", StringUtils.isEmpty(fse.getString("field_length")) ? 0 : fse.getString("field_length"));
+ saveFse.setValue("field_unit", StringUtils.isEmpty(fse.getString("field_unit")) ? 0 : fse.getString("field_unit"));
+ saveFse.setValue("is_required", 0);
+ saveFse.setValue("is_unique", 0);
+ saveFse.setValue("is_filter", 0);
+ saveFse.setValue("field_sequence", 0);
+ System.out.println(BaseUtil.fieldSetEntityToJson(saveFse));
+ baseDao.add(saveFse);
+ }
+ }
+
+ /**
+ * 鏍规嵁鏁版嵁搴撳瓨鍌ㄧ殑绫诲瀷鑾峰彇瀛楁缂撳瓨琛ㄧ殑瀛樺偍绫诲瀷
+ * @param dbType
+ * @return
+ */
+ private String getDictType(String fieldName, String dbType) {
+ fieldName = fieldName.trim();
+ List<FieldType> baseList = Arrays.asList(FieldType.values());
+ List<FieldType> aimList = baseList.stream().filter(o -> o.getDatabaseType().equals(dbType)).collect(Collectors.toList());
+ String result = "";
+ if (aimList.size() > 1) {
+ if ("varchar".equals(dbType)) {
+ if (Arrays.asList(
+ "district_sqe","asset_code","material_name","material_sn","supplier_uuid","uuid","version","material_uuid","use_dept",
+ "remark","name","standard_value","standard_value_1","inspect_item","inspect_remark","device_information","device_sn",
+ "fault_type","repair_applicant","origin_plan_uuid","maintenance_content","maintenance_item","id_number","id_type",
+ "sub_device_uuid","unit_uuid","high_threshold","department_uuid","manager_opinion","staff_uuid","personnel_opinion",
+ "must_read_num","file_org_level_uuid","light_mode_icon","dark_mode_icon"
+ ).contains(fieldName)) {
+ result = "string";
+ } else if("org_level_uuid".equals(fieldName)) {
+ result = "orgUuid";
+ } else if (fieldName.endsWith("img") || fieldName.endsWith("photo") || fieldName.endsWith("attachment")) {
+ result = "file";
+ } else if (Arrays.asList("parent_uuid","parent_key").contains(fieldName)) {
+ result = "parentuuid";
+ } else if ("user_id".equals(fieldName)) {
+ result = "userid";
+ }
+ }
+ if ("bigint".equals(dbType)) {
+ if ("created_by".equals(fieldName)) {
+ result = "userid";
+ } else if ("id".equals(fieldName)) {
+ result = "pk";
+ } else {
+ result = "int";
+ }
+ }
+ if ("int".equals(dbType)) {
+ if (!"flow_flag".equals(fieldName)) {
+ result = "int";
+ }
+ }
+ System.out.println("result = " + result);;
+ } else if (aimList.size() == 1) {
+ result = aimList.get(0).getDictValue();
+ }
+ if (StringUtils.isEmpty(result)) {
+ throw new RuntimeException(String.format("test bug...fieldName:%s,dbType:%s", fieldName, dbType));
+ }
+ return result;
+ }
+
+ /**
+ * 灏嗗瓧娈电紦瀛樿〃涓浣欑殑瀛楁娓呯悊
+ */
+ public void clearTableEx() {
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\nWITH t1 AS (");
+ sql.append("\n SELECT t.table_name,f.table_uuid,f.field_name,f.uuid field_uuid,field_type type,field_length,field_unit,field_description");
+ sql.append("\n FROM product_sys_datamodel_field f");
+ sql.append("\n INNER JOIN product_sys_datamodel_table t ON t.uuid=f.table_uuid");
+ sql.append("\n),t2 AS (");
+ sql.append("\n SELECT table_name,column_name field_name,null,DATA_TYPE type,case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN CHARACTER_MAXIMUM_LENGTH ELSE NUMERIC_PRECISION END field_length,NUMERIC_SCALE field_unit,COLUMN_COMMENT field_description");
+ sql.append("\n FROM information_schema.`COLUMNS`");
+ sql.append("\n WHERE table_schema='product_db_v3.0.0'");
+ sql.append("\n)");
+ sql.append("\nDELETE FROM product_sys_datamodel_field WHERE uuid IN (");
+ sql.append("\n SELECT t1.field_uuid");
+ sql.append("\n FROM t1 ");
+ sql.append("\n LEFT JOIN t2 ON t1.table_name=t2.table_name AND t1.field_name=t2.field_name ");
+ sql.append("\n WHERE t2.field_name IS NULL");
+ sql.append("\n AND t1.table_name NOT LIKE '%v'");
+ sql.append("\n AND t1.table_name NOT LIKE 'qrtz%'");
+ sql.append("\n)");
+ System.out.println("璇ql鏃犳硶閫氳繃baseDao鎵ц锛屼絾鏄兘澶熺洿鎺ヨ繛鎺ュ埌鏁版嵁搴撴墽琛�");
+ System.out.println("========================================================================");
+ System.out.println(sql);
+ System.out.println("========================================================================");
+ }
+}
diff --git a/src/main/java/com/product/patch/service/idel/ISyncTableStructureService.java b/src/main/java/com/product/patch/service/idel/ISyncTableStructureService.java
new file mode 100644
index 0000000..4654a42
--- /dev/null
+++ b/src/main/java/com/product/patch/service/idel/ISyncTableStructureService.java
@@ -0,0 +1,23 @@
+package com.product.patch.service.idel;
+
+import java.util.List;
+
+/**
+ * 瀹炵幇鍔熻兘锛�
+ *
+ * @author 浣滆�匸澶滀付鍏塢
+ * @version 1.0.00 2024-10-16 16:39
+ */
+public interface ISyncTableStructureService {
+
+ /**
+ * 鏌ヨ鑾峰彇鍑烘暟鎹簱琛ㄧ粨鏋勫瓧娈靛拰缂撳瓨瀛楁琛ㄥ瓧娈靛瓨鍦ㄥ樊寮傜殑琛�
+ * @return
+ */
+ List<String> listDiffTable();
+
+ /**
+ * 灏嗘暟鎹簱琛ㄧ粨鏋勫瓧娈靛悓姝ヨ嚦缂撳瓨瀛楁琛ㄥ瓧娈�
+ */
+ void execute();
+}
--
Gitblit v1.9.2