将数据库表结构字段同步至缓存字段表字段

6c

2024-10-18 8e4b3f3988dc8948c55fa5aecf206e2e181203f8

将数据库表结构字段同步至缓存字段表字段

 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>

 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;

 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);
        }
    }
}

 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("该sql无法通过baseDao执行，但是能够直接连接到数据库执行");
        System.out.println("========================================================================");
        System.out.println(sql);
        System.out.println("========================================================================");
    }
}

 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();
}