基于JPA实现多表关联删除：符合Deletion表条件的用户数据清理

需求概述 #

现有三张MySQL表：user用户表、deletion删除记录表、user_role用户角色关联表，需实现以下逻辑：

• 当deletion表中active字段为false且date字段已超过24小时时，删除该deletion记录
• 同时删除对应的user表记录
• 由于user与user_role存在关联约束，必须先删除user_role中的关联记录，才能删除user记录
• 目前已实现DeletionRepository中的单表删除方法，需完善多表关联删除逻辑

表结构与测试数据 #

user用户表 #

+----+----------------------------+-------------+
| id | date_created_user          | email       |
+----+----------------------------+-------------+
|  7 | 2023-02-23 13:23:09.085897 | www@www.www |
| 16 | 2023-02-25 14:23:31.691560 | qqq@qqq.qqq |
| 17 | 2023-02-25 14:24:02.089010 | aaa@aaa.aaa |
| 18 | 2023-02-25 14:24:24.708500 | xxx@xxx.xxx |
| 19 | 2023-02-25 14:25:19.253770 | ooo@ooo.ooo |
+----+----------------------------+-------------+

user表结构详情

+-------------------------+-------------+------+-----+---------+----------------+
| Field                   | Type        | Null | Key | Default | Extra          |
+-------------------------+-------------+------+-----+---------+----------------+
| id                      | bigint      | NO   | PRI | NULL    | auto_increment |
| email                   | varchar(58) | NO   | UNI | NULL    |                |
| enabled                 | bit(1)      | NO   |     | NULL    |                |
| password                | varchar(65) | NO   |     | NULL    |                |
| token                   | varchar(45) | YES  | UNI | NULL    |                |
+-------------------------+-------------+------+-----+---------+----------------+

deletion删除记录表 #

+----+----------------+----------------------------+---------+
| id | active         | date                       | user_id |
+----+----------------+----------------------------+---------+
| 10 | false          | 2023-02-25 14:23:31.691560 |      16 |
| 11 | false          | 2023-02-25 14:24:02.089010 |      17 |
| 12 | true           | 2023-02-25 14:24:24.708500 |      18 |
| 13 | true           | 2023-02-25 14:25:19.253770 |      19 |
+----+----------------+----------------------------+---------+

deletion表结构详情

+--------------------+-------------+------+-----+---------+----------------+
| Field              | Type        | Null | Key | Default | Extra          |
+--------------------+-------------+------+-----+---------+----------------+
| id                 | bigint      | NO   | PRI | NULL    | auto_increment |
| active             | bit(1)      | NO   |     | NULL    |                |
| date               | datetime(6) | NO   |     | NULL    |                |
| user_id            | bigint      | YES  | MUL | NULL    |                |
+--------------------+-------------+------+-----+---------+----------------+

user_role用户角色关联表 #

+---------+---------------+
| user_id | role_id       |
+---------+---------------+
|       7 |            1  |
|      16 |            2  |
|      17 |            2  |
|      18 |            2  |
|      19 |            2  |
+---------+---------------+

user_role表结构详情

+---------------+--------+------+-----+---------+-------+
| Field         | Type   | Null | Key | Default | Extra |
+---------------+--------+------+-----+---------+-------+
| user_id       | bigint | NO   | PRI | NULL    |       |
| role_id       | bigint | NO   | PRI | NULL    |       |
+---------------+--------+------+-----+---------+-------+

现有Repository代码 #

@Transactional(readOnly = true)
@Repository
public interface DeletionRepository extends JpaRepository<Deletion, Long> {

    @Transactional
    @Modifying
    @Query("DELETE FROM Deletion as a WHERE a.active = false AND a.date <= :date")
    void deleteDeletionByActiveAndDate(@Param("date") String date);

}

多表关联删除实现方案 #

方案1：JPQL批量删除（类型安全） #

由于JPA批量删除不会触发实体级联操作，需按约束顺序手动执行：

1. 先删除user_role中符合条件的关联记录
2. 再删除对应的user记录
3. 最后删除deletion表中的过期记录

扩展DeletionRepository方法

@Transactional
@Modifying
@Query("DELETE FROM UserRole ur WHERE ur.userId IN (SELECT d.userId FROM Deletion d WHERE d.active = false AND d.date <= :date)")
void deleteUserRolesByExpiredDeletion(@Param("date") LocalDateTime date);

@Transactional
@Modifying
@Query("DELETE FROM User u WHERE u.id IN (SELECT d.userId FROM Deletion d WHERE d.active = false AND d.date <= :date)")
void deleteUsersByExpiredDeletion(@Param("date") LocalDateTime date);

// 修改原有方法的参数类型，避免字符串格式问题
@Transactional
@Modifying
@Query("DELETE FROM Deletion d WHERE d.active = false AND d.date <= :date")
void deleteDeletionByActiveAndDate(@Param("date") LocalDateTime date);

业务层调用逻辑

// 计算24小时前的时间节点
LocalDateTime cutoffDate = LocalDateTime.now().minusHours(24);

// 按顺序执行删除，确保事务一致性
deletionRepository.deleteUserRolesByExpiredDeletion(cutoffDate);
deletionRepository.deleteUsersByExpiredDeletion(cutoffDate);
deletionRepository.deleteDeletionByActiveAndDate(cutoffDate);

方案2：原生SQL批量删除（性能优先） #

如果数据量较大，原生SQL的执行效率更优，同样遵循删除顺序：

扩展DeletionRepository方法

@Transactional
@Modifying
@Query(value = "DELETE FROM user_role WHERE user_id IN (SELECT user_id FROM deletion WHERE active = 0 AND date <= :date)", nativeQuery = true)
void deleteUserRolesByExpiredDeletionNative(@Param("date") LocalDateTime date);

@Transactional
@Modifying
@Query(value = "DELETE FROM user WHERE id IN (SELECT user_id FROM deletion WHERE active = 0 AND date <= :date)", nativeQuery = true)
void deleteUsersByExpiredDeletionNative(@Param("date") LocalDateTime date);

@Transactional
@Modifying
@Query(value = "DELETE FROM deletion WHERE active = 0 AND date <= :date", nativeQuery = true)
void deleteDeletionByActiveAndDateNative(@Param("date") LocalDateTime date);

业务层调用逻辑与方案1一致

关键注意事项 #

• 事务一致性：所有删除操作必须在同一个事务中执行，避免部分执行导致数据不一致
• 执行顺序：严格遵循user_role→user→deletion的删除顺序，否则会触发外键约束报错
• 时间参数：使用LocalDateTime替代字符串，避免时区和格式转换问题
• 索引优化：为deletion表的active和date字段建立联合索引，提升查询过滤效率

内容的提问来源于stack exchange，提问作者Maks Management