0

I have three entities, Bag, Item and Category. One Bag can have Many Items and Many Items can have Many Categories:

Bag <----OneToMany----> Item

Item -----ManyToMany-----> Category

It isn't necessary store items in Category

When I try delete a Item without categories, there isn't any error message, but the Item isn't deleted. However if i try delete the same item in DataBase, it is deleted successfully.

On the other hand, if i try delete a Item with categories, i receive this error message:

"Cannot delete or update a parent row: a foreign key constraint fails (item_categories, CONSTRAINT fk_item_categories_items_id FOREIGN KEY (items_id) REFERENCES item (id))".

I would like delete the item and the relation with his categories, but keep categories. How could I do this?

For the first problem, I have tried add to the delete method bag.removeItems(item), but it only deletes tthe relationship between both and the item continues in DB.

Bag.java

    @OneToMany(mappedBy = "bag", cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    @JsonIgnoreProperties("bag")
    private Set<Item> items = new HashSet<>();

Item.java

    @ManyToOne
    @JsonIgnoreProperties("items")
    private Bag bag;

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "item_categories",
               joinColumns = @JoinColumn(name="items_id", referencedColumnName="id"),
               inverseJoinColumns = @JoinColumn(name="categories_id", referencedColumnName="id"))
    private Set<Category> categories = new HashSet<>();

ItemResource.java

    @DeleteMapping("/items/{id}")
    @Timed
    public ResponseEntity<Void> deleteItem(@PathVariable Long id) {
        itemRepository.delete(id);
        return ResponseEntity.ok().headers(HeaderUtil.createEntityDeletionAlert(ENTITY_NAME, id.toString())).build();
    }

EDIT: The methods of create and update works fine.

CodificadorD
  • 68
  • 9

1 Answers1

0

So the problem is that you a many-to-many relation which is solved by intermediate (or association) table, where the pairs item - category are stored. When you delete the Item, an actual DELETE FROM item query is executed, but it fails since there is a foreign key constraint which won't allow the corresponding pairs to remain orphaned.

To solve this you can simply remove the associations yourself:

item.getCategories().clear();

As to where this code has to be executed, it's up to you. You can create a hook inside Item, like so:

@PreRemove
private void deleteCategories() {
    this.getCategories().clear();
}

Or you could create an ItemService which would do something like

public void deleteItem(Long id) {
    final Item item = itemRepository.findById(id);
    // you can handle here when not found, which is a good thing to do
    item.getCategories().clear();
    itemRepository.deleteItem(item);
}

Or you could implement a custom method for JPA repository, which I don't like, but you can read up on that here How to add custom method to Spring Data JPA.

Nestor Sokil
  • 2,162
  • 12
  • 28
  • Works!! I have opted for method in the service and allright. Any idea for the problem between `Bag` and `Item`? – CodificadorD Mar 30 '19 at 10:56
  • @CodificadorD sorry, what's the problem with Bag? You want to delete Item and keep Bag or what? – Nestor Sokil Mar 30 '19 at 11:02
  • Ok, I just solved it based on your answer. I add on `deleteItem` method: `final Bag b = item.getBag(); b.removeItems(item);` I had tried it before and it did not work, but now yes – CodificadorD Mar 30 '19 at 11:04
  • Thank you so much for everything @Nestor-sokil – CodificadorD Mar 30 '19 at 11:07
  • @CodificadorD you're welcome. Please note there is an option to use orphanRemoval = true in OneToMany relations, which allows you to skip explicit delete for child items. You can check it out here https://vladmihalcea.com/a-beginners-guide-to-jpa-and-hibernate-cascade-types/ – Nestor Sokil Mar 30 '19 at 11:19