两网数据交互

Lius

2025-06-24 92b8a49f972e6a6b887745f4d2cc2c0cac704a96

两网数据交互

 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/ImportXmlToDataJob.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,62 @@
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.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

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

/**
 * @author Lius
 * @date 2024/12/19 16:43
 */
@Slf4j
public class ImportXmlToDataJob 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数据任务 ImportXmlToDataJob start!  æ—¶é—´:" + DateUtils.now());
        long startTime = System.currentTimeMillis();
        try {
            logTableService.importXmlToData();
            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执行到数据库
     */
    void importXmlToData();

}

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

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

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.file.FileReader;
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.SqlExecutor;
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.ArrayList;
import java.util.List;

/**
 * @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;

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

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

    @Resource
    private SqlExecutor sqlExecutor;

    /**
     * å¯¼å…¥åŒæ­¥æ•°æ®sql执行到数据库
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void importXmlToData() {
        File[] files = FileUtil.ls(localFilePath);
        List<String> failedSqlList = new ArrayList<>(); // ç”¨äºŽå­˜å‚¨æ‰§è¡Œå¤±è´¥çš„ SQL
        for (File file : files) {
            if (file.isFile()) {
                String loFilePath = localFilePath + file.getName();
                FileReader fileReader = new FileReader(loFilePath);
                List<String> sqlList = fileReader.readLines();
                log.info("成功读取到{}条sql,执行操作", sqlList.size());
                for (String sql : sqlList) {
                    try {
                        sqlExecutor.execute(sql);
                    } catch (Exception e) {
                        failedSqlList.add(sql);
                    }
                }
                if (!failedSqlList.isEmpty()) {
                    try {
                        FileUtil.appendLines(failedSqlList, failedLocFilePath, "UTF-8");
                    } catch (Exception e) {
                        throw new JeecgBootException("数据写入文件失败！");
                    }
                }

                if (Integer.parseInt(file.getName().substring(file.getName().length() - 10, file.getName().length() - 4)) == 1) {
                    // åˆ é™¤åŽ†å²æ–‡ä»¶
                    FileUtil.clean(newFilePath);
                }

                // å¤‡ä»½
                FileUtil.move(new File(loFilePath), new File(newFilePath + file.getName()), true);
            }
        }
    }
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/util/SqlExecutor.java

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

import org.apache.ibatis.jdbc.SqlRunner;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;

/**
 * @author Lius
 * @date 2025/1/3 13:26
 */
@Component
public class SqlExecutor {

    @Resource
    private SqlSessionFactory sqlSessionFactory;

    public void execute(String sql) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(true)) {
            SqlRunner runner = new SqlRunner(sqlSession.getConnection());
            runner.run(sql);
        } catch (Exception e) {
//            e.printStackTrace();
            throw new RuntimeException("Error executing SQL: " + sql, e);
        }
    }
}

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

@@ -321,3 +321,8 @@
    interval: 10000  # ç›‘控间隔(ms)
security:
  encryption-key: 1234567890abcdef # åŠ è§£å¯†ç§˜é’¥
#mdc文件路径配置
fileService:
  localFilePath: D:/data/sql/     #本地文件路径
  newFilePath: D:/data/bak/     #备份文件路径
  failedLocFilePath: D:/data/failed/failed.sql    #失败文件路径