diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java new file mode 100644 index 0000000..ce045cb --- /dev/null +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java @@ -0,0 +1,202 @@ +package com.casic.missiles.job.handler; + +import cn.hutool.core.date.DateUnit; +import cn.hutool.core.date.DateUtil; +import com.xxl.job.core.biz.model.ReturnT; +import com.xxl.job.core.context.XxlJobHelper; +import com.xxl.job.core.handler.annotation.XxlJob; +import lombok.extern.slf4j.Slf4j; +import org.springframework.beans.factory.annotation.Value; +import org.springframework.stereotype.Component; + +import java.io.*; +import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; +import java.util.Date; + +import static com.xxl.job.core.biz.model.ReturnT.SUCCESS; + +/** + * @Description: 数据库备份任务 + * @Author: wangpeng + * @Date: 2023/7/4 13:39 + */ +@Slf4j +@Component +public class MysqlDataBackupHandler { + /** + * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) + */ + String filePath = "/backupSql/"; + + /** + * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 + */ + Boolean isDbVersion8 = true; + + /** + * 备份命令 + * USERNAME 账号 + * PASSWORD 密码 + * SERVERPATH 服务器IP/域名 + * DBNAME 数据库名称 + * FILEPATH 备份文件存放地址+名称 + * 说明 + * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） + * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 + */ + String cmdMysql8 = "D:\\software\\mysql-8.0\\mysql-8.0.16-winx64\\bin\\mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + + + @Value("${spring.datasource.url}") + private String dbUrl; + + @Value("${spring.datasource.username}") + private String dbUserName; + + @Value("${spring.datasource.password}") + private String dbPassWord; + + + // TODO: 2023/7/4 数据库备份Linux测试，备份文件恢复测试 + /** + * 每天凌晨4点执行 + */ + @XxlJob(value = "mysqlDataBackupHandler") + public ReturnT execute() { + XxlJobHelper.log("【备份数据库】--START"); + log.info("【备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + log.info("【备份数据库】,文件存储路径为：{}", finalPath); + String dbUrl2 = dbUrl.replace("jdbc:mysql://", ""); + // 获取数据库名称 + String dbName = dbUrl2.substring(dbUrl2.indexOf("/") + 1, dbUrl2.indexOf("?")); + // 获取数据库地址 + String serverPath = dbUrl2.substring(0, dbUrl2.indexOf("/")); + String server = serverPath.substring(0, serverPath.indexOf(":")); + String port = serverPath.substring(serverPath.indexOf(":") + 1); + // 数据库账号 + String username = dbUserName; + // 数据库密码 + String password = dbPassWord; + + // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql) + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + LocalDateTime localDateTime = LocalDateTime.now(); + String timeStr = formatter.format(localDateTime); + String pathFileName = finalPath + dbName + "_" + timeStr + ".sql"; + String newCmd = ""; + if (isDbVersion8) { + newCmd = cmdMysql8; + } else { + newCmd = cmd; + } + // 执行命令 + newCmd = newCmd.replace("{USERNAME}", username) + .replace("{PASSWORD}", password) + .replace("{SERVER}", server) + .replace("{PORT}", port) + .replace("{DBNAME}", dbName) + .replace("{FILEPATH}", pathFileName); + System.out.println(newCmd); + PrintWriter printWriter = null; + BufferedReader bufferedReader = null; + try { + // 创建存放sql的文件 + existsFile(new File(pathFileName)); + printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8")); + Process process = null; + String property = System.getProperty("os.name"); + System.out.println(property); + if (property.indexOf("Linux") != -1) { + // linux + process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd}); + } else { + // 本地win + process = Runtime.getRuntime().exec(newCmd); + } + InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); + bufferedReader = new BufferedReader(inputStreamReader); + String line; + while ((line = bufferedReader.readLine()) != null) { + printWriter.println(line); + } + // 此次会执行过长时间,直到备份完成 + printWriter.flush(); + printWriter.close(); + //0 表示线程正常终止。 +// if (process.waitFor() == 0) { +// // 线程正常执行 +// log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); +// } + process.waitFor(); + log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); + } catch (Exception e) { + e.printStackTrace(); + log.info("【备份数据库】FAILURE"); + } finally { + try { + if (bufferedReader != null) { + bufferedReader.close(); + } + if (printWriter != null) { + printWriter.close(); + } + } catch (IOException e) { + e.printStackTrace(); + } + } + log.info("【备份数据库】--END"); + return SUCCESS; + } + + /** + * 定期删除备份，只保留最近一个星期 + * 每天凌晨5点执行 + */ + @XxlJob(value = "mysqlDataBackupDeleteHandler") + public ReturnT deleteBackUpDataBase() { + log.info("【删除备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + File backUpFile = new File(finalPath); + if (backUpFile.exists()) { + File[] files = backUpFile.listFiles(); + for (File file : files) { + if (!file.isDirectory()) { + String substring = file.getName().substring(file.getName().indexOf("2"), file.getName().indexOf(".")); + Date date1 = DateUtil.parse(substring, "yyyy-MM-dd"); + Date date2 = new Date(); + long betweenDay = DateUtil.between(date1, date2, DateUnit.DAY); + if (betweenDay > 7) { + file.delete(); + log.info("【删除备份数据库】SUCCESS，SQL文件：{}", file.getName()); + } + } + } + } + log.info("【删除备份数据库】--END"); + return SUCCESS; + } + + /** + * 判断文件是否存在，不存在创建 + */ + private static void existsFile(File file) { + // 判断文件路径是否存在,不存在新建 + if (!file.getParentFile().exists()) { + file.getParentFile().mkdirs(); + } + if (!file.exists()) { + try { + file.createNewFile(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + +} diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java new file mode 100644 index 0000000..ce045cb --- /dev/null +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java @@ -0,0 +1,202 @@ +package com.casic.missiles.job.handler; + +import cn.hutool.core.date.DateUnit; +import cn.hutool.core.date.DateUtil; +import com.xxl.job.core.biz.model.ReturnT; +import com.xxl.job.core.context.XxlJobHelper; +import com.xxl.job.core.handler.annotation.XxlJob; +import lombok.extern.slf4j.Slf4j; +import org.springframework.beans.factory.annotation.Value; +import org.springframework.stereotype.Component; + +import java.io.*; +import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; +import java.util.Date; + +import static com.xxl.job.core.biz.model.ReturnT.SUCCESS; + +/** + * @Description: 数据库备份任务 + * @Author: wangpeng + * @Date: 2023/7/4 13:39 + */ +@Slf4j +@Component +public class MysqlDataBackupHandler { + /** + * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) + */ + String filePath = "/backupSql/"; + + /** + * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 + */ + Boolean isDbVersion8 = true; + + /** + * 备份命令 + * USERNAME 账号 + * PASSWORD 密码 + * SERVERPATH 服务器IP/域名 + * DBNAME 数据库名称 + * FILEPATH 备份文件存放地址+名称 + * 说明 + * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） + * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 + */ + String cmdMysql8 = "D:\\software\\mysql-8.0\\mysql-8.0.16-winx64\\bin\\mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + + + @Value("${spring.datasource.url}") + private String dbUrl; + + @Value("${spring.datasource.username}") + private String dbUserName; + + @Value("${spring.datasource.password}") + private String dbPassWord; + + + // TODO: 2023/7/4 数据库备份Linux测试，备份文件恢复测试 + /** + * 每天凌晨4点执行 + */ + @XxlJob(value = "mysqlDataBackupHandler") + public ReturnT execute() { + XxlJobHelper.log("【备份数据库】--START"); + log.info("【备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + log.info("【备份数据库】,文件存储路径为：{}", finalPath); + String dbUrl2 = dbUrl.replace("jdbc:mysql://", ""); + // 获取数据库名称 + String dbName = dbUrl2.substring(dbUrl2.indexOf("/") + 1, dbUrl2.indexOf("?")); + // 获取数据库地址 + String serverPath = dbUrl2.substring(0, dbUrl2.indexOf("/")); + String server = serverPath.substring(0, serverPath.indexOf(":")); + String port = serverPath.substring(serverPath.indexOf(":") + 1); + // 数据库账号 + String username = dbUserName; + // 数据库密码 + String password = dbPassWord; + + // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql) + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + LocalDateTime localDateTime = LocalDateTime.now(); + String timeStr = formatter.format(localDateTime); + String pathFileName = finalPath + dbName + "_" + timeStr + ".sql"; + String newCmd = ""; + if (isDbVersion8) { + newCmd = cmdMysql8; + } else { + newCmd = cmd; + } + // 执行命令 + newCmd = newCmd.replace("{USERNAME}", username) + .replace("{PASSWORD}", password) + .replace("{SERVER}", server) + .replace("{PORT}", port) + .replace("{DBNAME}", dbName) + .replace("{FILEPATH}", pathFileName); + System.out.println(newCmd); + PrintWriter printWriter = null; + BufferedReader bufferedReader = null; + try { + // 创建存放sql的文件 + existsFile(new File(pathFileName)); + printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8")); + Process process = null; + String property = System.getProperty("os.name"); + System.out.println(property); + if (property.indexOf("Linux") != -1) { + // linux + process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd}); + } else { + // 本地win + process = Runtime.getRuntime().exec(newCmd); + } + InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); + bufferedReader = new BufferedReader(inputStreamReader); + String line; + while ((line = bufferedReader.readLine()) != null) { + printWriter.println(line); + } + // 此次会执行过长时间,直到备份完成 + printWriter.flush(); + printWriter.close(); + //0 表示线程正常终止。 +// if (process.waitFor() == 0) { +// // 线程正常执行 +// log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); +// } + process.waitFor(); + log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); + } catch (Exception e) { + e.printStackTrace(); + log.info("【备份数据库】FAILURE"); + } finally { + try { + if (bufferedReader != null) { + bufferedReader.close(); + } + if (printWriter != null) { + printWriter.close(); + } + } catch (IOException e) { + e.printStackTrace(); + } + } + log.info("【备份数据库】--END"); + return SUCCESS; + } + + /** + * 定期删除备份，只保留最近一个星期 + * 每天凌晨5点执行 + */ + @XxlJob(value = "mysqlDataBackupDeleteHandler") + public ReturnT deleteBackUpDataBase() { + log.info("【删除备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + File backUpFile = new File(finalPath); + if (backUpFile.exists()) { + File[] files = backUpFile.listFiles(); + for (File file : files) { + if (!file.isDirectory()) { + String substring = file.getName().substring(file.getName().indexOf("2"), file.getName().indexOf(".")); + Date date1 = DateUtil.parse(substring, "yyyy-MM-dd"); + Date date2 = new Date(); + long betweenDay = DateUtil.between(date1, date2, DateUnit.DAY); + if (betweenDay > 7) { + file.delete(); + log.info("【删除备份数据库】SUCCESS，SQL文件：{}", file.getName()); + } + } + } + } + log.info("【删除备份数据库】--END"); + return SUCCESS; + } + + /** + * 判断文件是否存在，不存在创建 + */ + private static void existsFile(File file) { + // 判断文件路径是否存在,不存在新建 + if (!file.getParentFile().exists()) { + file.getParentFile().mkdirs(); + } + if (!file.exists()) { + try { + file.createNewFile(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + +} diff --git a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java index b0cdb5b..c084330 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java @@ -16,6 +16,18 @@ @ApiModelProperty(value = "委托书id", dataType = "Long") private Long orderId; + @ApiModelProperty(value = "委托书编号", dataType = "String") + private String orderCode; + + @ApiModelProperty(value = "委托方id", dataType = "Long") + private Long customerId; + + @ApiModelProperty(value = "委托方代码", dataType = "String") + private String customerNo; + + @ApiModelProperty(value = "委托方名称", dataType = "String") + private String customerName; + @ApiModelProperty(value = "样品编号", dataType = "String") private String sampleNo; @@ -25,6 +37,9 @@ @ApiModelProperty(value = "样品型号", dataType = "String") private String sampleModel; + @ApiModelProperty(value = "出厂编号", dataType = "String") + private String manufacturingNo; + @ApiModelProperty(value = "标签绑定", dataType = "String") private String labelBind; } diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java new file mode 100644 index 0000000..ce045cb --- /dev/null +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java @@ -0,0 +1,202 @@ +package com.casic.missiles.job.handler; + +import cn.hutool.core.date.DateUnit; +import cn.hutool.core.date.DateUtil; +import com.xxl.job.core.biz.model.ReturnT; +import com.xxl.job.core.context.XxlJobHelper; +import com.xxl.job.core.handler.annotation.XxlJob; +import lombok.extern.slf4j.Slf4j; +import org.springframework.beans.factory.annotation.Value; +import org.springframework.stereotype.Component; + +import java.io.*; +import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; +import java.util.Date; + +import static com.xxl.job.core.biz.model.ReturnT.SUCCESS; + +/** + * @Description: 数据库备份任务 + * @Author: wangpeng + * @Date: 2023/7/4 13:39 + */ +@Slf4j +@Component +public class MysqlDataBackupHandler { + /** + * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) + */ + String filePath = "/backupSql/"; + + /** + * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 + */ + Boolean isDbVersion8 = true; + + /** + * 备份命令 + * USERNAME 账号 + * PASSWORD 密码 + * SERVERPATH 服务器IP/域名 + * DBNAME 数据库名称 + * FILEPATH 备份文件存放地址+名称 + * 说明 + * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） + * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 + */ + String cmdMysql8 = "D:\\software\\mysql-8.0\\mysql-8.0.16-winx64\\bin\\mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + + + @Value("${spring.datasource.url}") + private String dbUrl; + + @Value("${spring.datasource.username}") + private String dbUserName; + + @Value("${spring.datasource.password}") + private String dbPassWord; + + + // TODO: 2023/7/4 数据库备份Linux测试，备份文件恢复测试 + /** + * 每天凌晨4点执行 + */ + @XxlJob(value = "mysqlDataBackupHandler") + public ReturnT execute() { + XxlJobHelper.log("【备份数据库】--START"); + log.info("【备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + log.info("【备份数据库】,文件存储路径为：{}", finalPath); + String dbUrl2 = dbUrl.replace("jdbc:mysql://", ""); + // 获取数据库名称 + String dbName = dbUrl2.substring(dbUrl2.indexOf("/") + 1, dbUrl2.indexOf("?")); + // 获取数据库地址 + String serverPath = dbUrl2.substring(0, dbUrl2.indexOf("/")); + String server = serverPath.substring(0, serverPath.indexOf(":")); + String port = serverPath.substring(serverPath.indexOf(":") + 1); + // 数据库账号 + String username = dbUserName; + // 数据库密码 + String password = dbPassWord; + + // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql) + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + LocalDateTime localDateTime = LocalDateTime.now(); + String timeStr = formatter.format(localDateTime); + String pathFileName = finalPath + dbName + "_" + timeStr + ".sql"; + String newCmd = ""; + if (isDbVersion8) { + newCmd = cmdMysql8; + } else { + newCmd = cmd; + } + // 执行命令 + newCmd = newCmd.replace("{USERNAME}", username) + .replace("{PASSWORD}", password) + .replace("{SERVER}", server) + .replace("{PORT}", port) + .replace("{DBNAME}", dbName) + .replace("{FILEPATH}", pathFileName); + System.out.println(newCmd); + PrintWriter printWriter = null; + BufferedReader bufferedReader = null; + try { + // 创建存放sql的文件 + existsFile(new File(pathFileName)); + printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8")); + Process process = null; + String property = System.getProperty("os.name"); + System.out.println(property); + if (property.indexOf("Linux") != -1) { + // linux + process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd}); + } else { + // 本地win + process = Runtime.getRuntime().exec(newCmd); + } + InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); + bufferedReader = new BufferedReader(inputStreamReader); + String line; + while ((line = bufferedReader.readLine()) != null) { + printWriter.println(line); + } + // 此次会执行过长时间,直到备份完成 + printWriter.flush(); + printWriter.close(); + //0 表示线程正常终止。 +// if (process.waitFor() == 0) { +// // 线程正常执行 +// log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); +// } + process.waitFor(); + log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); + } catch (Exception e) { + e.printStackTrace(); + log.info("【备份数据库】FAILURE"); + } finally { + try { + if (bufferedReader != null) { + bufferedReader.close(); + } + if (printWriter != null) { + printWriter.close(); + } + } catch (IOException e) { + e.printStackTrace(); + } + } + log.info("【备份数据库】--END"); + return SUCCESS; + } + + /** + * 定期删除备份，只保留最近一个星期 + * 每天凌晨5点执行 + */ + @XxlJob(value = "mysqlDataBackupDeleteHandler") + public ReturnT deleteBackUpDataBase() { + log.info("【删除备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + File backUpFile = new File(finalPath); + if (backUpFile.exists()) { + File[] files = backUpFile.listFiles(); + for (File file : files) { + if (!file.isDirectory()) { + String substring = file.getName().substring(file.getName().indexOf("2"), file.getName().indexOf(".")); + Date date1 = DateUtil.parse(substring, "yyyy-MM-dd"); + Date date2 = new Date(); + long betweenDay = DateUtil.between(date1, date2, DateUnit.DAY); + if (betweenDay > 7) { + file.delete(); + log.info("【删除备份数据库】SUCCESS，SQL文件：{}", file.getName()); + } + } + } + } + log.info("【删除备份数据库】--END"); + return SUCCESS; + } + + /** + * 判断文件是否存在，不存在创建 + */ + private static void existsFile(File file) { + // 判断文件路径是否存在,不存在新建 + if (!file.getParentFile().exists()) { + file.getParentFile().mkdirs(); + } + if (!file.exists()) { + try { + file.createNewFile(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + +} diff --git a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java index b0cdb5b..c084330 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java @@ -16,6 +16,18 @@ @ApiModelProperty(value = "委托书id", dataType = "Long") private Long orderId; + @ApiModelProperty(value = "委托书编号", dataType = "String") + private String orderCode; + + @ApiModelProperty(value = "委托方id", dataType = "Long") + private Long customerId; + + @ApiModelProperty(value = "委托方代码", dataType = "String") + private String customerNo; + + @ApiModelProperty(value = "委托方名称", dataType = "String") + private String customerName; + @ApiModelProperty(value = "样品编号", dataType = "String") private String sampleNo; @@ -25,6 +37,9 @@ @ApiModelProperty(value = "样品型号", dataType = "String") private String sampleModel; + @ApiModelProperty(value = "出厂编号", dataType = "String") + private String manufacturingNo; + @ApiModelProperty(value = "标签绑定", dataType = "String") private String labelBind; } diff --git a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java index 27d4981..832e87a 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java @@ -118,4 +118,9 @@ @ApiModelProperty(value = "其他跳转参数", dataType = "Object") @TableField(exist = false) private Object skipParamsObject = new Object(); + + //删除标志，用于删除在库中已删除的数据 + @ApiModelProperty(hidden = true) + @TableField(exist = false) + private int del = 0; } diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java new file mode 100644 index 0000000..ce045cb --- /dev/null +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java @@ -0,0 +1,202 @@ +package com.casic.missiles.job.handler; + +import cn.hutool.core.date.DateUnit; +import cn.hutool.core.date.DateUtil; +import com.xxl.job.core.biz.model.ReturnT; +import com.xxl.job.core.context.XxlJobHelper; +import com.xxl.job.core.handler.annotation.XxlJob; +import lombok.extern.slf4j.Slf4j; +import org.springframework.beans.factory.annotation.Value; +import org.springframework.stereotype.Component; + +import java.io.*; +import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; +import java.util.Date; + +import static com.xxl.job.core.biz.model.ReturnT.SUCCESS; + +/** + * @Description: 数据库备份任务 + * @Author: wangpeng + * @Date: 2023/7/4 13:39 + */ +@Slf4j +@Component +public class MysqlDataBackupHandler { + /** + * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) + */ + String filePath = "/backupSql/"; + + /** + * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 + */ + Boolean isDbVersion8 = true; + + /** + * 备份命令 + * USERNAME 账号 + * PASSWORD 密码 + * SERVERPATH 服务器IP/域名 + * DBNAME 数据库名称 + * FILEPATH 备份文件存放地址+名称 + * 说明 + * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） + * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 + */ + String cmdMysql8 = "D:\\software\\mysql-8.0\\mysql-8.0.16-winx64\\bin\\mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + + + @Value("${spring.datasource.url}") + private String dbUrl; + + @Value("${spring.datasource.username}") + private String dbUserName; + + @Value("${spring.datasource.password}") + private String dbPassWord; + + + // TODO: 2023/7/4 数据库备份Linux测试，备份文件恢复测试 + /** + * 每天凌晨4点执行 + */ + @XxlJob(value = "mysqlDataBackupHandler") + public ReturnT execute() { + XxlJobHelper.log("【备份数据库】--START"); + log.info("【备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + log.info("【备份数据库】,文件存储路径为：{}", finalPath); + String dbUrl2 = dbUrl.replace("jdbc:mysql://", ""); + // 获取数据库名称 + String dbName = dbUrl2.substring(dbUrl2.indexOf("/") + 1, dbUrl2.indexOf("?")); + // 获取数据库地址 + String serverPath = dbUrl2.substring(0, dbUrl2.indexOf("/")); + String server = serverPath.substring(0, serverPath.indexOf(":")); + String port = serverPath.substring(serverPath.indexOf(":") + 1); + // 数据库账号 + String username = dbUserName; + // 数据库密码 + String password = dbPassWord; + + // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql) + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + LocalDateTime localDateTime = LocalDateTime.now(); + String timeStr = formatter.format(localDateTime); + String pathFileName = finalPath + dbName + "_" + timeStr + ".sql"; + String newCmd = ""; + if (isDbVersion8) { + newCmd = cmdMysql8; + } else { + newCmd = cmd; + } + // 执行命令 + newCmd = newCmd.replace("{USERNAME}", username) + .replace("{PASSWORD}", password) + .replace("{SERVER}", server) + .replace("{PORT}", port) + .replace("{DBNAME}", dbName) + .replace("{FILEPATH}", pathFileName); + System.out.println(newCmd); + PrintWriter printWriter = null; + BufferedReader bufferedReader = null; + try { + // 创建存放sql的文件 + existsFile(new File(pathFileName)); + printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8")); + Process process = null; + String property = System.getProperty("os.name"); + System.out.println(property); + if (property.indexOf("Linux") != -1) { + // linux + process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd}); + } else { + // 本地win + process = Runtime.getRuntime().exec(newCmd); + } + InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); + bufferedReader = new BufferedReader(inputStreamReader); + String line; + while ((line = bufferedReader.readLine()) != null) { + printWriter.println(line); + } + // 此次会执行过长时间,直到备份完成 + printWriter.flush(); + printWriter.close(); + //0 表示线程正常终止。 +// if (process.waitFor() == 0) { +// // 线程正常执行 +// log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); +// } + process.waitFor(); + log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); + } catch (Exception e) { + e.printStackTrace(); + log.info("【备份数据库】FAILURE"); + } finally { + try { + if (bufferedReader != null) { + bufferedReader.close(); + } + if (printWriter != null) { + printWriter.close(); + } + } catch (IOException e) { + e.printStackTrace(); + } + } + log.info("【备份数据库】--END"); + return SUCCESS; + } + + /** + * 定期删除备份，只保留最近一个星期 + * 每天凌晨5点执行 + */ + @XxlJob(value = "mysqlDataBackupDeleteHandler") + public ReturnT deleteBackUpDataBase() { + log.info("【删除备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + File backUpFile = new File(finalPath); + if (backUpFile.exists()) { + File[] files = backUpFile.listFiles(); + for (File file : files) { + if (!file.isDirectory()) { + String substring = file.getName().substring(file.getName().indexOf("2"), file.getName().indexOf(".")); + Date date1 = DateUtil.parse(substring, "yyyy-MM-dd"); + Date date2 = new Date(); + long betweenDay = DateUtil.between(date1, date2, DateUnit.DAY); + if (betweenDay > 7) { + file.delete(); + log.info("【删除备份数据库】SUCCESS，SQL文件：{}", file.getName()); + } + } + } + } + log.info("【删除备份数据库】--END"); + return SUCCESS; + } + + /** + * 判断文件是否存在，不存在创建 + */ + private static void existsFile(File file) { + // 判断文件路径是否存在,不存在新建 + if (!file.getParentFile().exists()) { + file.getParentFile().mkdirs(); + } + if (!file.exists()) { + try { + file.createNewFile(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + +} diff --git a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java index b0cdb5b..c084330 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java @@ -16,6 +16,18 @@ @ApiModelProperty(value = "委托书id", dataType = "Long") private Long orderId; + @ApiModelProperty(value = "委托书编号", dataType = "String") + private String orderCode; + + @ApiModelProperty(value = "委托方id", dataType = "Long") + private Long customerId; + + @ApiModelProperty(value = "委托方代码", dataType = "String") + private String customerNo; + + @ApiModelProperty(value = "委托方名称", dataType = "String") + private String customerName; + @ApiModelProperty(value = "样品编号", dataType = "String") private String sampleNo; @@ -25,6 +37,9 @@ @ApiModelProperty(value = "样品型号", dataType = "String") private String sampleModel; + @ApiModelProperty(value = "出厂编号", dataType = "String") + private String manufacturingNo; + @ApiModelProperty(value = "标签绑定", dataType = "String") private String labelBind; } diff --git a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java index 27d4981..832e87a 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java @@ -118,4 +118,9 @@ @ApiModelProperty(value = "其他跳转参数", dataType = "Object") @TableField(exist = false) private Object skipParamsObject = new Object(); + + //删除标志，用于删除在库中已删除的数据 + @ApiModelProperty(hidden = true) + @TableField(exist = false) + private int del = 0; } diff --git a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java index 3b0566c..799b2a5 100644 --- a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java +++ b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java @@ -6,10 +6,8 @@ import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.mapper.MeterFixedAssetsMapper; import com.casic.missiles.mapper.customer.CustomerSampleMapper; -import com.casic.missiles.model.customer.CustomerSampleInfo; import com.casic.missiles.model.equipment.EquipmentFixedAssets; import com.casic.missiles.service.business.IBusinessReadWriterService; -import com.casic.missiles.utils.ConvertUtils; import gnu.io.SerialPort; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; @@ -19,7 +17,6 @@ import java.util.ArrayList; import java.util.List; import java.util.Set; -import java.util.concurrent.ForkJoinPool; /** * @Description: 读写器相关业务 @@ -33,8 +30,6 @@ private CustomerSampleMapper sampleMapper; @Autowired private MeterFixedAssetsMapper meterFixedAssetsMapper; - @Autowired - private ForkJoinPool forkJoinPool; public static SerialPort serialPort = null; @@ -73,21 +68,13 @@ } private List queryGunReadSampleList(Set gunSet) { - QueryWrapper wrapper = new QueryWrapper<>(); - wrapper.eq("is_del", 0); - wrapper.in("sample_no", gunSet); - List customerSampleInfos = sampleMapper.selectList(wrapper); - List resultList = ConvertUtils.sourceToTarget(customerSampleInfos, ReadSampleDTO.class); - return resultList; + List readSampleDTOS = sampleMapper.selectListForReadWriter(gunSet, null); + return readSampleDTOS; } private List queryReadSampleList(Set tIds) { - QueryWrapper wrapper = new QueryWrapper<>(); - wrapper.eq("is_del", 0); - wrapper.in("label_bind", tIds); - List customerSampleInfos = sampleMapper.selectList(wrapper); - List resultList = ConvertUtils.sourceToTarget(customerSampleInfos, ReadSampleDTO.class); - return resultList; + List readSampleDTOS = sampleMapper.selectListForReadWriter(null, tIds); + return readSampleDTOS; } // @Override diff --git a/casic-metering-api/src/main/resources/config/application.yml b/casic-metering-api/src/main/resources/config/application.yml index c06a3b9..f734f50 100644 --- a/casic-metering-api/src/main/resources/config/application.yml +++ b/casic-metering-api/src/main/resources/config/application.yml @@ -70,9 +70,9 @@ executor: # 此执行器的名称 appname: casic-metering-job - # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信 - ip: 127.0.0.1 - port: 9999 + # 此执行器的ip、端口，每个节点对应自己的ip，端口用于任务执行的通信，默认为空表示自动获取IP，默认端口为9999 +# ip: 127.0.0.1 +# port: 9999 # 此执行器的日志存放路径 logpath: logs/xxl-job/casic-metering-job # 此执行器的日志保存时间 diff --git a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java index a021e9e..1399880 100644 --- a/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java +++ b/casic-metering-dao/src/main/java/com/casic/missiles/mapper/customer/CustomerSampleMapper.java @@ -2,12 +2,14 @@ import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; +import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.dto.customer.MeasureRecordsResponse; import com.casic.missiles.dto.customer.sample.*; import com.casic.missiles.model.customer.CustomerSampleInfo; import org.apache.ibatis.annotations.Param; import java.util.List; +import java.util.Set; public interface CustomerSampleMapper extends BaseMapper { @@ -32,5 +34,5 @@ List listPageBySubpackageCertificate(@Param("page") Page page, @Param("request") InterchangeSampleListRequest request); - + List selectListForReadWriter(@Param("gunSet") Set gunSet, @Param("tIds") Set tIds); } diff --git a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml index 9374043..623f193 100644 --- a/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml +++ b/casic-metering-dao/src/main/resources/mapper/customer/CustomerSampleMapper.xml @@ -231,4 +231,25 @@ ) + diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java index 250d99b..1148ff8 100644 --- a/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/config/XxlJobConfig.java @@ -28,11 +28,11 @@ // @Value("${xxl.job.executor.address}") // private String address; - @Value("${xxl.job.executor.ip}") - private String ip; - - @Value("${xxl.job.executor.port}") - private int port; +// @Value("${xxl.job.executor.ip}") +// private String ip; +// +// @Value("${xxl.job.executor.port}") +// private int port; @Value("${xxl.job.executor.logpath}") private String logPath; @@ -47,8 +47,8 @@ xxlJobSpringExecutor.setAdminAddresses(adminAddresses); xxlJobSpringExecutor.setAppname(appname); // xxlJobSpringExecutor.setAddress(address); - xxlJobSpringExecutor.setIp(ip); - xxlJobSpringExecutor.setPort(port); +// xxlJobSpringExecutor.setIp(ip); +// xxlJobSpringExecutor.setPort(port); xxlJobSpringExecutor.setAccessToken(accessToken); xxlJobSpringExecutor.setLogPath(logPath); xxlJobSpringExecutor.setLogRetentionDays(logRetentionDays); diff --git a/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java new file mode 100644 index 0000000..ce045cb --- /dev/null +++ b/casic-metering-job/src/main/java/com/casic/missiles/job/handler/MysqlDataBackupHandler.java @@ -0,0 +1,202 @@ +package com.casic.missiles.job.handler; + +import cn.hutool.core.date.DateUnit; +import cn.hutool.core.date.DateUtil; +import com.xxl.job.core.biz.model.ReturnT; +import com.xxl.job.core.context.XxlJobHelper; +import com.xxl.job.core.handler.annotation.XxlJob; +import lombok.extern.slf4j.Slf4j; +import org.springframework.beans.factory.annotation.Value; +import org.springframework.stereotype.Component; + +import java.io.*; +import java.time.LocalDateTime; +import java.time.format.DateTimeFormatter; +import java.util.Date; + +import static com.xxl.job.core.biz.model.ReturnT.SUCCESS; + +/** + * @Description: 数据库备份任务 + * @Author: wangpeng + * @Date: 2023/7/4 13:39 + */ +@Slf4j +@Component +public class MysqlDataBackupHandler { + /** + * 备份 sql 存放目录(相对路径, 注意可能需要在 MvcConfig 配置访问权限) + */ + String filePath = "/backupSql/"; + + /** + * 数据库版本是否为 8.0 + （false=否 true=是）， mysql8+ 需要参数 --column-statistics=0 ， mysql8- 不需要 + */ + Boolean isDbVersion8 = true; + + /** + * 备份命令 + * USERNAME 账号 + * PASSWORD 密码 + * SERVERPATH 服务器IP/域名 + * DBNAME 数据库名称 + * FILEPATH 备份文件存放地址+名称 + * 说明 + * cmdCompression ： 需压缩 （本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + gzip 命令(独立安装)） + * cmd ： 不压缩 (本地或服务器需安装 mysqldump 命令(安装mysql自带患独立安装) + * --column-statistics=0 mysql8 添加该参数, 非mysql8 不添加, 否则将出错 + */ + String cmdMysql8 = "D:\\software\\mysql-8.0\\mysql-8.0.16-winx64\\bin\\mysqldump --column-statistics=0 -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + String cmd = "mysqldump -u{USERNAME} -p{PASSWORD} -h{SERVER} -P{PORT} --databases {DBNAME} > {FILEPATH}"; + + + @Value("${spring.datasource.url}") + private String dbUrl; + + @Value("${spring.datasource.username}") + private String dbUserName; + + @Value("${spring.datasource.password}") + private String dbPassWord; + + + // TODO: 2023/7/4 数据库备份Linux测试，备份文件恢复测试 + /** + * 每天凌晨4点执行 + */ + @XxlJob(value = "mysqlDataBackupHandler") + public ReturnT execute() { + XxlJobHelper.log("【备份数据库】--START"); + log.info("【备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + log.info("【备份数据库】,文件存储路径为：{}", finalPath); + String dbUrl2 = dbUrl.replace("jdbc:mysql://", ""); + // 获取数据库名称 + String dbName = dbUrl2.substring(dbUrl2.indexOf("/") + 1, dbUrl2.indexOf("?")); + // 获取数据库地址 + String serverPath = dbUrl2.substring(0, dbUrl2.indexOf("/")); + String server = serverPath.substring(0, serverPath.indexOf(":")); + String port = serverPath.substring(serverPath.indexOf(":") + 1); + // 数据库账号 + String username = dbUserName; + // 数据库密码 + String password = dbPassWord; + + // 备份文件目录+名称 备份文件存放目录+名称(名称 = 数据库名+时间字符串.sql) + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + LocalDateTime localDateTime = LocalDateTime.now(); + String timeStr = formatter.format(localDateTime); + String pathFileName = finalPath + dbName + "_" + timeStr + ".sql"; + String newCmd = ""; + if (isDbVersion8) { + newCmd = cmdMysql8; + } else { + newCmd = cmd; + } + // 执行命令 + newCmd = newCmd.replace("{USERNAME}", username) + .replace("{PASSWORD}", password) + .replace("{SERVER}", server) + .replace("{PORT}", port) + .replace("{DBNAME}", dbName) + .replace("{FILEPATH}", pathFileName); + System.out.println(newCmd); + PrintWriter printWriter = null; + BufferedReader bufferedReader = null; + try { + // 创建存放sql的文件 + existsFile(new File(pathFileName)); + printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(pathFileName), "utf8")); + Process process = null; + String property = System.getProperty("os.name"); + System.out.println(property); + if (property.indexOf("Linux") != -1) { + // linux + process = Runtime.getRuntime().exec(new String[]{"bash", "-c", newCmd}); + } else { + // 本地win + process = Runtime.getRuntime().exec(newCmd); + } + InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(), "utf8"); + bufferedReader = new BufferedReader(inputStreamReader); + String line; + while ((line = bufferedReader.readLine()) != null) { + printWriter.println(line); + } + // 此次会执行过长时间,直到备份完成 + printWriter.flush(); + printWriter.close(); + //0 表示线程正常终止。 +// if (process.waitFor() == 0) { +// // 线程正常执行 +// log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); +// } + process.waitFor(); + log.info("【备份数据库】SUCCESS，SQL文件：{}", pathFileName); + } catch (Exception e) { + e.printStackTrace(); + log.info("【备份数据库】FAILURE"); + } finally { + try { + if (bufferedReader != null) { + bufferedReader.close(); + } + if (printWriter != null) { + printWriter.close(); + } + } catch (IOException e) { + e.printStackTrace(); + } + } + log.info("【备份数据库】--END"); + return SUCCESS; + } + + /** + * 定期删除备份，只保留最近一个星期 + * 每天凌晨5点执行 + */ + @XxlJob(value = "mysqlDataBackupDeleteHandler") + public ReturnT deleteBackUpDataBase() { + log.info("【删除备份数据库】--START"); + String path = System.getProperty("user.dir"); + String finalPath = path + filePath; + File backUpFile = new File(finalPath); + if (backUpFile.exists()) { + File[] files = backUpFile.listFiles(); + for (File file : files) { + if (!file.isDirectory()) { + String substring = file.getName().substring(file.getName().indexOf("2"), file.getName().indexOf(".")); + Date date1 = DateUtil.parse(substring, "yyyy-MM-dd"); + Date date2 = new Date(); + long betweenDay = DateUtil.between(date1, date2, DateUnit.DAY); + if (betweenDay > 7) { + file.delete(); + log.info("【删除备份数据库】SUCCESS，SQL文件：{}", file.getName()); + } + } + } + } + log.info("【删除备份数据库】--END"); + return SUCCESS; + } + + /** + * 判断文件是否存在，不存在创建 + */ + private static void existsFile(File file) { + // 判断文件路径是否存在,不存在新建 + if (!file.getParentFile().exists()) { + file.getParentFile().mkdirs(); + } + if (!file.exists()) { + try { + file.createNewFile(); + } catch (IOException e) { + e.printStackTrace(); + } + } + } + +} diff --git a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java index b0cdb5b..c084330 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/dto/business/dispatch/ReadSampleDTO.java @@ -16,6 +16,18 @@ @ApiModelProperty(value = "委托书id", dataType = "Long") private Long orderId; + @ApiModelProperty(value = "委托书编号", dataType = "String") + private String orderCode; + + @ApiModelProperty(value = "委托方id", dataType = "Long") + private Long customerId; + + @ApiModelProperty(value = "委托方代码", dataType = "String") + private String customerNo; + + @ApiModelProperty(value = "委托方名称", dataType = "String") + private String customerName; + @ApiModelProperty(value = "样品编号", dataType = "String") private String sampleNo; @@ -25,6 +37,9 @@ @ApiModelProperty(value = "样品型号", dataType = "String") private String sampleModel; + @ApiModelProperty(value = "出厂编号", dataType = "String") + private String manufacturingNo; + @ApiModelProperty(value = "标签绑定", dataType = "String") private String labelBind; } diff --git a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java index 27d4981..832e87a 100644 --- a/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java +++ b/casic-metering-model/src/main/java/com/casic/missiles/model/workbench/WorkbenchApprovalMessage.java @@ -118,4 +118,9 @@ @ApiModelProperty(value = "其他跳转参数", dataType = "Object") @TableField(exist = false) private Object skipParamsObject = new Object(); + + //删除标志，用于删除在库中已删除的数据 + @ApiModelProperty(hidden = true) + @TableField(exist = false) + private int del = 0; } diff --git a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java index 3b0566c..799b2a5 100644 --- a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java +++ b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/business/BusinessReadWriterServiceImpl.java @@ -6,10 +6,8 @@ import com.casic.missiles.dto.business.dispatch.ReadSampleDTO; import com.casic.missiles.mapper.MeterFixedAssetsMapper; import com.casic.missiles.mapper.customer.CustomerSampleMapper; -import com.casic.missiles.model.customer.CustomerSampleInfo; import com.casic.missiles.model.equipment.EquipmentFixedAssets; import com.casic.missiles.service.business.IBusinessReadWriterService; -import com.casic.missiles.utils.ConvertUtils; import gnu.io.SerialPort; import lombok.extern.slf4j.Slf4j; import org.springframework.beans.factory.annotation.Autowired; @@ -19,7 +17,6 @@ import java.util.ArrayList; import java.util.List; import java.util.Set; -import java.util.concurrent.ForkJoinPool; /** * @Description: 读写器相关业务 @@ -33,8 +30,6 @@ private CustomerSampleMapper sampleMapper; @Autowired private MeterFixedAssetsMapper meterFixedAssetsMapper; - @Autowired - private ForkJoinPool forkJoinPool; public static SerialPort serialPort = null; @@ -73,21 +68,13 @@ } private List queryGunReadSampleList(Set gunSet) { - QueryWrapper wrapper = new QueryWrapper<>(); - wrapper.eq("is_del", 0); - wrapper.in("sample_no", gunSet); - List customerSampleInfos = sampleMapper.selectList(wrapper); - List resultList = ConvertUtils.sourceToTarget(customerSampleInfos, ReadSampleDTO.class); - return resultList; + List readSampleDTOS = sampleMapper.selectListForReadWriter(gunSet, null); + return readSampleDTOS; } private List queryReadSampleList(Set tIds) { - QueryWrapper wrapper = new QueryWrapper<>(); - wrapper.eq("is_del", 0); - wrapper.in("label_bind", tIds); - List customerSampleInfos = sampleMapper.selectList(wrapper); - List resultList = ConvertUtils.sourceToTarget(customerSampleInfos, ReadSampleDTO.class); - return resultList; + List readSampleDTOS = sampleMapper.selectListForReadWriter(null, tIds); + return readSampleDTOS; } // @Override diff --git a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/workbench/WorkbenchApprovalMessageServiceImpl.java b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/workbench/WorkbenchApprovalMessageServiceImpl.java index 6d7aad6..d3d3a5d 100644 --- a/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/workbench/WorkbenchApprovalMessageServiceImpl.java +++ b/casic-metering-service/src/main/java/com/casic/missiles/service/Impl/workbench/WorkbenchApprovalMessageServiceImpl.java @@ -127,7 +127,7 @@ enrichSkipParams(approvalMessage); } } - return finalList; + return finalList.stream().filter(message -> message.getDel() == 0).collect(Collectors.toList()); } private void enrichSkipParams(WorkbenchApprovalMessage approvalMessage) { @@ -138,18 +138,25 @@ if(!CollectionUtils.isEmpty(meterFiles)){ FileApprovalListResponse fileApprovalListResponse = ConvertUtils.sourceToTarget(meterFiles.get(0), FileApprovalListResponse.class); approvalMessage.setSkipParamsObject(fileApprovalListResponse); + }else { + //查询为空说明待审批在flowable中残留，在业务表中已删除 + approvalMessage.setDel(1); } }else if(ApplyFromIdEnum.TRAIN_APPROVAL.getCode().equals(approvalMessage.getFormId()) && approvalMessage.getMessageTitle().contains("待审批")) { List meterTrainPlans = trainPlanMapper.selectBatchForApproval(PageFactory.defaultPage(), new TrainPlanApprovalListRequest(), businessIds); if(!CollectionUtils.isEmpty(meterTrainPlans)){ TrainPlanApprovalListResponse trainPlanApprovalListResponse = ConvertUtils.sourceToTarget(meterTrainPlans.get(0), TrainPlanApprovalListResponse.class); approvalMessage.setSkipParamsObject(trainPlanApprovalListResponse); + }else { + approvalMessage.setDel(1); } }else if(ApplyFromIdEnum.STANDARD_EQUIPMENT.getCode().equals(approvalMessage.getFormId()) && approvalMessage.getMessageTitle().contains("待审批")) { List meterTraceSuppliers = traceSupplierMapper.selectBatchForApproval(PageFactory.defaultPage(), new TraceSupplierApprovalListRequest(), businessIds); if(!CollectionUtils.isEmpty(meterTraceSuppliers)){ TraceSupplierApprovalListResponse traceSupplierApprovalListResponse = ConvertUtils.sourceToTarget(meterTraceSuppliers.get(0), TraceSupplierApprovalListResponse.class); approvalMessage.setSkipParamsObject(traceSupplierApprovalListResponse); + }else { + approvalMessage.setDel(1); } } }