如何高效管理Spring Batch元数据：成功作业记录的自动清理策略

import org.springframework.batch.core.StepContribution;
import org.springframework.batch.core.scope.context.ChunkContext;
import org.springframework.batch.core.step.tasklet.Tasklet;
import org.springframework.batch.repeat.RepeatStatus;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;
import java.util.stream.Collectors;

public class CleanSpringBatchHistoryTasklet implements Tasklet {

    private final JdbcTemplate jdbcTemplate;
    private final int daysToRetain; // 保留天数

    public CleanSpringBatchHistoryTasklet(JdbcTemplate jdbcTemplate, int daysToRetain) {
        this.jdbcTemplate = jdbcTemplate;
        this.daysToRetain = daysToRetain;
    }

    @Override
    @Transactional
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception {
        LocalDateTime cutoffDate = LocalDateTime.now().minusDays(daysToRetain);

        // 1. 获取需要删除的成功作业执行ID
        List<Long> jobExecutionIdsToDelete = jdbcTemplate.queryForList(
                "SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION WHERE STATUS = 'COMPLETED' AND END_TIME < ?",
                Long.class, cutoffDate);

        if (jobExecutionIdsToDelete.isEmpty()) {
            System.out.println("No successful job executions to delete before " + cutoffDate);
            return RepeatStatus.FINISHED;
        }

        System.out.println("Found " + jobExecutionIdsToDelete.size() + " successful job executions to delete.");

        // 2. 删除相关子表记录 (注意删除顺序)
        // BATCH_STEP_EXECUTION_CONTEXT
        deleteRecords("DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN (SELECT STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (" + formatIds(jobExecutionIdsToDelete) + "))");
        // BATCH_JOB_EXECUTION_CONTEXT
        deleteRecords("DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (" + formatIds(jobExecutionIdsToDelete) + ")");
        // BATCH_JOB_EXECUTION_PARAMS
        deleteRecords("DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (" + formatIds(jobExecutionIdsToDelete) + ")");
        // BATCH_STEP_EXECUTION
        deleteRecords("DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (" + formatIds(jobExecutionIdsToDelete) + ")");
        // BATCH_JOB_EXECUTION
        deleteRecords("DELETE FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID IN (" + formatIds(jobExecutionIdsToDelete) + ")");

        // 3. （可选）删除 BATCH_JOB_INSTANCE。
        //    通常只有当某个JOB_INSTANCE的所有EXECUTION都被删除后，才考虑删除JOB_INSTANCE本身。
        //    这需要更复杂的逻辑来判断一个JOB_INSTANCE是否还有其他EXECUTION。
        //    为简化，本示例暂不直接删除 BATCH_JOB_INSTANCE。
        //    如果需要删除，可能需要先找到所有JOB_INSTANCE_ID，然后检查它们是否还有剩余的JOB_EXECUTION。

        System.out.println("Successfully deleted records for " + jobExecutionIdsToDelete.size() + " job executions.");
        return RepeatStatus.FINISHED;
    }

    private void deleteRecords(String sql) {
        int deletedRows = jdbcTemplate.update(sql);
        System.out.println("Executed: " + sql + " -> Deleted " + deletedRows + " rows.");
    }

    private String formatIds(List<Long> ids) {
        return ids.stream().map(String::valueOf).collect(Collectors.joining(","));
    }
}

import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
@EnableBatchProcessing
public class BatchCleanupJobConfig {

    @Autowired
    private JobBuilderFactory jobBuilderFactory;

    @Autowired
    private StepBuilderFactory stepBuilderFactory;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Bean
    public Job cleanupBatchHistoryJob() {
        return jobBuilderFactory.get("cleanupBatchHistoryJob")
                .incrementer(new RunIdIncrementer())
                .start(cleanupHistoryStep())
                .build();
    }

    @Bean
    public Step cleanupHistoryStep() {
        return stepBuilderFactory.get("cleanupHistoryStep")
                .tasklet(cleanSpringBatchHistoryTasklet())
                .build();
    }

    @Bean
    public CleanSpringBatchHistoryTasklet cleanSpringBatchHistoryTasklet() {
        // 配置保留30天内的成功作业数据
        return new CleanSpringBatchHistoryTasklet(jdbcTemplate, 30);
    }
}

-- 定义要保留的天数（例如，保留最近30天的数据）
SET @days_to_retain = 30;

-- 计算截止日期
SET @cutoff_date = DATE_SUB(NOW(), INTERVAL @days_to_retain DAY);

-- 查找所有需要删除的JOB_EXECUTION_ID
DROP TEMPORARY TABLE IF EXISTS temp_job_executions_to_delete;
CREATE TEMPORARY TABLE temp_job_executions_to_delete (
    JOB_EXECUTION_ID BIGINT PRIMARY KEY
);

INSERT INTO temp_job_executions_to_delete (JOB_EXECUTION_ID)
SELECT JOB_EXECUTION_ID
FROM BATCH_JOB_EXECUTION
WHERE STATUS = 'COMPLETED' AND END_TIME < @cutoff_date;

-- 如果没有需要删除的作业，则退出
SELECT COUNT(*) INTO @num_jobs_to_delete FROM temp_job_executions_to_delete;
IF @num_jobs_to_delete = 0 THEN
    SELECT 'No successful job executions to delete.';
ELSE
    SELECT CONCAT('Found ', @num_jobs_to_delete, ' successful job executions to delete before ', @cutoff_date);

    -- 1. 删除 BATCH_STEP_EXECUTION_CONTEXT
    DELETE FROM BATCH_STEP_EXECUTION_CONTEXT
    WHERE STEP_EXECUTION_ID IN (
        SELECT BSE.STEP_EXECUTION_ID
        FROM BATCH_STEP_EXECUTION BSE
        JOIN temp_job_executions_to_delete TJED ON BSE.JOB_EXECUTION_ID = TJED.JOB_EXECUTION_ID
    );
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_STEP_EXECUTION_CONTEXT.');

    -- 2. 删除 BATCH_JOB_EXECUTION_CONTEXT
    DELETE FROM BATCH_JOB_EXECUTION_CONTEXT
    WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM temp_job_executions_to_delete);
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_JOB_EXECUTION_CONTEXT.');

    -- 3. 删除 BATCH_JOB_EXECUTION_PARAMS
    DELETE FROM BATCH_JOB_EXECUTION_PARAMS
    WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM temp_job_executions_to_delete);
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_JOB_EXECUTION_PARAMS.');

    -- 4. 删除 BATCH_STEP_EXECUTION
    DELETE FROM BATCH_STEP_EXECUTION
    WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM temp_job_executions_to_delete);
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_STEP_EXECUTION.');

    -- 5. 删除 BATCH_JOB_EXECUTION
    DELETE FROM BATCH_JOB_EXECUTION
    WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM temp_job_executions_to_delete);
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_JOB_EXECUTION.');

    -- 6. （可选）删除 BATCH_JOB_INSTANCE
    -- 只有当一个JOB_INSTANCE的所有EXECUTION都被删除后，才考虑删除JOB_INSTANCE本身。
    -- 这个逻辑比较复杂，需要确保没有其他JOB_EXECUTION引用该JOB_INSTANCE。
    -- 以下是一个示例，仅删除那些所有执行都已被清理的JOB_INSTANCE。
    DELETE FROM BATCH_JOB_INSTANCE
    WHERE JOB_INSTANCE_ID IN (
        SELECT BJI.JOB_INSTANCE_ID
        FROM BATCH_JOB_INSTANCE BJI
        LEFT JOIN BATCH_JOB_EXECUTION BJE ON BJI.JOB_INSTANCE_ID = BJE.JOB_INSTANCE_ID
        WHERE BJE.JOB_INSTANCE_ID IS NULL -- 表示该JOB_INSTANCE已没有关联的JOB_EXECUTION
    );
    SELECT CONCAT('Deleted ', ROW_COUNT(), ' rows from BATCH_JOB_INSTANCE (if all executions were deleted).');

END IF;

DROP TEMPORARY TABLE IF EXISTS temp_job_executions_to_delete;