jpa - 在jpa中删除多对多实体

Question

我有一个实体

@Entity
@Table(name="user")
public class User {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "friends")
    private Set<User> friends;
}

目前，我通过首先从数据库中获取所有 friend 来删除 friend ，这不是高效/最佳的

@Override
@Transactional
public void deleteFriend(String username, String friend) {
    User user = getUser(username);
    User other = getUser(friend);
    user.getFriends().remove(other);
    other.getFriends().remove(user);
}

由于效率不高，我想使用此查询

public interface UserRepository extends PagingAndSortingRepository<User, Long>{
    void removeByUsernameAndFriendsUsername(String username, String otherUsername);
}

当我尝试使用此查询删除好友时，出现异常

    Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK8KCUM44FVPUPYW6F5BACCX25C: PUBLIC.COMMENT FOREIGN KEY(USER_ID) REFERENCES PUBLIC.USER(ID) (3)"; SQL statement:
delete from user where id=? and version=? [23503-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:425)
    at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:442)
    at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:317)
    at org.h2.table.Table.fireConstraints(Table.java:980)
    at org.h2.table.Table.fireAfterRow(Table.java:998)
    at org.h2.command.dml.Delete.update(Delete.java:101)
    at org.h2.command.CommandContainer.update(CommandContainer.java:101)
    at org.h2.command.Command.executeUpdate(Command.java:260)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:164)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:150)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 68 more

你知道是否可以使用 @NamedQuery 或 @Query 删除它，而不必先获取所有好友？类似于下面的查询。

@Query(value = "delete from User U join U.friends friends where U.username = ?1 and friends.username = ?2")

这是评论实体

@Entity
@Table(name="comment")
public class Comment {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id")
    private Long id;

    @NotNull
    @Size(min=1)
    @Column(name="text", nullable=false)
    private String text;

    @NotNull
    @ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;
}

Answer 1

尝试:

 @Modifying
 @Query("delete from User U join U.friends friends where U.username = ?1 and friends.username = ?2)
....