Spring Boot中如何用JPQL/原生查询删除Postgres7天前旧数据

在Spring Boot应用中使用PostgreSQL作为数据库，需要清理activity表中created_at字段时间早于7天的历史数据，由于没有现成的JPA方法直接支持，编写了如下原生查询：

@Query(nativeQuery = true, value = "DELETE FROM activity WHERE created_date < (NOW() - INTERVAL 7 DAY)")
void deleteActivitiesOlderThanOneWeek();

执行后抛出如下语法错误：

org.postgresql.util.PSQLException: ERROR: syntax error at or near "7"
  Position: 61
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[postgresql-42.3.3.jar:42.3.3]
    // 省略其余栈信息

一共存在3个核心问题：

• 时间间隔语法不兼容PostgreSQL：INTERVAL 7 DAY是MySQL的方言写法，PostgreSQL中INTERVAL的参数需要用单引号包裹，原写法直接写数字7会被判定为语法错误，这是当前报错的直接诱因。
• 缺少JPA修改操作必要注解：Spring Data JPA默认将@Query标注的方法当做查询操作处理，执行DELETE/UPDATE这类修改语句时，必须添加@Modifying注解标识，否则框架会调用查询接口执行删除逻辑，即使SQL语法正确也会抛出操作类型不匹配异常。
• 字段名笔误：需求中明确判断的字段是created_at，但SQL中写的是created_date，如果表结构实际字段为created_at，修正语法后还会触发字段不存在的错误。

修正后的Repository层方法写法如下：

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;
import java.time.LocalDateTime;

public interface ActivityRepository extends CrudRepository<Activity, Long> {

    @Modifying
    // 事务注解也可以加在调用该方法的Service层业务方法上
    @Transactional
    @Query(nativeQuery = true, value = "DELETE FROM activity WHERE created_at < NOW() - INTERVAL '7 days'")
    void deleteActivitiesOlderThanOneWeek();
}

如果需要避免绑定数据库方言，也可以改用JPQL写法，不依赖数据库原生语法，兼容性更强：

@Modifying
@Transactional
@Query("DELETE FROM Activity a WHERE a.createdAt < :thresholdTime")
void deleteActivitiesOlderThanOneWeek(LocalDateTime thresholdTime);
// 调用时直接计算7天前的时间传入即可：LocalDateTime.now().minusDays(7)

额外优化建议 #

如果activity表数据量较大，直接执行全量DELETE会长时间持有表锁，影响线上业务写入，建议采用分批删除的方式，每次删除固定条数（比如1000条）循环执行，直到没有符合条件的数据为止；如果是长期固定的清理需求，也可以通过PostgreSQL的定时任务配合表分区机制，实现过期数据的自动清理，减少应用层逻辑压力。

内容的提问来源于stack exchange，提问作者Steve Oluwatosin Olaleye