Hibernate - Lazily fetch @OneToOne association with same parent referred by multiple recursively mapped children

Question

The following constellation gives me a org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException:

The associations:

1 Account   (account)      <->  (mainBudget) Budget 0..1
0..1 Budget (parentBudget) <->  (subBudget) Budget *

The actual implementation of an Account objects and Budget objects would look like this:

Forwards:

Account -> (mainBudget) Budget -> (subBudget) Budget -> (subBudget) Budget

Backwards:

Budget -> (parentBudget) Budget -> (parentBudget) Budget -> (account) Account

Each Budget in the budget tree (max height 3 as modeled) has the same account associated to it, where the account only has the mainBudget associated.

For the Account.java:

@Audited
@Entity
public class Account {
  @Id
  @GeneratedValue(strategy = GenerationType.TABLE)
  protected long id;

  @OneToOne(cascade = CascadeType.ALL, orphanRemoval=true, fetch=FetchType.LAZY) 
// Here I also tried mappedBy = "account" with @JoinColumn for the budget (with the exception stated at the end)
  @LazyToOne(LazyToOneOption.PROXY)
  private  Budget mainBudget;
  } 
...
}

For the Budget.java:

@Audited
@Entity
public class Budget {
  @Id
  @GeneratedValue(strategy = GenerationType.TABLE)
  protected long id;

  @OneToOne(fetch=FetchType.LAZY)
  //@JoinColumn(name="account_id") - I tried this but also got an exception (stated below)
  private Account account;

  @OneToMany(targetEntity = Budget.class, cascade = CascadeType.ALL, 
    mappedBy = "parentBudget", orphanRemoval=true)
  @Fetch(value = FetchMode.SUBSELECT)
  private List<Budget> subBudget; 

  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="parentBudget_id")
  private Budget parentBudget; 
...
} 

The in-memory H2 database looks the following (which seems fine to me):

The error message in detail is:

Referential integrity constraint violation: "FKovatioxlljiymn9haxf1yrjs7: PUBLIC.Account FOREIGN KEY(mainBudget_id) REFERENCES PUBLIC.Budget(id) (2259)"; SQL statement:
delete from Budget where id=? [23503-200]

The delete-method looks like this:

  @Transactional
  public boolean delete(long id, String resource)
      throws NoSuchElementException {
    List<Budget> objs = getAllById(id, resource); //SELECT * FROM Budget WHERE id=...

    if (objs.isEmpty()) {
      throw new NoSuchElementException(
          "Delete not possible.");
    }

    router.setDataSource(resource);

    for (Budget obj : objs) {
      em.remove(obj);
      em.flush(); // Here the exception is thrown
    }
    return true;
  }

Why do I get a referential integrity constraint violation exception? The same mappings worked before with EAGER loading.

As stated as comments in the code, if I use mappedBy for the parent and @JoinColumn for the child I as suggested here, I get the exception:

org.hibernate.HibernateException: More than one row with the given identifier was found: 50, for class: ...Budget

Answer 1

The way I see it, your model has two uni-directional relationships between Account and Budget.

It has * - 1 from Budget to Account.
It has 1 - 0..1 from Account to Budget.

I could make it work writing the following entities:

@Entity
public class Account {

    @Id
    private long id;

    @OneToOne
    @JoinColumn(name = "mainBudget_id")
    private Budget budget;
}

@Entity
public class Budget {

    @Id
    private long id;

    @ManyToOne(optional = false)
    @JoinColumn(name = "account_id")
    private Account account;

    @ManyToOne
    @JoinColumn(name = "parentBudget_id")
    private Budget parentBudget;

    @OneToMany(orphanRemoval = true, mappedBy = "parentBudget")
    private List<Budget> subBudget;
}

And using the same data you provided:

insert into Account(id, mainBudget_id) values (76, null);
insert into Budget(id, account_id, parentBudget_id) values (50, 76, null);
update Account set mainBudget_id = 50 where id = 76;
insert into Budget(id, account_id, parentBudget_id) values (51, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (52, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (55, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (58, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (61, 76, 51);
insert into Budget(id, account_id, parentBudget_id) values (64, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (65, 76, 64);
insert into Budget(id, account_id, parentBudget_id) values (68, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (69, 76, 68);
insert into Budget(id, account_id, parentBudget_id) values (72, 76, 50);
insert into Budget(id, account_id, parentBudget_id) values (73, 76, 72);
insert into Budget(id, account_id, parentBudget_id) values (2259, 76, 50);

This was enough to play around retrieving a list of budgets, deleting a budget, etc.

ATTENTION: Using the mapping provided here, you candelete any Budget in the tree. If the deleted budget isn't a leaf node, the persistence provider will delete the tree below it. Nevertheless, if you try to delete the main budget without updating the account entity first, it will fail.