JPA Hibernate - cascade delete in both database and annotation

Question

Brief

I am wondering what I should do as I have read many articles trying to understand this, including many SO questions. Nothing I have read has quite hit the nail on the head with this one.

I want to know what happens when a database is defined with cascade rules as well as the application since this will define whether I should take the following approach or another.

Example tables

create table foo(
  id int unsigned not null auto_increment,
  primary key(id)
);

create table bar(
  id int unsigned not null auto_increment,
  foo_id int unsigned not null,
  primary key(id),
  foreign key(foo_id) references foo(id) on delete cascade on update cascade
)

Example classes

@Entity
@Table(name = "foo")
public class Foo {

  private int id;
  private List<Bar> bars;

  @Id
  @GeneratedValue
  @Column(name = "id")
  public int getId() {
    return id;
  }

  @OneToMany(mappedBy = "foo", cascade = {CascadeType.ALL})
  public List<Bar> getBars() {
    return bars;
  }

  public void setId() {
    this.id = id;
  }

  public void setBars(List<Bar> bars) {
    this.bars = bars;
  }

}

@Entity
@Table(name = "bar")
public class Bar {

  private int id;
  private Foo foo;

  @Id
  @GeneratedValue
  @Column(name = "id")
  public int getId() {
    return id;
  }

  @ManyToOne
  @JoinColumn(name = "foo_id", nullable = false)
  public getFoo() {
    return foo;
  }

  public void setId(int id) {
    this.id = id;
  }

  public void setFoo(Foo foo) {
    this.foo = foo;
  }

}

Questions

If I now call a delete operation (be it through EntityManagerFactory or SessionFactory) on a Foo object, which of the following will occur?

1. The hibernate operation will delete all records in the bar table whose foreign key is that of Foo's foo_id and then delete the Foo record.

2. The hibernate operation will delete all corresponding Bar records that have been loaded into session cache (which may or may not be all bar records that exist in the actual database) and then delete the Foo record (the database cascade rule will then delete any remaining bar records).

3. The hibernate operation will attempt to delete the Foo record first and if database failure then do one of the aforementioned steps.

4. Something else happens for which I have not considered, if so what?

Considering the following dilemna assumptions, what is the best approach?

Dilemna

If 1 is true then it would suggest:

A) Define cascade rule in database only. Be sure to remove bars from the object in the application so they are not left detached from the database (as the database will delete their records) then make the call to delete foo.

OR

B) Define cascade rule in application only since it will manage the database integrity thoroughly.

NOT

C) Define cascade rules in both, since each achieve the desired result making the other a waste of processing.

If 2 is true then it would suggest:

Define cascade rules in both database and application so that Hibernate can take care of managing its entities and the database can clean up after since the application is not guaranteed to remove all the bar records.

If 3 is true then it would suggest:

Define cascade rules in both database and application since Hibernate appears to support the cascade rule already being defined at the database level.

If 4 is true then it would suggest:

This question is even more important as I have missed something fundamental!

Edit: Add articles I have read...

Related articles

Conflicting views for database, application or both:

SO - should-i-let-jpa-or-the-database-cascade-deletions

Conflicting views for database or application:

SO - cascading-deletes-updates-using-jpa-or-inside-of-database

This article sheds light on what JPA providers actually do (though it should be noted that they use OpenJPA provider for their proof of operations):

jpa-tutorial

It states that:

The cascading of remove and persist operations applies also on those entities that have not been loaded yet. It even passes through them to other entities, potentially traversing through whole object graph.

It goes on to state:

The cascading of refresh, merge and detach passes only through entities that are already loaded.

This would mean that proposed process 2 is not true.

Answer 1

if you declare cascade in the database and hibernate the database will always delete first if it supports it and the hibernate calls will not really delete anything but run anyway. However, since you are using hibernate its main advantage is to allow easy transition to a new database which may not support database side cascade ability. Thus you would want to leave them in there even if your database supports cascade and the hiberate underlining jdbc statements are not currently doing anything (they may do something in the future)

Answer 2

Why would you even consider it? It is best to stick with hibernate cascade options. On the other side having cascade on both sides would run cascade delete two times. Once from hibernate and once managed by database.

Example 189. from hibernate 5.2 docs. which generates below sql.

@Entity(name = "Person")
public static class Person {
    @ManyToMany(cascade = {CascadeType.DELETE})
    private List<Address> addresses = new ArrayList<>();
    ...
}

Person person1 = entityManager.find( Person.class, personId );
entityManager.remove( person1 );

DELETE FROM Person_Address
WHERE  Person_id = 1

DELETE FROM Person
WHERE  id = 1

Now you see that hibernate deletes child entities before it deletes parent. Database cascade will run on sql person delete but it has nothing to remove now when children were removed before.