Java 每日自动备份指定表脚本

package com.ruoyi.xxljob.task.backup;  
  
import org.springframework.jdbc.core.JdbcTemplate;  
import org.springframework.scheduling.annotation.Scheduled;  
import org.springframework.stereotype.Service;  
  
import javax.annotation.PostConstruct;  
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileOutputStream;  
import java.io.FileWriter;  
import java.sql.*;  
import java.time.LocalDate;  
import java.util.ArrayList;  
import java.util.List;  
import java.util.zip.ZipEntry;  
import java.util.zip.ZipOutputStream;  
  
import org.springframework.beans.factory.annotation.Value;  
  
import java.util.Arrays;  
import java.util.stream.Collectors;  
  
@Service  
public class MultiTableBackupService {  
  
    private final JdbcTemplate jdbcTemplate;  
  
    // 从配置文件读取参数  
    @Value("${backup.tables}")  
    private String tableNames;  // 用逗号分隔的表名列表  
  
    @Value("${backup.directory}")  
    private String backupDir;   // 备份目录  
  
    @Value("${backup.compression:false}")  
    private boolean enableCompression;  // 是否启用压缩，默认false  
  
    @Value("${backup.keep.days:30}")  
    private int keepDays;       // 保留最近多少天的备份，默认30天  
  
    public MultiTableBackupService(JdbcTemplate jdbcTemplate) {  
        this.jdbcTemplate = jdbcTemplate;  
    }  
  
    @PostConstruct  
    public void init() {  
        System.out.println("初始化多表备份服务，表名: " + tableNames);  
        System.out.println("备份目录: " + backupDir);  
        System.out.println("压缩启用: " + enableCompression);  
        System.out.println("备份保留天数: " + keepDays);  
    }  
  
    // 每天凌晨3点执行备份  
    @Scheduled(cron = "${backup.cron:0 0 3 * * ?}")  
    public void executeDailyBackup() {  
        List<String> tables = parseTableNames();  
        if (tables.isEmpty()) {  
            System.err.println("未配置需要备份的表名");  
            return;  
        }  
  
        // 获取当天日期并创建日期文件夹  
        LocalDate today = LocalDate.now();  
        String dailyFolder = backupDir + File.separator + today.toString();  
        File folder = new File(dailyFolder);  
        if (!folder.exists()) {  
            folder.mkdirs();  
        }  
  
        System.out.println("开始执行多表备份...");  
        for (String tableName : tables) {  
            try {  
                String sqlContent = generateCompleteSql(tableName);  
                // 文件名不再包含日期，放在日期文件夹中  
                String fileName = dailyFolder + File.separator + tableName + ".sql";  
  
                saveToFile(sqlContent, fileName);  
                System.out.println("表备份完成: " + fileName);  
            } catch (Exception e) {  
                System.err.println("表" + tableName + "备份失败: " + e.getMessage());  
                e.printStackTrace();  
            }  
        }  
  
        cleanOldBackups();  
    }  
  
    private List<String> parseTableNames() {  
        return Arrays.stream(tableNames.split(","))  
                .map(String::trim)  
                .filter(name -> !name.isEmpty())  
                .collect(Collectors.toList());  
    }  
  
    private String generateCompleteSql(String tableName) throws SQLException {  
        StringBuilder sql = new StringBuilder();  
  
        // 1. 生成表结构SQL  
        sql.append("-- Table structure for ").append(tableName).append("\n");  
        sql.append(generateCreateTableSql(tableName)).append("\n\n");  
  
        // 2. 生成数据插入SQL  
        sql.append("-- Data for ").append(tableName).append("\n");  
        sql.append(generateInsertDataSql(tableName));  
  
        return sql.toString();  
    }  
  
    private String generateCreateTableSql(String tableName) throws SQLException {  
        StringBuilder createSql = new StringBuilder();  
        Connection conn = jdbcTemplate.getDataSource().getConnection();  
  
        try {  
            DatabaseMetaData meta = conn.getMetaData();  
  
            // 获取表结构  
            createSql.append("DROP TABLE IF EXISTS `").append(tableName).append("`;\n");  
            createSql.append("CREATE TABLE `").append(tableName).append("` (\n");  
  
            // 获取列信息  
            List<String> columns = new ArrayList<>();  
            try (ResultSet rs = meta.getColumns(null, null, tableName, null)) {  
                while (rs.next()) {  
                    String colName = rs.getString("COLUMN_NAME");  
                    String type = rs.getString("TYPE_NAME");  
                    int size = rs.getInt("COLUMN_SIZE");  
                    int nullable = rs.getInt("NULLABLE");  
                    String defValue = rs.getString("COLUMN_DEF");  
  
                    StringBuilder colDef = new StringBuilder("  `").append(colName).append("` ").append(type);  
                    if (size > 0 && (type.equals("VARCHAR") || type.equals("CHAR") || type.equals("DECIMAL"))) {  
                        colDef.append("(").append(size).append(")");  
                    }  
                    if (nullable == DatabaseMetaData.columnNoNulls) {  
                        colDef.append(" NOT NULL");  
                    }  
                    if (defValue != null) {  
                        colDef.append(" DEFAULT ").append(defValue);  
                    }  
                    columns.add(colDef.toString());  
                }  
            }  
  
            // 获取主键  
            List<String> pks = new ArrayList<>();  
            try (ResultSet rs = meta.getPrimaryKeys(null, null, tableName)) {  
                while (rs.next()) {  
                    pks.add(rs.getString("COLUMN_NAME"));  
                }  
            }  
  
            createSql.append(String.join(",\n", columns));  
            if (!pks.isEmpty()) {  
                createSql.append(",\n  PRIMARY KEY (`").append(String.join("`, `", pks)).append("`)");  
            }  
            createSql.append("\n);");  
  
        } finally {  
            conn.close();  
        }  
  
        return createSql.toString();  
    }  
  
    private String generateInsertDataSql(String tableName) {  
        StringBuilder inserts = new StringBuilder();  
  
        jdbcTemplate.query("SELECT * FROM " + tableName, (rs) -> {  
            ResultSetMetaData meta = rs.getMetaData();  
            int colCount = meta.getColumnCount();  
  
            while (rs.next()) {  
                inserts.append("INSERT INTO `").append(tableName).append("` VALUES (");  
  
                List<String> values = new ArrayList<>();  
                for (int i = 1; i <= colCount; i++) {  
                    Object value = rs.getObject(i);  
                    if (value == null) {  
                        values.add("NULL");  
                    } else if (value instanceof Number) {  
                        values.add(value.toString());  
                    } else if (value instanceof Boolean) {  
                        values.add(((Boolean) value) ? "1" : "0");  
                    } else {  
                        values.add("'" + value.toString().replace("'", "''") + "'");  
                    }  
                }  
  
                inserts.append(String.join(", ", values));  
                inserts.append(");\n");  
            }  
            return null;  
        });  
  
        return inserts.toString();  
    }  
  
    private void saveToFile(String content, String filePath) throws Exception {  
        try (FileWriter writer = new FileWriter(filePath)) {  
            writer.write(content);  
        }  
  
        if (enableCompression) {  
            compressFile(filePath);  
            new File(filePath).delete(); // 删除原始SQL文件  
        }  
    }  
  
    private void compressFile(String filePath) throws Exception {  
        String zipPath = filePath.replace(".sql", ".zip");  
        try (FileOutputStream fos = new FileOutputStream(zipPath);  
             ZipOutputStream zos = new ZipOutputStream(fos);  
             FileInputStream fis = new FileInputStream(filePath)) {  
  
            zos.putNextEntry(new ZipEntry(new File(filePath).getName()));  
            byte[] buffer = new byte[1024];  
            int len;  
            while ((len = fis.read(buffer)) > 0) {  
                zos.write(buffer, 0, len);  
            }  
            zos.closeEntry();  
        }  
    }  
  
    private void cleanOldBackups() {  
        File dir = new File(backupDir);  
        if (!dir.exists()) return;  
  
        long cutoff = System.currentTimeMillis() - (keepDays * 24L * 60 * 60 * 1000);  
  
        // 获取所有日期文件夹  
        File[] dateFolders = dir.listFiles((d, name) -> {  
            try {  
                LocalDate.parse(name);  // 尝试解析为日期  
                return true;  
            } catch (Exception e) {  
                return false;  
            }  
        });  
  
        if (dateFolders != null) {  
            for (File folder : dateFolders) {  
                if (folder.lastModified() < cutoff) {  
                    deleteFolder(folder);  
                    System.out.println("删除过期备份文件夹: " + folder.getName());  
                }  
            }  
        }  
    }  
  
    private void deleteFolder(File folder) {  
        File[] files = folder.listFiles();  
        if (files != null) {  
            for (File file : files) {  
                file.delete();  
            }  
        }  
        folder.delete();  
    }  
}

backup:  
  compression: false  
  cron: 0 0 1 * * ?  
  directory: /Users/xxx/IdeaProjects/backup/sql  
  keep:  
    days: 60  
  tables: table_names