Spring data - Deletion is suppressed with relation to view

Question

So I have two entities:

@Getter
@Setter
@Entity
@Table(name = "MY_TABLE")
public class MyTable {
    @Id
    @Column(nullable = false, length = 18)
    @SequenceGenerator(name = "MY_TABLE_seq", sequenceName = "MY_TABLE_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MY_TABLE_seq")
    private long id;

    @OneToOne(mappedBy = "myTable")
    private MyTableView myTableView;
}

And an Immutable entity (the reason for this is that it is a database view):

@Entity
@Getter
@Immutable
@Table(name = "MY_TABLE_VIEW")
public class MyTableView {
    @Id
    @Column(name = "ID", nullable = false, length = 18)
    private Long id;

    @OneToOne
    @MapsId
    @JoinColumn(name = "id")
    private MyTable myTable;
}

Updating and creating the MyTable works without a problem. The problem start when I try to remove the MyTable. I am using the repository for that:

public interface MyTableRepository extends CrudRepository<MyTable,Long> {
}

In the service I am using:

public void deleteMyTable(Long id){
/*fetch the my table enity*/
   myTableRepository.delete(myTable);
}

Nothing happens. No exception nothing at all. What I have tried is changing the @OneToOne mapping. With different cascade:

@OneToOne(mappedBy = "myTable",cascade = CascadeType.ALL)
@OneToOne(mappedBy = "myTable",cascade = CascadeType.DETACH)
@OneToOne(mappedBy = "myTable",cascade = CascadeType.MERGE)
@OneToOne(mappedBy = "myTable",cascade = CascadeType.REFRESH)
@OneToOne(mappedBy = "myTable",cascade = CascadeType.REMOVE)
@OneToOne(mappedBy = "myTable",cascade = CascadeType.PERSIST)

ALL,MERGE and REMOVE throws and exception as I can not delete from a view
DETACH,REFRESH and PERSIST does nothing but the MyTable entity is not removed

Does anyone have an idea how to resolve this problem?

Can you remove @Repository annotation also transactional can be put to service layer? Spring data automatically scan the packages for you. — Gurkan İlleez, Jun 22 '21 at 14:04
As I understand from your code you try to delete from table and view automatically will be updated — Gurkan İlleez, Jun 22 '21 at 14:06
@Gurkanİlleez : In the end I want to be able to remove the table and by doing that refreshing the view as well. Because I cannot delete from the view — Arion, Jun 22 '21 at 14:09
You can't delete view you can only delete from table and view will be refreshed automatically — Gurkan İlleez, Jun 22 '21 at 14:21
According to behavior of view, it will be created when sql is queried — Gurkan İlleez, Jun 22 '21 at 14:22
@Arion I am not sure if it will work, but have you tried to set to `null` the relationship with `MyTable` in `MyTableView` before deleting `MyTable`? Please, try including something like `@PreRemove private void unsetMyTableInView() { myTableView.setMyTable( null ); }` in `MyTable` and test your code again. This [SO question](https://stackoverflow.com/questions/1763863/delete-hibernate-entity-without-attempting-to-delete-association-table-view) perhaps can be of help as well. — jccampanero, Aug 10 '21 at 13:48
@jccampanero it will still try to do a delete on the view when setting the view to null — Arion, Aug 11 '21 at 19:33
Hi @Arion. Thank you very much for the feedback. I am sorry to hear that it doesn't work. In fact, sorry, because it makes sense according to how the relationship between the entities is established. Please, can you try again but inverting the relationship ownership? I mean, in `MyTable` define the following: `@OneToOne @JoinColumn(name = "id", referencedColumnName = "id") private MyTableView myTableView;`. In `MyTableView` simplify your relationship as this: `@OneToOne(mappedBy = "myTableView") private MyTable myTable;`. If you think about it, as `MyTableView` is a view, defining the relation — jccampanero, Aug 11 '21 at 21:36
@jccampanero that worked. simplifying the mapping and making it the other way around worked. If you put that in a nice answer I can give you the bounty. I agree it make sense — Arion, Aug 12 '21 at 09:56
That is great @Arion!! I am very happy to hear that it worked. I let an answer with this information. Thank you very much! — jccampanero, Aug 12 '21 at 10:46

score 1 · Accepted Answer · edited Aug 17 '21 at 13:22

As indicated in the question comments, I suggested you to try creating some kind of @PreRemove hook in order to avoid the problem.

Because it makes perfect sense according to how the relationship between your entities is defined.

But if you think about that, you are dealing with a view, with a read-only entity: in my opinion, it makes more sense to invert the relationship and make MyTable the owner. In addition probably it will solve the issue.

Please, consider define you relationship with MyTableView in MyTable like this:

@OneToOne
@JoinColumn(name = "id", referencedColumnName = "id")
private MyTableView myTableView;

In MyTableView, simplify your relationship with MyTable in the following way:

@OneToOne(mappedBy = "myTableView")
private MyTable myTable;

score 0 · Answer 2 · answered Aug 11 '21 at 10:35

0

This is probably caused by the constraint on the mapping. An alternative way to remove "MyTableView" instance when deleting "MyTable" is to set "orphanRemoval = true". I think this is better for a double OneToOne mapping. there are some links that (i hope) may helps you :

Hibernate one to one mapping. Delete row from dependent table

How does JPA orphanRemoval=true differ from the ON DELETE CASCADE DML clause

answered Aug 11 '21 at 10:35

KASMI G.

116
11

I tried with the orphan removal. It still tries to do a delete on the view which doesn’t work – Arion Aug 11 '21 at 19:35

Spring data - Deletion is suppressed with relation to view

2 Answers2