How to mix Inheritance Strategies in combination with multiple subclass layers

Question

I am trying to optimize my database tables a bit. But my lack of understanding of Hibernate/JPA is not much of a help right now.

I have a Java Object Model which looks more or less like this:

ParentClass
  SubClass1
    SubSubClass1
    SubSubClass2
    SubSubClass3
    SubSubClass4
  SubClass2
    SubSubClass5
    SubSubClass6

All the Classes contain Fields. About 50% of all Fields are in the ParentClass. 40-50% are on the SubClass1 Level and 0-10% are in the SubSubclass Level. Many of the SubSubClass* classes are empty but are required for identifying the Type.

TRY 1:

So. What we did first, was using a TABLE_PER_CLASS Strategy on the Parentclass. This resulted in a huge amount of Tables:

SubSubClass1
SubSubClass2
SubSubClass3
SubSubClass4
SubSubClass5
SubSubClass6

which is not very cool, since 50% of all Columns in these Tables are shared between all Tables and the other Rest is shared between 3-4 Tables.

TRY 2:

We Changed the Strategy to SINGLE_TABLE.

The resulting table is just one big "ParentClass" Table. But since only about 50% of all Columns are shared between all Subclasses, lots of Fields have to be set to Null, which is not that sexy.

TRY 3:

The Next try was going towards mixing the TABLE_PER_CLASS Strategy with the "SINGLE_TABLE" Strategy. I decided against using JOIN TABLES as a Strategy since I have to many small subclasses which would cause the creation of many many small tables with one or two Columns inside.

So, I was following answers in this Question: How to mix inheritance strategies with JPA annotations and Hibernate?

I wanted now to put all Values from the ParentClass in one table and all values from the first SubLevel into one table for each of them. This should result in a Schema like this:

ParentClass
 - SubClass1
 - SubClass2
 - SubClass3

Here is my code:

@MappedSuperclass
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
public abstract class ParentClass {
  private String value1;
}

@MappedSuperclass
@SecondaryTable(name = "SubClass1")
public abstract class SubClass1 extends ParentClass {
  @Column(table = "SubClass1")
  private String value11;
}

@MappedSuperclass
@SecondaryTable(name = "SubClass2")
public abstract class SubClass2 extends ParentClass {
  @Column(table = "SubClass2")
  private String value12;
}


@Entity
@SecondaryTable(name = "SubClass1")
public abstract class SubSubClass1 extends SubClass1 {
  @Column(table = "SubClass1")
  private String value111;
}

@Entity
@SecondaryTable(name = "SubClass2")
public abstract class SubSubClass2 extends SubClass2 {
  @Column(table = "SubClass2")
  private String value121;
}

Which actually works quite well. But there my problems started:

First of all I get the following Errors during the SchemaUpdate.

Unsuccessful: alter table schema.SubClass1 add constraint AAAAAAA36D68C4 foreign key (id) references schema.ParentClass
ORA-02275: such a referential constraint already exists in the table

Unsuccessful: alter table schema.SubClass2 add constraint AAAAAAA36D68C4 foreign key (id)  references schema.ParentClass
ORA-02275: such a referential constraint already exists in the table

I think these errors are caused since, I am using the SecondaryTables more than once on multiple Levels. For each time I use them, another constraint is being created. which does not work of course, since the constraint already exists.

The Second Problem is the fact that Hibernate goes crazy if it should fetch the data from all those Tables:

select
    * 
from
    ( select
        parentclass0_.value1 as value1_1_,
        parentclass0_1_.value11 as erste1_3_,
        parentclass0_1_.value111 as value1113_3_,
        parentclass0_2_.value12 as value122_3_,
        parentclass0_2_.value121 as value1214_3_,
        parentclass0_.DTYPE as DTYPE2_ 
    from
        schema.parentclass parentclass0_ 
    left outer join
        schema.subclass1 parentclass0_1_ 
            on parentclass0_.id=parentclass0_1_.id 
    left outer join
        schema.subclass1 parentclass0_2_ 
            on parentclass0_.id=parentclass0_2_.id 
    left outer join
        schema.subclass1 parentclass0_3_ 
            on parentclass0_.id=parentclass0_3_.id 
    left outer join
        schema.subclass2 parentclass0_4_ 
            on parentclass0_.id=parentclass0_4_.parentclass_id 
    left outer join
        schema.subclass1 parentclass0_5_ 
            on parentclass0_.id=parentclass0_5_.id 
    left outer join
        schema.subclass1 parentclass0_6_ 
            on parentclass0_.id=parentclass0_6_.id ) 

It joins the same table for each time i used the @SecondaryTable annotation in a subclass. It joins it again and again. I had a look at the Explain Plan from Oracle which tells me that this plan is automatically optimized to what I would use if i would optimize it. but anyway. Its strange.

The Question:

How can i prevent Hibernate from creating the same constraint multiple times? I think this would also fix the join problem. Or should stop trying to do it like this and is there another way?

Answer 1

From a purely JPA perspective, you CANNOT MIX strategies in an inheritance tree. To quote the JPA spec (11.1.20)

The Inheritance annotation defines the inheritance strategy to be used for an entity class hierarchy. It is specified on the entity class that is the root of the entity class hierarchy. Support for the combination of inheritance strategies is not required by this specification. Portable applications should only use a single inheritance strategy within an entity hierarchy.

JDO is the only persistence spec that allows you to define different strategies down the tree.

Answer 2

I think you should really use the SINGLE_TABLE strategy. 50% of columns shared is REALLY not so bad, particularly if you know that number is not going to decrease with time (i mean do you think one day there will be 500 columns in that table, with only 40 columns shared?), and if you're going to run a lot of queries against the root entity that will always do many joins with the other strategy.

At work we just discussed about that. The SINGLE_TABLE strategy was chosen because we didn't think the number of columns would explode and we may end with only 5% of shared properties :(. So i think it will work for your case but take care and think how your data is going to be accessed.

Edit: since it's not possible to mix strategies and you don't want many tables: If you have 140 sub entities and 50% of shared attributes, you should really REALLY use the SINGLE_TABLE strategy!!!!!!