删除多对多关联时出现问题

Question

@entity
@table(name = "users")
public class User {

@Id
private Long id;

private String name;

@ManyToMany(cascade = {PERSIST, MERGE})
@JoinTable(name = "user_following",
        joinColumns = {@JoinColumn(name = "user_id")},
        inverseJoinColumns = {@JoinColumn(name = "user_following_id")})
private List<User> following;

@ManyToMany(cascade = {PERSIST, MERGE})
@JoinTable(name = "user_followers",
        joinColumns = {@JoinColumn(name = "user_id")},
        inverseJoinColumns = {@JoinColumn(name = "user_follower_id")})
private List<User> followers;

//some other fields, getters and setters

public void follow(User user) {
    following.add(user);
    user.acceptFollower(this);
}

public void acceptFollower(User user) {
    followers.add(user);
}
}

我们有三个表：

• 用户
• user_following
• user_followers

可以想象拥有此实体，并且您正在实现以下＆amp;追随者功能。 我们必须向用户 - Rick＆amp;汤姆。

User rick = new User(1, "rick");
User tom = new User(2, "tom");
rick.follow(tom);

调用方法 - rick.follow（tom）； 我们将以以下方式填充表

- “用户”

ID	名称
1	rick
2	tom

表 - “ user_following”

user_id user_id	user_following_id_id
1	2

表 - “ user_followers”

user_id user_id user_follower_id	user_follower_id
2	1

到目前我尝试删除用户。

例如：

userrepository.deletebyid（1）

JPA给出了错误 - 更新或在表“用户”上删除违反外键约束“ FK1YG7XQW2KFX6N2196O0GR3OBC”表“ User_followers”上 Nomention（ID）=（1）仍然从表“ user_followers”引用。

JPA无法删除，因为它会生成错误的SQL。

Hibernate: 
   select
       user0_.id as id1_5_,
       user0_.first_name as first_na5_5_,
   from
       users user0_ 
   where
       user0_.username=?
Hibernate: 
   delete 
   from
       user_followers 
   where
       user_id=?
Hibernate: 
   delete 
   from
       user_following 
   where
       user_id=?
Hibernate: 
   delete 
   from
       users 
   where
       id=?

我想删除用户-ID = 1的Rick

由于错误的SQL而发生错误。 当我们删除用户 - 'rick'， 我们首先从“ user_following”和“ user_followers”表中删除“ rick”表。

看看这个说法。

Hibernate: 
    delete 
    from
        user_followers 
    where
        user_id=?

错误是由于此语句引起的，因为 用户我们删除的用户对应于“ user_followers”表的（user_following_id）列， 不是“ user_id”列。

正确的SQL语句必须是：

Hibernate: 
    delete 
    from
        user_followers 
    where
        user_following_id=?

有没有办法解决此问题？

Answer 1

请勿创建user_followers作为表，而是在表 view 上创建它， user_following。视图定义仅逆转列名。我不知道您的模糊经理，所以我只会提供所需的SQL。 （请参阅

create table users( id    integer primary key 
                  , name  text 
                  ) ; 
                 
 create table user_following( user_id   integer 
                                        references users(id) 
                                        on delete cascade 
                            , user_following_id integer 
                                        references users(id) 
                                        on delete cascade
                            , constraint  user_following_pk 
                                          primary key (user_id,user_following_id)
                            ) ; 
                            
create view user_followers as 
       select user_following_id as user_id 
            , user_id           as user_follower_id
  from user_following;