package com.example.server.DataSync.service;
|
|
import com.example.Application;
|
import com.example.client.utils.WaitUtil;
|
import com.example.server.DataSync.dto.IDUpdateDateDto;
|
import com.example.server.DataSync.dto.FieldTypeDto;
|
import com.example.server.mysql.dao.SysMysqlDao;
|
import com.example.server.utils.CacheUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Service;
|
|
import javax.swing.*;
|
import java.io.*;
|
|
import java.nio.file.*;
|
import java.nio.file.attribute.BasicFileAttributes;
|
import java.sql.*;
|
import java.text.SimpleDateFormat;
|
import java.time.LocalDate;
|
import java.time.format.DateTimeFormatter;
|
import java.util.*;
|
import java.util.Date;
|
import java.util.zip.ZipEntry;
|
import java.util.zip.ZipOutputStream;
|
|
@Service
|
public class DataSyncService {
|
@Autowired
|
protected SysMysqlDao sysMysqlDao;
|
|
@Value("${data.config-path}")
|
private String configPath2;
|
@Value("${zt.oss.local-path}")
|
private String localPath;
|
@Value("${zt.oss.Unzip-path}")
|
private String UnzipPath;
|
@Value("${data.imgDir}")
|
private String imgPath;
|
@Value("${spring.datasource.url}")
|
private String datasource;
|
|
public void export() {
|
String[] parts = datasource.split(":", 3);
|
// 定义文件路径
|
String dbPath = parts[2];
|
String configPath = configPath2;
|
String directoryPath = localPath + "life-protection";
|
|
String mySite = (String) CacheUtils.get("site", "site");
|
|
// 弹出文件选择框，让用户选择保存ZIP文件的路径
|
String outputZipPath = showSaveFileDialog(mySite + "同步数据包.zip");
|
|
if (outputZipPath == null) {
|
System.out.println("用户取消保存，程序退出。");
|
}else{
|
final WaitUtil waitUtil = new WaitUtil(imgPath, "数据正在导入，请稍候");
|
|
// 开始上传文件的异步任务
|
SwingWorker<String, Void> sw = new SwingWorker<String, Void>() {
|
@Override
|
protected String doInBackground() throws Exception {
|
String flag = zipData(dbPath,configPath,directoryPath,outputZipPath);
|
return flag;
|
}
|
|
@Override
|
protected void done() {
|
try {
|
// 获取上传结果（在这个例子中，我们假设上传方法返回Boolean类型）
|
String uploadSucceeded = get();
|
|
// 刷新表格数据（如果上传成功）
|
if (uploadSucceeded.equals("true")) {
|
waitUtil.dispose();
|
System.out.println("导出成功时间" + new Date());
|
} else {
|
waitUtil.dispose();
|
JOptionPane.showMessageDialog(null, uploadSucceeded, "提示", JOptionPane.ERROR_MESSAGE);
|
}
|
|
} catch (Exception ex) {
|
ex.printStackTrace();
|
waitUtil.dispose();
|
}
|
}
|
};
|
// 执行异步任务
|
sw.execute();
|
waitUtil.setVisible(true);
|
}
|
}
|
|
public String zipData(String dbPath,String configPath,String directoryPath,String outputZipPath){
|
// 创建ZIP文件输出流
|
FileOutputStream fos = null;
|
ZipOutputStream zos = null;
|
try {
|
fos = new FileOutputStream(outputZipPath);
|
zos = new ZipOutputStream(new BufferedOutputStream(fos));
|
// 添加SQLite数据库文件
|
addFileToZip(dbPath, zos, "csiczb.db");
|
|
// 添加配置文件
|
addFileToZip(configPath, zos, "config.properties");
|
|
// 添加目录
|
addDirectoryToZip(directoryPath, zos, "life-protection/");
|
|
System.out.println("文件压缩完成，输出路径：" + outputZipPath);
|
} catch (IOException e) {
|
e.printStackTrace();
|
return "false";
|
} finally {
|
try {
|
zos.close();
|
fos.close();
|
} catch (IOException exception) {
|
exception.printStackTrace();
|
}
|
}
|
return "true";
|
}
|
|
public void importData() {
|
InputStream inStream = null;
|
Properties properties = new Properties();
|
try {
|
Path sourceDir = Paths.get(UnzipPath + "life-protection");
|
Path targetDir = Paths.get(localPath + "life-protection");
|
|
// 如果目标文件夹不存在，创建它
|
if (!Files.exists(targetDir)) {
|
Files.createDirectories(targetDir);
|
}
|
|
// 递归复制文件夹
|
copyDirectory(sourceDir, targetDir);
|
System.out.println("文件夹复制完成，目标路径：" + targetDir);
|
|
//读取配置
|
inStream = new FileInputStream(UnzipPath + "config.properties");
|
properties.load(inStream);
|
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
|
String mySite = (String) CacheUtils.get("site", "site");
|
String otherSite = properties.get("site").toString();
|
if ("工作组".equals(mySite)) {
|
syncTable("dj_jdgz_handover");
|
syncTable("dj_jdgz_network_level3");
|
syncTable("dj_jdgz_network_level3_list");
|
syncTable("dj_jdgz_track_record");
|
syncTable("dj_sys_oss");
|
syncTable("dj_jdgz_dismant_track");
|
} else if ("TD".equals(mySite)) {
|
if ("TD".equals(otherSite)) {
|
syncTable("dj_jdgz_network_level3");
|
syncTable("dj_jdgz_network_level3_list");
|
syncTable("dj_jdgz_track_record");
|
syncTable("dj_sys_oss");
|
syncTable("dj_jdgz_dismant_track");
|
} else if ("工作组".equals(otherSite)) {
|
syncTable("dj_jdgz_handover");
|
syncTable("dj_jdgz_network_level1");
|
syncTable("dj_jdgz_network_level1_list");
|
syncTable("dj_jdgz_network_level2");
|
syncTable("dj_jdgz_network_level2_list");
|
syncTable("dj_jdgz_network_level3");
|
syncTable("dj_jdgz_network_level3_list");
|
syncTable("dj_jdgz_track_record");
|
syncTable("dj_sys_oss");
|
syncTable("dj_jdgz_dismant_track");
|
}
|
} else if ("厂家".equals(mySite)) {
|
syncTable("dj_jdgz_handover");
|
syncTable("dj_jdgz_network_level1");
|
syncTable("dj_jdgz_network_level1_list");
|
syncTable("dj_jdgz_network_level2");
|
syncTable("dj_jdgz_network_level2_list");
|
syncTable("dj_jdgz_network_level3");
|
syncTable("dj_jdgz_network_level3_list");
|
syncTable("dj_jdgz_track_record");
|
syncTable("dj_sys_oss");
|
syncTable("dj_jdgz_dismant_track");
|
}
|
}
|
|
public void syncTable(String tableName) {
|
Connection connection = null;
|
List<IDUpdateDateDto> mydataList = sysMysqlDao.getIdUpdateDateList("select * from " + tableName); // 从MySQL获取数据
|
Map<Long, String> myIdUpdateDateMap = new HashMap<>();
|
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
|
for (IDUpdateDateDto dto : mydataList) {
|
myIdUpdateDateMap.put(dto.getId(), dto.getUpdateDate());
|
}
|
// 定义日期格式
|
|
try {
|
// 加载驱动程序
|
Class.forName("org.sqlite.JDBC");
|
// 创建数据库连接
|
connection = DriverManager.getConnection("jdbc:sqlite:" + UnzipPath + "csiczb.db");
|
System.out.println("数据库连接成功！");
|
|
// 检查表是否存在
|
String checkTableQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name=LOWER(?)";
|
PreparedStatement checkStmt = connection.prepareStatement(checkTableQuery);
|
checkStmt.setString(1, tableName.toLowerCase());
|
ResultSet tableExists = checkStmt.executeQuery();
|
if (!tableExists.next()) {
|
throw new SQLException("表 " + tableName + " 不存在！");
|
}
|
tableExists.close();
|
checkStmt.close();
|
|
DatabaseMetaData metaData = connection.getMetaData();
|
ResultSet colRet = metaData.getColumns(null, "%", tableName, "%");
|
// 将SQLite中的数据存储到Map中，便于后续比较
|
List<FieldTypeDto> fieldTypeList = new ArrayList<>();
|
String columnsStr = null;
|
while (colRet.next()) {
|
String quotation = "";
|
if (colRet.getString("TYPE_NAME").contains("TEXT"))
|
quotation = "'";
|
columnsStr = columnsStr == null ? colRet.getString("COLUMN_NAME") : columnsStr + "," + colRet.getString("COLUMN_NAME");
|
fieldTypeList.add(new FieldTypeDto(colRet.getString("COLUMN_NAME"), colRet.getString("TYPE_NAME"), quotation));
|
}
|
colRet.close();
|
|
// 查询SQLite表中的所有数据
|
String otherDataSql = "SELECT * FROM " + tableName;
|
PreparedStatement otherDataStmt = connection.prepareStatement(otherDataSql);
|
ResultSet otherDataRs = otherDataStmt.executeQuery();
|
|
// 遍历dataList，比较MySQL和SQLite中的数据
|
String allInsertSql = null;
|
|
while (otherDataRs.next()) {
|
Boolean isUpdate = false;
|
Long id = otherDataRs.getLong("ID");
|
if (myIdUpdateDateMap.get(id) != null) {
|
String otherupdateDate = otherDataRs.getString("UPDATE_DATE");
|
String myupdateDate = myIdUpdateDateMap.get(id);
|
java.util.Date mysqlUpdateDate = dateFormat.parse(myupdateDate);
|
java.util.Date otherUpdateDate = dateFormat.parse(otherupdateDate);
|
if (otherUpdateDate.after(mysqlUpdateDate)) {
|
isUpdate = true;
|
}else {
|
continue;
|
}
|
}
|
String insertSql = null;
|
String updateSql = null;
|
for (FieldTypeDto fieldType : fieldTypeList) {
|
String value = otherDataRs.getString(fieldType.getFieldName());
|
if (value != null)
|
value = fieldType.getQuotation() + otherDataRs.getString(fieldType.getFieldName()) + fieldType.getQuotation();
|
if (isUpdate) {
|
String fieldValue = fieldType.getFieldName() + "=" + value;
|
updateSql = updateSql == null ? fieldValue : updateSql + "," + fieldValue;
|
} else {
|
insertSql = insertSql == null ? value : insertSql + "," + value;
|
}
|
}
|
if (isUpdate) {
|
updateSql = "update " + tableName + " set " + updateSql + "where id = " + id;
|
// 执行update
|
sysMysqlDao.execute(updateSql);
|
} else {
|
insertSql = "(" + insertSql + ")";
|
allInsertSql = allInsertSql == null ? insertSql : allInsertSql + "," + insertSql;
|
}
|
}
|
if (allInsertSql != null) {
|
// insert
|
allInsertSql = "INSERT INTO " + tableName + " (" + columnsStr + ") VALUES " + allInsertSql;
|
sysMysqlDao.execute(allInsertSql);
|
}
|
System.out.println("数据同步完成！");
|
} catch (Exception e) {
|
System.err.println(e.getClass().getName() + ": " + e.getMessage());
|
} finally {
|
// 关闭数据库连接
|
if (connection != null) {
|
try {
|
connection.close();
|
} catch (Exception e) {
|
System.err.println(e.getClass().getName() + ": " + e.getMessage());
|
}
|
}
|
}
|
}
|
|
private static String showSaveFileDialog(String defaultFileName) {
|
JFileChooser fileChooser = new JFileChooser();
|
fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
|
fileChooser.setDialogTitle("选择保存ZIP文件的位置");
|
fileChooser.setSelectedFile(new File(defaultFileName)); // 设置默认文件名
|
|
int result = fileChooser.showSaveDialog(null);
|
if (result == JFileChooser.APPROVE_OPTION) {
|
return fileChooser.getSelectedFile().getAbsolutePath();
|
} else {
|
return null;
|
}
|
}
|
|
private static void addFileToZip(String filePath, ZipOutputStream zos, String entryName) throws IOException {
|
File file = new File(filePath);
|
if (!file.exists()) {
|
InputStream fis = Application.class.getClassLoader().getResourceAsStream(filePath);
|
try {
|
ZipEntry zipEntry = new ZipEntry(entryName);
|
zos.putNextEntry(zipEntry);
|
|
byte[] buffer = new byte[1024];
|
int length;
|
while ((length = fis.read(buffer)) > 0) {
|
zos.write(buffer, 0, length);
|
}
|
} catch (IOException exception) {
|
exception.printStackTrace();
|
} finally {
|
fis.close();
|
}
|
} else {
|
FileInputStream fis = new FileInputStream(file);
|
try {
|
ZipEntry zipEntry = new ZipEntry(entryName);
|
zos.putNextEntry(zipEntry);
|
|
byte[] buffer = new byte[1024];
|
int length;
|
while ((length = fis.read(buffer)) > 0) {
|
zos.write(buffer, 0, length);
|
}
|
} catch (IOException exception) {
|
exception.printStackTrace();
|
} finally {
|
fis.close();
|
}
|
}
|
}
|
|
private static void addDirectoryToZip(String directoryPath, ZipOutputStream zos, String parentEntry) throws IOException {
|
File directory = new File(directoryPath);
|
if (!directory.exists() || !directory.isDirectory()) {
|
System.err.println("目录不存在或不是一个目录：" + directoryPath);
|
return;
|
}
|
|
// 获取当前日期和三个月前的日期
|
LocalDate today = LocalDate.now();
|
LocalDate threeMonthsAgo = today.minusMonths(3);
|
|
File[] files = directory.listFiles();
|
if (files != null) {
|
for (File file : files) {
|
String entryName = parentEntry + file.getName();
|
if (file.isDirectory()) {
|
// 检查文件夹名称是否符合日期格式且在当前三个月内
|
if (isRecentDirectory(file.getName(), threeMonthsAgo)) {
|
addDirectoryToZip(file.getAbsolutePath(), zos, entryName + "/");
|
}
|
} else {
|
// 添加文件
|
addFileToZip(file.getAbsolutePath(), zos, entryName);
|
}
|
}
|
}
|
}
|
|
/**
|
* 检查文件夹名称是否符合日期格式且在当前三个月内
|
*
|
* @param directoryName 文件夹名称
|
* @param threeMonthsAgo 三个月前的日期
|
* @return 如果文件夹名称符合日期格式且在当前三个月内，返回true
|
*/
|
private static boolean isRecentDirectory(String directoryName, LocalDate threeMonthsAgo) {
|
try {
|
// 解析文件夹名称为日期
|
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
|
LocalDate date = LocalDate.parse(directoryName, formatter);
|
|
// 检查日期是否在当前三个月内
|
return date.isAfter(threeMonthsAgo);
|
} catch (Exception e) {
|
// 如果解析失败，说明文件夹名称不符合日期格式，跳过
|
return false;
|
}
|
}
|
|
|
private static void copyDirectory(Path source, Path target) throws IOException {
|
// 使用Files.walkFileTree递归遍历文件夹
|
Files.walkFileTree(source, new SimpleFileVisitor<Path>() {
|
@Override
|
public FileVisitResult preVisitDirectory(Path dir, BasicFileAttributes attrs) throws IOException {
|
// 在目标路径中创建对应的目录
|
Path targetDir = target.resolve(source.relativize(dir));
|
if (!Files.exists(targetDir)) {
|
Files.createDirectory(targetDir);
|
}
|
return FileVisitResult.CONTINUE;
|
}
|
|
@Override
|
public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {
|
// 复制文件到目标路径，如果目标文件已存在则覆盖
|
Path targetFile = target.resolve(source.relativize(file));
|
Files.copy(file, targetFile, StandardCopyOption.REPLACE_EXISTING);
|
return FileVisitResult.CONTINUE;
|
}
|
});
|
}
|
}
|
