Spring-boot-data-jpa 级联软删除如何实现？

求助问答
, , ,
1

我有以下三张表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `is_deleted` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

CREATE TABLE `role` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `is_deleted` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

CREATE TABLE `user_role` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `role_id` int NOT NULL,
  `is_deleted` tinyint unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
)

下面是我的代码：

@Entity
@SQLDelete(sql = "update user set is_deleted = 1 where id = ?")
@Where(clause = "is_deleted = 0")
public class User {
  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "TINYINT")
    private Integer isDeleted;
  
    @Column
    private String username;

    @Column
    private String password;

    @WhereJoinTable(clause = "is_deleted = 0")
    @JsonIgnoreProperties(value = {"users"})
    @ManyToMany(cascade = CascadeType.MERGE)
    @JoinTable(name = "user_role",
            joinColumns = @JoinColumn(name = "user_id"),
            inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private List<Role> roles;
  
  	......
      
}

@Entity
@SQLDelete(sql = "update role set is_deleted = 1 where id = ?")
@Where(clause = "is_deleted = 0")
public class Role {
  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "TINYINT")
    private Integer isDeleted;
  
    @Column
    private String name;
  	
  	@JsonIgnoreProperties(value = {"roles"})
    @ManyToMany(mappedBy = "roles")
    private List<User> users;
  
  	......
      
}

@Entity
@SQLDelete(sql = "update user_role set is_deleted = 1 where id = ?")
@Where(clause = "is_deleted = 0")
public class UserRole {
  
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "TINYINT")
    private Integer isDeleted;
  
    @Column
    private Integer user_id;
  
  	@Column
    private Integer role_id;	
  
  	......
      
}

public interface UserRepository extends PagingAndSortingRepository<User, Integer>, JpaSpecificationExecutor {
}

当我调用 userRepository.deleteById() 删除 id 对应的用户数据时，用户表是符合预期的软删除，但是关系表 user_role 中相应的数据项并没有走预期的软删除逻辑，而是从数据库中被硬删除了。

我想问下怎么才能实现我的需求呢？

2

jpa就这点烦，这堆级联更新删除的关系，鼓捣起来要死人。其实你可以不按照OOP建对象。可以一张表一个对象，再配合QueryDsl，舒服死了。

1 个赞