Problem with Hibernate 6 and multi level inheritance: Add joins

Hibernate ORM
1

Hi everyone!

I’m facing a problem when using a 2-level inheritance in Hibernate 6.2.13.Final.

I have a grandfather class: ClinicalAct
I have a father class: Consultation that extends ClinicalAct
I have 2 children classes: MedicalOrder and TreatmentSession that extend Consultation.

I have a registry in the database that represents a MedicalOrder, say with SID=1.

If I query ClinicalAct (grandfather) where sid = 1, I get the right query, with necessary joins to fetch the hierarchy.
If I query MedicalOrder (child) where sid = 1, I get the right query, joining with the ancestry.
But if I query Consultation (father) where sid = 1, I get about 30 innecesary joins, related to some other entities mapped by some of these levels.

More data:

  • I have no OneToOne relationship
  • All ManyToOne at all levels have FetchType.LAZY
  • ClinicalAct extends ClinicalActBase, which is a @MappedSuperclass

I’m showing you the problematic query in case it helps:

— JQL QUERY —
SELECT consultation FROM ConsultationImpl consultation WHERE consultation.sid = 2190990

— SQL QUERY —

select ...
from
    SCH_CONSULTATIONS c1_0 -- The father table
join
    COM_CLINICAL_ACTS c1_1 
        on c1_0.SID=c1_1.SID -- The grandfather table
left join
    TRE_TREATMENT_SESSIONS c1_2 
        on c1_0.SID=c1_2.SID -- Child 1
left join
    SCH_MEDICAL_ORDERS c1_3 
        on c1_0.SID=c1_3.SID -- Child 2
-- Innecesary joins from here
left join
    COM_ACT_STATUS a1_0 
        on a1_0.sid=c1_1.ACTIVITY_STATUS 
left join
    COM_CLI_ACT_TYPES c2_0 
        on c2_0.SID=c1_1.CLINICAL_ACT_TYPE 
left join
    COM_SERVICES s1_0 
        on s1_0.SID=c1_1.SERVICE 
left join
    COM_SUBENCOUNTERS s2_0 
        on s2_0.SID=c1_1.SUBENCOUNTER 
left join
    COM_SUBENCOUNTERS s3_0 
        on s3_0.SID=c1_1.SUBENCOUNTER 
left join
    SCH_AG_SES_SP_ACTS a2_0 
        on a2_0.sid=c1_0.AGE_SES_SPAN_ACT 
left join
    SCH_AGENDAS a3_0 
        on a3_0.sid=c1_0.AGENDA 
left join
    SCH_AGENDAS a4_0 
        on a4_0.sid=c1_0.AGENDA 
left join
    SCH_CONS_ORIGINS c3_0 
        on c3_0.sid=c1_0.CONSULTATION_ORIGIN 
left join
    MOR_EXTER_DOCTORS e1_0 
        on e1_0.sid=c1_0.EXTERNAL_DOCTOR 
left join
    SCH_CONSULTATIONS f1_0 
        on f1_0.SID=c1_0.FATHER_CONSULTATION 
left join
    SCH_APPS_CONFIRM l1_0 
        on l1_0.sid=c1_0.LAST_CONFIRM 
left join
    SCH_MED_REC_REQS m1_0 
        on m1_0.sid=c1_0.MED_RECORD_REQUEST 
left join
    COR_OPERATORS o1_0 
        on o1_0.SID=c1_0.OPERATOR 
left join
    COM_FACILITIES o2_0 
        on o2_0.SID=c1_0.ORIGIN_FACILITY 
left join
    COM_GEN_PRACTITNRS p1_0 
        on p1_0.SID=c1_0.PLAC_GENERAL_PRACT 
left join
    COM_PERSONAL p2_0 
        on p2_0.SID=c1_0.PLACER_DOCTOR 
left join
    COM_FACILITIES p3_0 
        on p3_0.SID=c1_0.PLACER_FACILITY 
left join
    COM_SERVICES p4_0 
        on p4_0.SID=c1_0.PLACER_SERVICE 
left join
    SCH_VALIDATION_STATUS v1_0 
        on v1_0.SID=c1_0.VALIDATION_STATUS 
left join
    SCH_WEB_CONSULTATIONS w1_0 
        on w1_0.SID=c1_0.WEB_CONSULTATION 
left join
    SCH_WL_STATUS w2_0 
        on w2_0.sid=c1_0.WL_STATUS 
left join
    DHS_SESSION_DEFS s4_0 
        on s4_0.sid=c1_2.SESSION_DEF 
left join
    COM_SUBENCOUNTERS s5_0 
        on s5_0.SID=c1_2.SUBENCOUNTER_TRE_SESSION 
left join
    TRE_TREAT_PRESCRIPTIONS t1_0 
        on t1_0.SID=c1_2.TREATMENT_PRESCRIPTION 
left join
    COM_PERSONAL f2_0 
        on f2_0.SID=c1_3.FILLER_DOCTOR 
left join
    COR_OPERATORS s6_0 
        on s6_0.SID=c1_3.SAMPLE_OPERATOR 
left join
    MOR_SPAN_TYPES s7_0 
        on s7_0.sid=c1_3.SPAN_TYPE 
left join
    ADM_SURG_WAIT_LIST s8_0 
        on s8_0.sid=c1_3.REQUESTED_TEST_SWL 
left join
    COM_TIM_RECURRENCS t2_0 
        on t2_0.sid=c1_3.TIME_RECURRENCE 
where
    c1_0.SID=2190990

Kind regards,
Alex

2

I can add some more information:

I’ve added a @DiscriminatorColumn annotation to ClinicalAct:
@DiscriminatorColumn(name = “DTYPE”, discriminatorType = DiscriminatorType.STRING)

Now, when I query Consultation, I get quite a perfect query, but it adds the “DTYPE” part, that I believe it’s not needed for there is a inner join with Consultation:

select … from
COM_CLINICAL_ACTS c1_0
join
SCH_CONSULTATIONS c1_1
on c1_0.SID=c1_1.SID
left join
TRE_TREATMENT_SESSIONS c1_2
on c1_0.SID=c1_2.SID
left join
SCH_MEDICAL_ORDERS c1_3
on c1_0.SID=c1_3.SID
where
c1_1.SID=2190270
and c1_0.DTYPE in (‘SCH_CONSULTATIONS’,‘SCH_MEDICAL_ORDERS’,‘TRE_TREATMENT_SESSIONS’)

That would affect performance, but I think I could live with that, but at another certain point of the application, it seems to try to find the DTYPE field not in the grandfather ClinicalAct, but in the father, Consultation:

select … from SCH_CONSULTATIONS d1_0 join COM_CLINICAL_ACTS d1_1 on d1_0.SID=d1_1.SID left join TRE_TREATMENT_SESSIONS d1_2 on d1_0.SID=d1_2.SID left join SCH_MEDICAL_ORDERS d1_3 on d1_0.SID=d1_3.SID where d1_0.FATHER_CONSULTATION=? and d1_0.DTYPE in (‘SCH_CONSULTATIONS’,‘SCH_MEDICAL_ORDERS’,‘TRE_TREATMENT_SESSIONS’)] [ORA-00904: “D1_0”.“DTYPE”: identificador no válido

I think this could be the right way to go, but am unsure because of the details.

Any hints, either using or not using @DiscriminatorColumns?

3

Did you try updating Hibernate to the latest version 6.5.2/6.6.0 yet? Or if you must stick with 6.2, use 6.2.28.Final. If so, and you still have the problem, please create an issue in our issue tracker with a test case template that reproduces the issue.

4

Ok, I’ll try to upgrade to 6.2.28.Final (I depend on Wildfly’s hibernate version), but in the meantime what would be the right way to do it? I’ve searched the documentation but couldn’t find any examples of this situation using @DiscriminatorColumn.

Thanks!

5

There is no right or wrong. You can use either discriminated or joined inheritance, both are fine, but discriminated inheritance usually performs better.