Bidirectional @OneToMany @ManyToOne with Multi-Tenancy

Hibernate ORM
1

We are using the Multi-Tenancy in the following structure, where Dog and Cow belong to different schema and NEVER co-exist in the same schema:

Parent Class, which is in both schema HOME and FARM:

@Entity
@Inheritance(strategy = InheritanceType.JOINED)
...
public class Animal implements Serializable {...}

Child Class 1, which is in schema HOME:

@Entity
@PrimaryKeyJoinColumn(name = "ANIMAL_ID")
...
public class Dog extends Animal {...}

Child Class 2, which is in schema FARM:

@Entity
@PrimaryKeyJoinColumn(name = "ANIMAL_ID")
...
public class Cow extends Animal {...}

In this way, when we fetch the Animal entity by different repository, it will fetching only the corresponding child class, i.e., from DogRepository, it will do SELECT * FROM ANIMAL a JOIN DOG d ON a.ID = d.ANIMAL_ID, which essentially does SELECT * FROM HOME.ANIMAL a JOIN HOME.DOG d ON a.ID = d.ANIMAL_ID, and this is exactly what we want.

Now, inside the parent class, we have a field which has a bidirectional @OneToMany and @ManyToOne relationship:

Inside Animal.class:

@OneToMany(
        fetch = FetchType.EAGER,
        mappedBy = "animal",
        cascade = {CascadeType.PERSIST, CascadeType.MERGE})
private List<AnimalRelation> relationships;

Inside AnimalRelation.class:

@ManyToOne
@JoinColumn(name = "ANIMAL_ID")
private Animal animal;

Here comes the issue: when Animal entity has AnimalRelation inside, with the current structure, Hibernate is not able to know which child class it needs, i.e., even from DogRepository, it will do SELECT * FROM ANIMAL a LEFT JOIN DOG d ON a.ID = d.ANIMAL_ID JOIN COW c ON a.ID = c.ANIMAL_ID. But this causes issue as Dog and Cow are not in the same schema, so we get error: ORA-00942: table or view does not exist.

Here is the question: how can we require the subentity @ManyToOne to either joining Animal with Dog only, or fetching Animal only?

2

Which Hibernate ORM version are you using? What HQL query are you executing and what SQL query does it produce exactly? If you use a query, you still have to tell Hibernate ORM to fetch the association as part of the query by using a join fetch or setting an EntityGraph as loadGraph hint.
Then Hibernate ORM should be capable to detect the circularity and avoid the join.

It seems like lazy loading of Animal is always going to be a problem for you though, so you might want to change your mapping that this can’t happen.

3

Hey @beikov , thanks a lot for your reply. For your questions:

  1. We are using Hibernate 5.6.15.Final
  2. We are doing a fetching based on the entity relationship directly, i.e., @ManyToOne private Animal animal.
    The corresponding SQL generated is:
    SELECT * 
FROM ANIMAL a 
LEFT JOIN DOG d ON a.ID = d.ANIMAL_ID 
LEFT JOIN COW c ON a.ID = c.ANIMAL_ID

And yes – lazy loading will not work… Is there a way to specify the schema information inside the query or the session?

4

You can map the Animal table multiple times for every schema e.g.

@Entity
@Inheritance(JOINED)
@Table(schema = "schema1", name = "ANIMAL")
public class AnimalSchema1 {...}

@Entity
@Inheritance(JOINED)
@Table(schema = "schema2", name = "ANIMAL")
public class AnimalSchema2 {...}

and

@Entity
@Table(name = "DOG")
public class Dog extends AnimalSchema1 {...}

@Entity
@Table(name = "COW")
public class Cow extends AnimalSchema2 {...}
5

Thanks again for your reply @beikov , then in this case, how would we deal with AnimalRelation entity? We have the following inside AnimalRelation.class:

@ManyToOne
@JoinColumn(name = "ANIMAL_ID")
private Animal animal;

Are we supposed to duplicate this entity too if we go with AnimalSchema1 and AnimalSchema2? Or is there a better solution (thinking of the duplication will make it hard to maintain)?

6

Yeah, you’d probably have to duplicate it, because otherwise a proxy lazy inititalization will fail.

7

Thanks @beikov. One more question – I see the definition for PolymorphismType.EXPLICIT seems to fit our needs, so I applied it as follows:

// Parent class
@Entity
@Inheritance(strategy = InheritanceType.JOINED)
...
public class Animal implements Serializable {...}

// Child class 1
@Entity
@PrimaryKeyJoinColumn(name = "ANIMAL_ID")
@Polymorphism(type = PolymorphismType.EXPLICIT)
...
public class Dog extends Animal {...}

// Child class 2
@Entity
@PrimaryKeyJoinColumn(name = "ANIMAL_ID")
@Polymorphism(type = PolymorphismType.EXPLICIT)
...
public class Cow extends Animal {...}

But when querying private Animal animal, Hibernate still performs a full LEFT JOIN with all child classes. If I’m understanding correctly, with EXPLICIT in this case, only the parent class should be fetched. Any idea why it is not performing in the desired way?

8

@AJ-Wuu as you can see in the User Guide example you linked, @PolymorphismType.EXPLICIT only works when querying non-mapped classes: in that example, the interface DomainModelEntity is used. If you query Animal, which is a mapped entity class, the subtype tables will still need to be joined.

If you wish to only query Animal type entities, you can use Hibernate’s type function to filter the required entity types:

select a from Animal a where type(a) = Animal