Spring批量表清除

Question

清除Spring表的最佳方法是什么？

Spring是否提供任何用于清除的API？或者，我们是否需要对所有Spring批处理表执行delete语句？

Answer 1

我创建了一个过程来在给定日期之前清理数据。

    DELIMITER $$

DROP PROCEDURE IF EXISTS purgeJobsTableBefore 
$$

CREATE PROCEDURE purgeJobsTableBefore (
    IN createTime VARCHAR(100)
)
BEGIN

DELETE
FROM
   batch_job_execution_context 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_job_execution_params 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_step_execution_context 
WHERE
   step_execution_id IN 
   (
      SELECT
         step_execution_id 
      FROM
         batch_step_execution 
      WHERE
         JOB_EXECUTION_ID IN 
         (
            SELECT
               JOB_EXECUTION_ID 
            FROM
               batch_job_execution 
            WHERE
               create_time <= createTime
         )
   );

DELETE
FROM
   batch_step_execution 
WHERE
   JOB_EXECUTION_ID IN 
   (
      SELECT
         JOB_EXECUTION_ID 
      FROM
         batch_job_execution 
      WHERE
         create_time <= createTime
   );

DELETE
FROM
   batch_job_execution 
WHERE
   create_time <= createTime;

DELETE
FROM
   batch_job_instance 
WHERE
   JOB_INSTANCE_ID < (
   SELECT
      MIN(JOB_INSTANCE_ID) 
   FROM
      batch_job_execution 
   WHERE
      create_time >= createTime);

END;
$$

您只需调用带有日期的过程即可在该日期之前清除数据。

CALL purgeJobsTableBefore('2020-05-22');

Answer 2

另一个选项是设置批次。数据来源init=true在属性中，这意味着在每个新部署上删除并重新创建表。。

Answer 3

我已经为此挣扎了一段时间，但没有标准的实现。

然后我想出了一个我自己的存储过程，

我已经创建了自己的变量-用于在六个月之前清除最近六个月的数据\u六个月\u日期

你可以利用自己的价值。

解决方案位于以下链接-

Spring批处理元数据表清除