我现在正在做一个小项目。我使用Java和Spring Data JPA。我的问题如下。当我试图删除一个博客，它有连接到其他实体，如收藏夹，评论，标签等。我得到一个外部约束错误。我确实理解问题是什么，当试图删除或删除一个实体时，它后面的表通过外键连接到另一个实体，只有当删除子实体时才能删除。因为blog与comment有一个一对多的连接，与tag有一个多对多的连接，并且是超类Post的一个子类，它与User实体有一个多对一的关系，所以删除不能通过使用cascade remove等来完成。理论上，在我的服务代码中，我做了所有手动删除连接的事情，但不知何故，它不起作用，当我运行rest控制器时，我得到一个外部约束错误：
{“消息”：“无法执行语句[（conn=8）无法删除或更新父行：外键约束失败（blogdb. comment，CONSTRAINT FKhvh0e2ybgg16bpu229a5teje7 FOREIGN KEY（parent_comment_id）REFERENCES comment（id））] [delete from comment where id=？]"，“httpError”：404 }
在我以前的一个版本中，我没有“注解”，它给了我同样的“收藏夹”表，所以这是我做的系统性错误，它不是实体特定的。下面是我的服务代码，感谢您的帮助：

public void deleteById(Long id) {

        final Blog blogToDelete = blogRepository.findById(id).orElseThrow(EntityNotFoundException::new);

        // remove blog comments
        removeBlogComments(blogToDelete);

        // remove favorite blogs
        removeFavoriteBlogs(id);

        // remove the blog of the user
        blogToDelete.getUser().getPosts().removeIf(x -> x.getId().equals(id));
        userRepository.save(blogToDelete.getUser());

        blogRepository.delete(blogToDelete);
    }

    private void removeBlogComments(Blog blogToDelete) {

        final List<Comment> blogCommentsToDelete = blogToDelete.getComments();
        blogCommentsToDelete.forEach(x -> x.setParentComment(null));
        commentRepository.saveAll(blogCommentsToDelete);

        blogCommentsToDelete.forEach(x -> {
            x.getUser().getPosts().removeIf(y->y.getId().equals(x.getId()));
            commentRepository.delete(x);
            entityManager.flush();
        });
        blogToDelete.getComments().clear();
        blogRepository.save(blogToDelete);
        entityManager.flush();
    }

    private void removeFavoriteBlogs(Long blogId) {
        final List<User> allByFavoriteBlog = userRepository.findAllByFavoriteBlog(blogId);
        allByFavoriteBlog.forEach(x -> {
            boolean b = x.getFavoriteBlogs().removeIf(y -> y.getId().equals(x.getId()));
            if (b) {
                userRepository.save(x);
                entityManager.flush();
            }
        });
    }

Post实体：

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Inheritance;
import jakarta.persistence.InheritanceType;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.Lob;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Inheritance(strategy = InheritanceType.JOINED)
@SuperBuilder
public class Post {

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

    @Getter
    @Setter
    @Column(name = "likes")
    @Builder.Default
    private int likes = 0;

    @Getter
    @Setter
    @Lob
    @Column(name = "content", nullable = false, columnDefinition = "LONGTEXT")
    private String content;

    @Getter
    @Setter
    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id")
    private User user;

}

Blog实体：

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.Lob;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToMany;
import jakarta.persistence.PrimaryKeyJoinColumn;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@EqualsAndHashCode(callSuper = true)
@Entity
@PrimaryKeyJoinColumn(name = "id")
@Table(name = "blog")
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
@Getter
@Setter
public class Blog extends Post {

    @Column(name = "title", nullable = false)
    private String title;

    @Column(nullable = false)
    @Builder.Default
    @ManyToMany
    @JoinTable(
            name = "blog_tags",
            joinColumns = @JoinColumn(name="blog_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name="tag_id", referencedColumnName = "id"))
    private List<Tag> tags = new ArrayList<>();

    @Lob
    @Column(columnDefinition = "LONGTEXT")
    private String thumbnail;

    @OneToMany(mappedBy = "blog")
    @Builder.Default
    private List<Comment> comments = new ArrayList<>();

}

用户实体：

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.JoinTable;
import jakarta.persistence.ManyToMany;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.ArrayList;
import java.util.List;

@Entity
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table(name = "benutzer")
public class User {

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

    @Getter
    @Setter
    @Column(name = "username", nullable = false, unique = true)
    private String username;

    @Getter
    @Setter
    @Column(name = "email", nullable = false, unique = true)
    private String email;

    @Getter
    @Setter
    @Column(name = "password", nullable = false)
    private String password;

    @Getter
    @Setter
    @OneToMany(mappedBy = "user")
    @Builder.Default
    private List<Post> posts = new ArrayList<>();

    @Getter
    @Setter
    @Builder.Default
    @ManyToMany
    @JoinTable(
            name = "favorites",
            joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "blog_id", referencedColumnName = "id")
    )
    private List<Blog> favoriteBlogs = new ArrayList<>();
}

标签实体：

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Entity
@Table(name = "tag")
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Tag {

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

    @Getter
    @Setter
    @Column(name = "title", nullable = false)
    private String title;
}

注解实体：

import jakarta.persistence.*;
import jakarta.validation.constraints.NotNull;
import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.SuperBuilder;

import java.util.ArrayList;
import java.util.List;

@EqualsAndHashCode(callSuper = true)
@Entity
@PrimaryKeyJoinColumn(name = "id")
@Table(name = "comment")
@AllArgsConstructor
@NoArgsConstructor
@SuperBuilder
@Getter
@Setter
public class Comment extends Post {

    @ManyToOne
    @JoinColumn(name = "blog_id", updatable = false, referencedColumnName = "id")
    //@JsonBackReference("blog_comment")
    private Blog blog;

    @ManyToOne()
    @JoinColumn(name = "parent_comment_id", referencedColumnName = "id")
    //@JsonBackReference("comment_comment")
    private Comment parentComment;

    @OneToMany(mappedBy = "parentComment")
    //@JsonManagedReference("comment_comment")
    private List<Comment> childComments = new ArrayList<>();

}

1条答案

按热度按时间

uurity8g1#

应该没有必要手动删除注解。正如你提到的，这应该通过外键定义中的级联选项来完成。对于某些关系，“删除”选项似乎足够了，但看看你的代码，它看起来像是对于父注解，你需要“设置空”级联选项。
但是，如何设置它取决于您如何管理架构。如果您使用SQL配置表，那么这些选项通常可以与外键定义一起设置为沿着。ex.

CREATE TABLE users (
    user_id integer PRIMARY KEY
);

CREATE TABLE comments (
    comment_id integer PRIMARY KEY,
    user_id integer REFERENCES users ON DELETE CASCADE, -- will delete comments when user is deleted
    parent_id integer REFERENCES comments ON DELETE SET NULL, -- will set to null when parent is deleted
);

如果使用Spring注解，则需要设置特定选项。请参阅：
1.一对多https://www.baeldung.com/spring-jpa-unidirectional-one-to-many-and-cascading-delete

@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<Comment> comments = new HashSet<>();

1.多对一JPA: unidirectional many-to-one and cascading delete

@ManyToOne
    @OnDelete(action = OnDeleteAction.CASCADE)
    private Parent parent;

1.“set null”比较复杂，我建议相应地设置外键约束Have JPA/Hibernate to replicate the "ON DELETE SET NULL" functionality

@ForeignKey(name = "fk_student_teacher",
            foreignKeyDefinition = " /*FOREIGN KEY in sql that sets ON DELETE SET NULL*/")

赞(0）回复(0）举报 8个月前

我在JPA中得到一个外键约束错误

1条答案

相关问题

热门标签

最新问答