How to delete data that has foreign key in jpa java

user_10454516 0
Viewed 6138

I have use case, a Book has one Category and a Category could belong to a lot of Books. One Book could belong to multi Authors and an Author could belong to multi books. So, here is their relationship:

  1. Book to BookCategory => many-to-one
  2. BookCategory to Book => one-to-many
  3. Book to Author and Author to Book => many-to-many

So, I have 4 table:

  1. book
  2. book_category
  3. author
  4. book_author (book_id, author_id)

Case:

  1. if I delete a Book, there should nothing happen to the book_category table
  2. If I delete a Book, the Author should be deleted if the Author not belong to any books anymore. For example, I have book1 and book2 with same Author named John. If I delete book1, it wont delete John. But if I delete book2 also, then John will be deleted from Author table. Also, any book deletion will affect book_author table.

here is how I declare the entity

Author

@Entity(name = "author")
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "name", nullable = false)
    private String name;
    @Column(name = "address", nullable = false)
    private String address;
    @ManyToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JsonBackReference
    private Set<Book> book;
}

Book

@Entity(name = "book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "title", nullable = false)
    private String title;
    @Column(name = "year", nullable = false)
    private String year;
    @ManyToMany(cascade = CascadeType.ALL)
    @JoinTable(
      name = "book_author",
      joinColumns = @JoinColumn(name = "book_id", referencedColumnName = "id"),
      inverseJoinColumns = @JoinColumn(name = "author_id", referencedColumnName = "id"))
    @JsonManagedReference
    private Set<Author> author;
    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id", referencedColumnName = "id", nullable = false)
    @JsonIgnoreProperties({"hibernateLazyInitializer", "handler"}) 
    private BookCategory category;
}

Book Category

@Entity(name = "book_category")
public class BookCategory {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(name = "category", nullable = false)
    private String category;
}

and here is how I try to delete the book. Just a simple method. But, after I run this, it show error said org.postgresql.util.PSQLException: ERROR: update or delete on table "book_category" violates foreign key constraint "fk5jgwecmfn1vyn9jtld3o64v4x" on table "book", which seems like the method try to also delete the book_category table.

@RequestMapping(value = "/{id}", method = RequestMethod.DELETE)
    public ResponseEntity<?> delete(@PathVariable(value = "id") Long id) throws Exception {
        try {
            bookService.deleteById(id);
            return ResponseEntity.ok().body("delete done");
        } catch (Exception e) {
            return ResponseEntity.badRequest().body(e.getMessage());
        }
    }

Then, how to achieve it? Any clue, please?

spring-data-jpa spring-boot jpa java
1 Answers

I guess the problem is the cascade = CascadeType.ALL in the category field in Book class. Using CascadeType.ALL, whenever you try to delete a Book, its category must be deleted too. The problem is that it might exist other books instances with the category you want to delete in your database. Then, a constraint database error will be thrown.

So, I would recommend using cascade = CascadeType.PERSIST or cascade = CascadeType.MERGE in the category field. In this way, when you delete a book, the category will not be deleted.

This link will give you a better understanding of cascade types, showing how it works.

I tend to avoid using cascades, especially CascadeType.ALL and CascadeType.REMOVE. I prefer to make several calls to the persistence layer in order to perform some business task (similar to those at the beginning of your question in the enumerated items - if I delete a book ..). Besides, Spring allows you to put all calls in a transaction using @Transactional in any method of your business layer.

user_58549250
Related