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?
