mdc两网数据交互

Lius

2025-06-24 aaaeee46bf6d408e68cfddebea146bf382977ac9

mdc两网数据交互

 lxzn-module-dnc/src/main/java/org/jeecg/modules/dnc/listener/FileListener.java

@@ -24,7 +24,7 @@

    @Override
    public void onStart(FileAlterationObserver observer) {
        log.info("开始监听目录: {}", observer.getDirectory().getAbsolutePath());
//        log.info("开始监听目录: {}", observer.getDirectory().getAbsolutePath());
    }

    @Override
@@ -66,6 +66,6 @@

    @Override
    public void onStop(FileAlterationObserver observer) {
        log.info("结束监听目录: {}", observer.getDirectory().getAbsolutePath());
//        log.info("结束监听目录: {}", observer.getDirectory().getAbsolutePath());
    }
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/entity/LogTable.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,39 @@
package org.jeecg.modules.mdc.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * @author Lius
 * @date 2024/12/18 14:19
 */
@Data
@TableName("log_table")
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value = "log_table对象", description = "sql记录表")
public class LogTable implements Serializable {

    private static final long serialVersionUID = 953864495681756550L;

    /**
     * æ“ä½œç±»åž‹ï¼ˆINSERT / UPDATE / DELETE）
     */
    private String action;

    /**
     * åˆ›å»ºæ—¶é—´
     */
    private Date createTime;

    /**
     * sql记录
     */
    private String sqlLog;
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/job/ExportToXmlJob.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,63 @@
package org.jeecg.modules.mdc.job;

import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.util.DateUtils;
import org.jeecg.modules.mdc.service.ILogTableService;
import org.jeecg.modules.mdc.util.ThrowableUtil;
import org.jeecg.modules.quartz.entity.QuartzJob;
import org.jeecg.modules.quartz.entity.SysQuartzLog;
import org.jeecg.modules.quartz.service.IQuartzJobService;
import org.jeecg.modules.quartz.service.ISysQuartzLogService;
import org.jeecg.modules.system.service.ISysAnnouncementService;
import org.quartz.*;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * å¯¼å‡ºsql记录到xml文件任务
 * @author Lius
 * @date 2024/12/18 14:53
 */
@PersistJobDataAfterExecution
@DisallowConcurrentExecution
@Slf4j
public class ExportToXmlJob implements Job {

    @Resource
    private ISysQuartzLogService sysQuartzLogService;

    @Resource
    private IQuartzJobService quartzJobService;

    @Resource
    private ISysAnnouncementService sysAnnouncementService;

    @Resource
    private ILogTableService logTableService;

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {
        SysQuartzLog quartzLog = new SysQuartzLog();
        quartzLog.setCreateTime(new Date());
        List<QuartzJob> byJobClassName = this.quartzJobService.findByJobClassName(this.getClass().getName());
        if (byJobClassName != null && !byJobClassName.isEmpty()) {
            quartzLog.setJobId(byJobClassName.get(0).getId());
        }
        log.info("定时导出同步数据表sql任务 ExportToXmlJob start!  æ—¶é—´:" + DateUtils.now());
        long startTime = System.currentTimeMillis();
        try {
            logTableService.exportDataToXml();
            quartzLog.setIsSuccess(0);
        } catch (Exception e) {
            quartzLog.setIsSuccess(-1);
            quartzLog.setExceptionDetail(ThrowableUtil.getStackTrace(e));
            // å‘送消息通知
            sysAnnouncementService.jobSendMessage("定时导出同步数据表sql任务", quartzLog.getExceptionDetail());
        }
        long endTime = System.currentTimeMillis();
        quartzLog.setExecutionTime(Integer.parseInt(String.valueOf(endTime - startTime)));
        sysQuartzLogService.save(quartzLog);
    }
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/LogTableMapper.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,11 @@
package org.jeecg.modules.mdc.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.jeecg.modules.mdc.entity.LogTable;

/**
 * @author Lius
 * @date 2024/12/18 14:24
 */
public interface LogTableMapper extends BaseMapper<LogTable>  {
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/LogTableMapper.xml

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,5 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.mdc.mapper.LogTableMapper">

</mapper>

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/ILogTableService.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,17 @@
package org.jeecg.modules.mdc.service;

import com.baomidou.mybatisplus.extension.service.IService;
import org.jeecg.modules.mdc.entity.LogTable;

/**
 * @author Lius
 * @date 2024/12/18 14:29
 */
public interface ILogTableService extends IService<LogTable> {

    /**
     * å¯¼å‡ºåŒæ­¥æ•°æ®è¡¨sql到xml文件上传到网闸服务器
     */
    void exportDataToXml();

}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcPassLogService.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,19 @@
package org.jeecg.modules.mdc.service;

import com.baomidou.mybatisplus.extension.service.IService;
import org.jeecg.modules.system.entity.MdcPassLog;

/**
 * @author Lius
 * @date 2024/12/23 15:00
 */
public interface IMdcPassLogService extends IService<MdcPassLog> {

    /**
     * æŸ¥è¯¢ä»Šæ—¥æœ€æ–°ä¸€æ¡ä¼ è¾“日志
     *
     * @param date
     * @return
     */
    MdcPassLog selectTodayLast(String date);
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/LogTableServiceImpl.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,96 @@
package org.jeecg.modules.mdc.service.impl;

import cn.hutool.core.io.FileUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.modules.mdc.entity.LogTable;
import org.jeecg.modules.mdc.mapper.LogTableMapper;
import org.jeecg.modules.mdc.service.ILogTableService;
import org.jeecg.modules.mdc.util.DateUtils;
import org.jeecg.modules.system.entity.MdcPassLog;
import org.jeecg.modules.system.service.IMdcPassLogService;
import org.jeecg.modules.system.util.FileUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.File;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author Lius
 * @date 2024/12/18 14:29
 */
@Service
@Slf4j
public class LogTableServiceImpl extends ServiceImpl<LogTableMapper, LogTable> implements ILogTableService {

    @Value("${fileService.localFilePath}")
    private String localFilePath;

    @Resource
    private IMdcPassLogService mdcPassLogService;

    private static final String TYPE = "18";

    /**
     * å¯¼å‡ºåŒæ­¥æ•°æ®è¡¨sql到xml文件
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void exportDataToXml() {
        // step.1 æŸ¥è¯¢æœ€æ–°æ•°æ® æ—¶é—´æ­£åºæŽ’列
        List<LogTable> logTables = this.baseMapper.selectList(new LambdaQueryWrapper<LogTable>().orderByAsc(LogTable::getCreateTime));
        if (logTables != null && !logTables.isEmpty()) {

            // step.2 å¤„理数据
            List<String> sqlList = logTables.stream().map(LogTable::getSqlLog).collect(Collectors.toList());

            // step.3 å†™å…¥æ–‡ä»¶
            // step.3.1 æ–‡ä»¶å‘½å
            String today = DateUtils.format(DateUtils.getNow(), DateUtils.STRDATE);
            MdcPassLog mdcPassLogLast = mdcPassLogService.selectTodayLast(today);
            int sequenceNumber = 1;
            if (mdcPassLogLast != null) {
                sequenceNumber = mdcPassLogLast.getSequenceNumber() + 1;
            }

            // æ–‡ä»¶è·¯å¾„
            String locFilePath = localFilePath + TYPE + today + String.format("%06d", sequenceNumber) + ".xml";
            String listSql = String.join("\n", sqlList);
            try {
                FileUtils.fileWriterSql(locFilePath, listSql);
            } catch (Exception e) {
                throw new JeecgBootException("数据写入文件失败！");
            }

            File[] files = FileUtil.ls(localFilePath);
            for (File file : files) {
                if (file.isFile()) {
                    //顺序号
                    String fileName = file.getName();
                    String sequenceOrder = fileName.substring(fileName.length() - 10, fileName.length() - 4);
                    String dayTime = fileName.substring(fileName.length() - 18, fileName.length() - 10);
                    int sequenceNum = Integer.parseInt(sequenceOrder);
                    // æ’入传输日志
                    MdcPassLog mdcPassLog = new MdcPassLog();
                    mdcPassLog.setPassLogFileName(file.getAbsolutePath());
                    mdcPassLog.setPassName(fileName);
                    mdcPassLog.setDayTime(dayTime);
                    mdcPassLog.setSequenceNumber(sequenceNum);
                    mdcPassLog.setSequenceOrder(sequenceOrder);
                    mdcPassLogService.save(mdcPassLog);
                }
            }
            // step.5 åˆ é™¤å·²ç»å†™å…¥æ–‡ä»¶å¹¶å‘送至网闸的数据
            // step.5.1 åˆ é™¤æ•°æ®åº“数据
            this.baseMapper.delete(new LambdaQueryWrapper<LogTable>().le(LogTable::getCreateTime, logTables.get(logTables.size() - 1).getCreateTime()));

        }
    }

}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/entity/MdcPassLog.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,58 @@
package org.jeecg.modules.system.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

import java.io.Serializable;
import java.util.Date;

/**
 * @author clown
 * * @date 2023/12/12
 */
@Data
@TableName("mdc_pass_log")
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value = "mdc_pass_log对象", description = "发送文件日志表")
public class MdcPassLog implements Serializable {

    private static final long serialVersionUID = -9138730240370211497L;

    @TableId(type = IdType.ASSIGN_ID)
    private String id;
    /**
     * åç§°
     */
    private String passName;
    /**
     * åºå·
     */
    private String sequenceOrder;
    /**
     * æ—¶é—´
     */
    private String dayTime;
    /**
     * ç±»åž‹
     */
    private String passType;
    /**
     * æ–‡ä»¶åç§°
     */
    private String passLogFileName;
    /**
     * é¡ºåºå·
     */
    private Integer sequenceNumber;
    /**
     * åˆ›å»ºæ—¶é—´
     */
    private Date createTime;

}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/mapper/MdcPassLogMapper.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,20 @@
package org.jeecg.modules.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.jeecg.modules.system.entity.MdcPassLog;

/**
 * @author Lius
 * @date 2024/12/23 14:59
 */
public interface MdcPassLogMapper extends BaseMapper<MdcPassLog> {

    /**
     * æŸ¥è¯¢ä»Šæ—¥æœ€æ–°ä¸€æ¡ä¼ è¾“日志
     *
     * @param date
     * @return
     */
    MdcPassLog selectTodayLast(@Param("date") String date);
}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/mapper/xml/MdcPassLogMapper.xml

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,8 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.jeecg.modules.system.mapper.MdcPassLogMapper">

    <select id="selectTodayLast" resultType="org.jeecg.modules.system.entity.MdcPassLog">
        SELECT TOP 1 * FROM mdc_pass_log WHERE day_time = #{date} ORDER BY create_time DESC
    </select>
</mapper>

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/service/IMdcPassLogService.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,19 @@
package org.jeecg.modules.system.service;

import com.baomidou.mybatisplus.extension.service.IService;
import org.jeecg.modules.system.entity.MdcPassLog;

/**
 * @author Lius
 * @date 2024/12/23 15:00
 */
public interface IMdcPassLogService extends IService<MdcPassLog> {

    /**
     * æŸ¥è¯¢ä»Šæ—¥æœ€æ–°ä¸€æ¡ä¼ è¾“日志
     *
     * @param date
     * @return
     */
    MdcPassLog selectTodayLast(String date);
}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/service/impl/MdcPassLogServiceImpl.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,26 @@
package org.jeecg.modules.system.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.jeecg.modules.system.entity.MdcPassLog;
import org.jeecg.modules.system.mapper.MdcPassLogMapper;
import org.jeecg.modules.system.service.IMdcPassLogService;
import org.springframework.stereotype.Service;

/**
 * @author Lius
 * @date 2024/12/23 15:00
 */
@Service
public class MdcPassLogServiceImpl extends ServiceImpl<MdcPassLogMapper, MdcPassLog> implements IMdcPassLogService {

    /**
     * æŸ¥è¯¢ä»Šæ—¥æœ€æ–°ä¸€æ¡ä¼ è¾“日志
     *
     * @param date
     * @return
     */
    @Override
    public MdcPassLog selectTodayLast(String date) {
        return this.baseMapper.selectTodayLast(date);
    }
}

 lxzn-module-system/lxzn-system-biz/src/main/java/org/jeecg/modules/system/util/FileUtils.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,194 @@
package org.jeecg.modules.system.util;

import cn.hutool.core.date.DateUtil;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.math.BigDecimal;

/**
 * Created by YangBin on 2017/9/15.
 */
public class FileUtils {
    private static final long MAX_FILE_SIZE = 10 * 1024 * 1024;

    /**
     * èŽ·å–服务器临时路径
     *
     * @param request
     * @return
     */
    public static String serverTempPath(HttpServletRequest request) {
        String fsep = System.getProperty("file.separator");
        String path = request.getSession().getServletContext().getRealPath("");
        if (path.lastIndexOf(fsep) == path.length() - 1) {
            path += "upload" + fsep + "voucher" + fsep;
        } else {
            path += fsep + "upload" + fsep + "voucher" + fsep;
        }
        return path;
    }

    public static String serverTempPathIntactReg(HttpServletRequest request) {
        String fsep = System.getProperty("file.separator");
        String path = request.getSession().getServletContext().getRealPath("");
        if (path.lastIndexOf(fsep) == path.length() - 1) {
            path += "upload" + fsep + "log" + fsep + "error" + fsep;
        } else {
            path += fsep + "upload" + fsep + "log" + fsep + "error" + fsep;
        }
        return path;
    }

    public static String serverTempPathProcess(HttpServletRequest request) {
        String fsep = System.getProperty("file.separator");
        String path = request.getSession().getServletContext().getRealPath("");
        if (path.lastIndexOf(fsep) == path.length() - 1) {
            path += "upload" + fsep + "process" + fsep;
        } else {
            path += fsep + "upload" + fsep + "process" + fsep;
        }
        return path;
    }

    public static String changeFileFormatKb(String flow) {
        BigDecimal flows = new BigDecimal(flow);
        if (flows.compareTo(new BigDecimal(0)) > 0 && flows.compareTo(new BigDecimal(1024)) < 0) {//小于1M
            return flows.toString() + "B";
        } else if (flows.compareTo(new BigDecimal(1024)) >= 0) {
            BigDecimal result = flows.divide(new BigDecimal(1024), 2, BigDecimal.ROUND_HALF_UP);
            return result.toString() + "KB";
        } else {
            return "0";
        }
    }


    /**
     * å¾—到项目根目录下的绝对路径（磁盘的物理路径）
     *
     * @param request
     * @param newPath
     * @return
     */
    public static String getFilePath(HttpServletRequest request, String newPath) {
        String fsep = System.getProperty("file.separator");
        String path = request.getSession().getServletContext().getRealPath("/upload");
        path += fsep + newPath;
        return path;
    }

    /**
     * å¾—到项目根目录下的相对路径 ï¼ˆç›¸å¯¹äºŽé¡¹ç›®ä¸ºæ ¹è·¯å¾„）
     *
     * @param newPath
     * @return
     */
    public static String getRelativePath(String newPath) {
        return "/upload/" + newPath;
    }


    /**
     * æ–¹æ³•ä¸€:使用 FileWriter å†™æ–‡ä»¶
     *
     * @param filepath æ–‡ä»¶ç›®å½•
     * @param content  å¾…写入内容
     * @throws IOException
     */
    public static void fileWriterSql(String filepath, String content) throws IOException {
        OutputStreamWriter outputStreamWriter = null;
        try {
            File file = new File(filepath);
            if (!file.exists()) {
                file.createNewFile();
            }
            FileOutputStream outputStream = new FileOutputStream(file);
            if (outputStream != null) {
                outputStreamWriter = new OutputStreamWriter(outputStream, "UTF-8");
                outputStreamWriter.write(content);
                outputStreamWriter.flush();
            }
        } catch (IOException e) {
            e.getMessage();
        } finally {
            try {
                if (outputStreamWriter != null) {
                    outputStreamWriter.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        /*
        File file=new File(filepath);
        Writer writer = new OutputStreamWriter(new FileOutputStream(file.getAbsoluteFile()), "UTF-8");
        writer.append(content);
         */
    }

    /**
     * æ–‡ä»¶å¤§å°æ™ºèƒ½è½¬æ¢
     * ä¼šå°†æ–‡ä»¶å¤§å°è½¬æ¢ä¸ºæœ€å¤§æ»¡è¶³å•ä½
     *
     * @param size（文件大小，单位为B）
     * @return æ–‡ä»¶å¤§å°
     */
    public static String formatFileSize(Long size) {
        String sizeName = null;
        if (1024 * 1024 > size && size >= 1024) {
            sizeName = String.format("%.2f", size.doubleValue() / 1024) + "KB";
        } else if (1024 * 1024 * 1024 > size && size >= 1024 * 1024) {
            sizeName = String.format("%.2f", size.doubleValue() / (1024 * 1024)) + "MB";
        } else if (size >= 1024 * 1024 * 1024) {
            sizeName = String.format("%.2f", size.doubleValue() / (1024 * 1024 * 1024)) + "GB";
        } else {
            sizeName = size.toString() + "B";
        }
        return sizeName;
    }

    /**
     * ä¸Šä¼ æ–‡ä»¶å·¥å…·ç±»
     *
     * @param userPic
     * @param path
     * @param fileName
     * @return
     */
    public static boolean uploadFile(MultipartFile userPic, String path, String fileName) {
        Long fileSize = userPic.getSize();
        if (fileSize > MAX_FILE_SIZE) {
            return false;
        }
        File targetFile = new File(path, fileName);
        if (!targetFile.exists()) {
            targetFile.mkdirs();
        }
        try {
            userPic.transferTo(targetFile);
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**
     * é‡å‘½åæ–‡ä»¶å åŠ å…¥æ—¶é—´æˆ³
     *
     * @param fileName
     * @return
     */
    public static String newFileName(String fileName) {
        String suffix = fileName.substring(fileName.lastIndexOf('.'));
        String dateStr = "_" + DateUtil.now();
        String nameFile = fileName.substring(0, fileName.indexOf("."));
        //新文件名
        return nameFile + dateStr + suffix;
    }
}

 lxzn-module-system/lxzn-system-start/src/main/java/org/jeecg/codegenerate/CreateTrigger.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,199 @@
package org.jeecg.codegenerate;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Lius
 * @CreateTime: 2025-06-23
 * @Description: SQL Server è§¦å‘器生成工具
 */
public class CreateTrigger {

    // æ•°æ®åº“连接信息
    private static final String JDBC_URL = "jdbc:sqlserver://192.168.1.118:1433;databaseName=LXZN_TEST_HANGYU_GK";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "123";

    public static void main(String[] args) {
        String schemaName = "dbo"; // SQL Server é»˜è®¤æž¶æž„
        String logTableName = "log_table"; // æ—¥å¿—表名

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // èŽ·å–指定架构下的所有表
            List<String> tables = getTables(connection, schemaName);

            // ç”Ÿæˆè§¦å‘器 SQL å¹¶ä¿å­˜åˆ°æ–‡ä»¶
            generateTriggersForTables(connection, schemaName, logTableName, tables);

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * èŽ·å–指定架构下的所有表
     */
    private static List<String> getTables(Connection connection, String schemaName) throws SQLException {
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();

        try (ResultSet resultSet = metaData.getTables(null, schemaName, null, new String[]{"TABLE"})) {
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                tables.add(tableName);
            }
        }

        return tables;
    }

    /**
     * èŽ·å–表的字段列表
     */
    private static List<String> getTableColumns(Connection connection, String schemaName, String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();

        try (ResultSet resultSet = metaData.getColumns(null, schemaName, tableName, null)) {
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                columns.add(columnName);
            }
        }

        return columns;
    }

    /**
     * èŽ·å–表的主键列
     */
    private static String getPrimaryKeyColumn(Connection connection, String schemaName, String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet resultSet = metaData.getPrimaryKeys(null, schemaName, tableName)) {
            if (resultSet.next()) {
                return resultSet.getString("COLUMN_NAME");
            }
        }
        return "id"; // é»˜è®¤ä½¿ç”¨id作为主键，如果没有找到主键
    }

    /**
     * ä¸ºæ‰€æœ‰è¡¨ç”Ÿæˆè§¦å‘器
     */
    private static void generateTriggersForTables(Connection connection, String schemaName, String logTableName, List<String> tables) throws SQLException, IOException {
        try (FileWriter writer = new FileWriter("triggers.sql")) {
            for (String tableName : tables) {
                // èŽ·å–表结构信息
                List<String> columns = getTableColumns(connection, schemaName, tableName);
                String primaryKey = getPrimaryKeyColumn(connection, schemaName, tableName);

                // ç”Ÿæˆè§¦å‘器 SQL
                String triggerSQL = generateTriggerSQL(schemaName, tableName, logTableName, columns, primaryKey);

                // å°†ç”Ÿæˆçš„触发器 SQL å†™å…¥æ–‡ä»¶
                writer.write("-- è§¦å‘器表: " + tableName + "\n");
                writer.write(triggerSQL);
                writer.write("\n\n");

                // å¦‚果需要，可以直接在数据库中创建触发器
                // createTrigger(connection, triggerSQL);
            }
            System.out.println("成功生成触发器并保存到Triggers.sql中!");
        }
    }

    /**
     * ç”Ÿæˆ SQL Server è§¦å‘器 SQL
     */
    private static String generateTriggerSQL(String schemaName, String tableName, String logTableName, List<String> columns, String primaryKey) {
        StringBuilder triggerSQL = new StringBuilder();

        // è§¦å‘器头部
        triggerSQL.append("CREATE TRIGGER [")
                .append(tableName)
                .append("_trigger]\n")
                .append("ON [")
                .append(schemaName)
                .append("].[").append(tableName).append("]\n")
                .append("AFTER INSERT, UPDATE, DELETE\n")
                .append("AS\n")
                .append("BEGIN\n")
                .append("    SET NOCOUNT ON;\n\n");

        // æ’入操作
        triggerSQL.append("    -- æ’入操作\n")
                .append("    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'INSERT', GETDATE(), 'INSERT INTO [").append(tableName).append("] (");

        // æ‹¼æŽ¥å­—段名
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("[").append(columns.get(i)).append("]");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ");
            }
        }

        triggerSQL.append(") VALUES (''' + ");

        // æ‹¼æŽ¥å­—段值
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ''' + ");
            }
        }

        triggerSQL.append(");'\n")
                .append("        FROM inserted i;\n")
                .append("    END\n\n");

        // æ›´æ–°æ“ä½œ
        triggerSQL.append("    -- æ›´æ–°æ“ä½œ\n")
                .append("    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'UPDATE', GETDATE(), 'UPDATE [").append(tableName).append("] SET ");

        // æ‹¼æŽ¥æ›´æ–°å­—段
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("[").append(columns.get(i)).append("] = ''' + ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ");
            }
        }

        triggerSQL.append(" WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
                .append("        FROM inserted i\n")
                .append("        INNER JOIN deleted d ON i.").append(primaryKey).append(" = d.").append(primaryKey).append(";\n")
                .append("    END\n\n");

        // åˆ é™¤æ“ä½œ
        triggerSQL.append("    -- åˆ é™¤æ“ä½œ\n")
                .append("    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'DELETE', GETDATE(), 'DELETE FROM [").append(tableName).append("] WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
                .append("        FROM deleted d;\n")
                .append("    END\n\n");

        triggerSQL.append("END;\n");

        return triggerSQL.toString();
    }

//    /**
//     * åœ¨æ•°æ®åº“中创建触发器
//     */
//    private static void createTrigger(Connection connection, String triggerSQL) throws SQLException {
//        try (Statement statement = connection.createStatement()) {
//            statement.execute(triggerSQL);
//            System.out.println("Trigger created successfully!");
//        }
//    }
}

 lxzn-module-system/lxzn-system-start/src/main/resources/application-dev.yml

@@ -328,3 +328,6 @@

security:
  encryption-key: 1234567890abcdef
#mdc文件路径配置
fileService:
  localFilePath: D:/data/sql/     #本地文件路径