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.time.ZoneId;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

public class RemoveSuccessfulBatchHistoryTasklet implements Tasklet {

    private final JdbcTemplate jdbcTemplate;
    private final int retentionDays; // 例如，保留最近7天的数据

    public RemoveSuccessfulBatchHistoryTasklet(JdbcTemplate jdbcTemplate, int retentionDays) {
        this.jdbcTemplate = jdbcTemplate;
        this.retentionDays = retentionDays;
    }

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

        // 1. 查找符合条件的成功作业执行ID
        // 状态为COMPLETED且结束时间早于截止日期的作业执行
        String findJobExecutionsSql = "SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION " +
                                      "WHERE STATUS = 'COMPLETED' AND END_TIME < ?";
        List<Long> jobExecutionIds = jdbcTemplate.queryForList(findJobExecutionsSql, Long.class, cutoffDate);

        if (jobExecutionIds.isEmpty()) {
            System.out.println("No successful job executions found for deletion before " + cutoffDateTime);
            return RepeatStatus.FINISHED;
        }

        String inClause = jobExecutionIds.stream().map(String::valueOf).collect(Collectors.joining(","));

        System.out.println("Deleting " + jobExecutionIds.size() + " successful job executions before " + cutoffDateTime);

        // 2. 删除相关联的步骤执行上下文
        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 (" + inClause + "))";
        jdbcTemplate.update(deleteStepExecutionContextSql);

        // 3. 删除相关联的作业执行上下文
        String deleteJobExecutionContextSql = "DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (" + inClause + ")";
        jdbcTemplate.update(deleteJobExecutionContextSql);

        // 4. 删除相关联的作业执行参数
        String deleteJobExecutionParamsSql = "DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (" + inClause + ")";
        jdbcTemplate.update(deleteJobExecutionParamsSql);

        // 5. 删除相关联的步骤执行
        String deleteStepExecutionSql = "DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (" + inClause + ")";
        jdbcTemplate.update(deleteStepExecutionSql);

        // 6. 删除作业执行
        String deleteJobExecutionSql = "DELETE FROM BATCH_JOB_EXECUTION WHERE JOB_EXECUTION_ID IN (" + inClause + ")";
        jdbcTemplate.update(deleteJobExecutionSql);

        // 7. (可选) 删除作业实例 - 仅当该实例的所有执行都被删除后才能删除
        // 这通常需要更复杂的逻辑来检查一个JOB_INSTANCE_ID是否不再有任何关联的JOB_EXECUTION
        // 这里为了简化，不包含此部分，但在实际生产中可能需要考虑。
        // 例如：SELECT JOB_INSTANCE_ID FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION)

        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.batch.core.launch.support.RunIdIncrementer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

@Configuration
@EnableBatchProcessing
public class BatchCleanupJobConfig {

    private final JobBuilderFactory jobBuilderFactory;
    private final StepBuilderFactory stepBuilderFactory;
    private final JdbcTemplate jdbcTemplate;

    public BatchCleanupJobConfig(JobBuilderFactory jobBuilderFactory, StepBuilderFactory stepBuilderFactory, JdbcTemplate jdbcTemplate) {
        this.jobBuilderFactory = jobBuilderFactory;
        this.stepBuilderFactory = stepBuilderFactory;
        this.jdbcTemplate = jdbcTemplate;
    }

    @Bean
    public RemoveSuccessfulBatchHistoryTasklet removeSuccessfulBatchHistoryTasklet() {
        // 设置保留天数，例如：保留7天内的数据，删除7天前的成功数据
        return new RemoveSuccessfulBatchHistoryTasklet(jdbcTemplate, 7);
    }

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

    @Bean
    public Job batchCleanupJob() {
        return jobBuilderFactory.get("batchCleanupJob")
                .incrementer(new RunIdIncrementer()) // 每次运行都生成新的JobInstance
                .start(cleanupStep())
                .build();
    }
}

-- 定义截止日期，例如，删除7天前的成功作业数据
SET @cutoff_date = DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 1. 删除步骤执行上下文
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.END_TIME < @cutoff_date
);

-- 2. 删除作业执行上下文
DELETE FROM BATCH_JOB_EXECUTION_CONTEXT
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED' AND END_TIME < @cutoff_date
);

-- 3. 删除作业执行参数
DELETE FROM BATCH_JOB_EXECUTION_PARAMS
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED' AND END_TIME < @cutoff_date
);

-- 4. 删除步骤执行
DELETE FROM BATCH_STEP_EXECUTION
WHERE JOB_EXECUTION_ID IN (
    SELECT JOB_EXECUTION_ID
    FROM BATCH_JOB_EXECUTION
    WHERE STATUS = 'COMPLETED' AND END_TIME < @cutoff_date
);

-- 5. 删除作业执行
DELETE FROM BATCH_JOB_EXECUTION
WHERE STATUS = 'COMPLETED' AND END_TIME < @cutoff_date;

-- 6. (可选) 删除作业实例
-- 只有当一个JOB_INSTANCE_ID不再有任何关联的JOB_EXECUTION时才能删除
-- 这是一个更复杂的检查，通常需要确保没有剩余的JOB_EXECUTION与JOB_INSTANCE关联
DELETE FROM BATCH_JOB_INSTANCE
WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION);