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.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;

@Component
public class JobHistoryCleanupTasklet implements Tasklet {

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

    @Autowired
    public JobHistoryCleanupTasklet(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        // 示例：保留最近30天的成功作业记录
        this.daysToRetain = 30; 
    }

    @Override
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception {
        // 计算截止日期
        LocalDateTime cutoffDateTime = LocalDateTime.now().minusDays(daysToRetain);
        Date cutoffDate = Date.from(cutoffDateTime.atZone(ZoneId.systemDefault()).toInstant());

        // 获取需要删除的成功作业执行ID
        // 仅删除状态为COMPLETED且执行时间早于截止日期的作业
        String selectJobExecutionIdsSql = "SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION " +
                                          "WHERE STATUS = 'COMPLETED' AND START_TIME < ?";

        List<Long> jobExecutionIdsToDelete = jdbcTemplate.queryForList(selectJobExecutionIdsSql, Long.class, cutoffDate);

        if (jobExecutionIdsToDelete.isEmpty()) {
            System.out.println("没有需要清理的成功作业历史记录。");
            return RepeatStatus.FINISHED;
        }

        System.out.println("开始清理 " + jobExecutionIdsToDelete.size() + " 条成功作业历史记录...");

        // 批量删除，注意删除顺序以维护外键约束
        // 1. 删除 BATCH_STEP_EXECUTION_CONTEXT
        String deleteStepExecutionContextSql = "DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN " +
                                               "(SELECT STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (?))";
        // 2. 删除 BATCH_STEP_EXECUTION
        String deleteStepExecutionSql = "DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (?)";
        // 3. 删除 BATCH_JOB_EXECUTION_PARAMS
        String deleteJobExecutionParamsSql = "DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (?)";
        // 4. 删除 BATCH_JOB_EXECUTION_CONTEXT
        String deleteJobExecutionContextSql = "DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (?)";
        // 5. 删除 BATCH_JOB_EXECUTION
        String deleteJobExecutionSql = "DELETE FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID IN (?)";

        // 注意：BATCH_JOB_INSTANCE 的删除需要更复杂的逻辑，因为它可能关联多个 JOB_EXECUTION。
        // 通常，我们只删除那些不再有任何 JOB_EXECUTION 关联的 JOB_INSTANCE。
        // 简化示例中，我们只清理JOB_EXECUTION及相关联的子表。
        // 实际生产中，应先查询出所有JOB_INSTANCE_ID，然后检查它们是否还有其他JOB_EXECUTION关联，再决定是否删除。

        String jobExecutionIdsPlaceholder = jobExecutionIdsToDelete.stream()
                                                .map(String::valueOf)
                                                .collect(Collectors.joining(","));

        // 执行删除操作
        jdbcTemplate.update(deleteStepExecutionContextSql.replace("(?)", "(" + jobExecutionIdsPlaceholder + ")"));
        jdbcTemplate.update(deleteStepExecutionSql.replace("(?)", "(" + jobExecutionIdsPlaceholder + ")"));
        jdbcTemplate.update(deleteJobExecutionParamsSql.replace("(?)", "(" + jobExecutionIdsPlaceholder + ")"));
        jdbcTemplate.update(deleteJobExecutionContextSql.replace("(?)", "(" + jobExecutionIdsPlaceholder + ")"));
        int deletedCount = jdbcTemplate.update(deleteJobExecutionSql.replace("(?)", "(" + jobExecutionIdsPlaceholder + ")"));

        System.out.println("成功清理 " + deletedCount + " 条作业执行记录及其关联数据。");

        return RepeatStatus.FINISHED;
    }
}

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@EnableBatchProcessing
public class BatchCleanupJobConfig {

    private final JobBuilderFactory jobBuilderFactory;
    private final StepBuilderFactory stepBuilderFactory;
    private final JobHistoryCleanupTasklet cleanupTasklet;

    public BatchCleanupJobConfig(JobBuilderFactory jobBuilderFactory, 
                                 StepBuilderFactory stepBuilderFactory,
                                 JobHistoryCleanupTasklet cleanupTasklet) {
        this.jobBuilderFactory = jobBuilderFactory;
        this.stepBuilderFactory = stepBuilderFactory;
        this.cleanupTasklet = cleanupTasklet;
    }

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

    @Bean
    public Job jobHistoryCleanupJob() {
        return jobBuilderFactory.get("jobHistoryCleanupJob")
                .start(cleanupStep())
                .build();
    }
}

-- 定义保留天数，例如30天
SET @daysToRetain = 30; 

-- 计算截止日期
SET @cutoffDate = DATE_SUB(CURDATE(), INTERVAL @daysToRetain DAY);

-- 1. 删除 BATCH_STEP_EXECUTION_CONTEXT
DELETE FROM BATCH_STEP_EXECUTION_CONTEXT
WHERE STEP_EXECUTION_ID IN (
    SELECT SE.STEP_EXECUTION_ID
    FROM BATCH_STEP_EXECUTION SE
    JOIN BATCH_JOB_EXECUTION JE ON SE.JOB_EXECUTION_ID = JE.JOB_EXECUTION_ID
    WHERE JE.STATUS = 'COMPLETED'
      AND JE.START_TIME < @cutoffDate
);

-- 2. 删除 BATCH_STEP_EXECUTION
DELETE FROM BATCH_STEP_EXECUTION
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED'
      AND START_TIME < @cutoffDate
);

-- 3. 删除 BATCH_JOB_EXECUTION_PARAMS
DELETE FROM BATCH_JOB_EXECUTION_PARAMS
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED'
      AND START_TIME < @cutoffDate
);

-- 4. 删除 BATCH_JOB_EXECUTION_CONTEXT
DELETE FROM BATCH_JOB_EXECUTION_CONTEXT
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED'
      AND START_TIME < @cutoffDate
);

-- 5. 删除 BATCH_JOB_EXECUTION
DELETE FROM BATCH_JOB_EXECUTION
WHERE STATUS = 'COMPLETED'
  AND START_TIME < @cutoffDate;

-- 6. 删除不再有任何 JOB_EXECUTION 关联的 BATCH_JOB_INSTANCE
-- 这一步需要非常谨慎，确保没有活跃的JOB_EXECUTION关联到JOB_INSTANCE
DELETE FROM BATCH_JOB_INSTANCE
WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION);