CODEWITHSUNDEEP
hibernatehql
Leo
Leo

Reputation: 1051

Hibernate left join always hit the base

I have next service:

public void checkAll() {
    Session sess = (Session) em.getDelegate();
    System.out.println("Check 1");
    Query query = sess.createQuery("select distinct t from House t left join fetch Kw k on t.id=k.fkHouse "
            + "left join fetch Kart a on k.id=a.fkKw "
            + "left join fetch Reg r on a.lsk=r.lsk "
            + "left join fetch RegState s on a.lsk=s.lsk "
            + " "
            + "where t.klsk = 187804");
    System.out.println("Check 2");
    List<House> lst =query.list(); 
    for (House o : lst) {
        System.out.println("Check 3");
        for (Kw kw : o.getKw()) {
            for (Kart kart : kw.getLsk()) {
                for (Reg reg : kart.getReg()) {
                    System.out.print("Reg="+reg.getDtReg());    
                    break;
                }
                break;
            }
            break;
        }
        break;
    }
    System.out.println("Check 4");
}

I've turned on SQL Debug of Hibernate an see next:

Check 1
2393 [main] INFO  org.hibernate.hql.internal.QueryTranslatorFactoryInitiator  - HHH000397: Using ASTQueryTranslatorFactory
Check 2
2547 [main] DEBUG org.hibernate.SQL  - select distinct house0_.ID as ID1_1_, house0_.FK_K_LSK as FK_K_LSK2_1_, house0_.FK_STREET as FK_STREET3_1_ from AR.HOUSE house0_ left outer join AR.KW kw1_ on (house0_.ID=kw1_.FK_HOUSE)left outer join AR.KART kart2_ on (kw1_.ID=kart2_.FK_KW)left outer join PS.REG reg3_ on (kart2_.LSK=reg3_.LSK)left outer join PS.REG_STATE regstate4_ on (kart2_.LSK=regstate4_.LSK) where house0_.FK_K_LSK=187804
Check 3
2609 [main] DEBUG org.hibernate.SQL  - select kw0_.FK_HOUSE as FK_HOUSE3_3_0_, kw0_.ID as ID1_3_0_, kw0_.ID as ID1_3_1_, kw0_.FK_K_LSK as FK_K_LSK2_3_1_, kw0_.FK_HOUSE as FK_HOUSE3_3_1_ from AR.KW kw0_ where kw0_.FK_HOUSE=?
2655 [main] DEBUG org.hibernate.SQL  - select kart0_.FK_KW as FK_KW4_2_0_, kart0_.LSK as LSK1_2_0_, kart0_.LSK as LSK1_2_1_, kart0_.FK_K_LSK as FK_K_LSK2_2_1_, kart0_.FIO as FIO3_2_1_, kart0_.FK_KW as FK_KW4_2_1_ from AR.KART kart0_ where kart0_.FK_KW=?
2657 [main] DEBUG org.hibernate.SQL  - select reg0_.LSK as LSK6_15_0_, reg0_.ID as ID1_15_0_, reg0_.ID as ID1_15_1_, reg0_.DT_REG as DT_REG2_15_1_, reg0_.DT_REG_TS as DT_REG_TS3_15_1_, reg0_.DT_UNREG as DT_UNREG4_15_1_, reg0_.DT_UNREG_TS as DT_UNREG_TS5_15_1_, reg0_.LSK as LSK6_15_1_, reg0_.FK_PERS as FK_PERS7_15_1_, reg0_.FK_REG_STATUS as FK_REG_STATUS8_15_1_, reg0_.FK_REG_TP as FK_REG_TP9_15_1_ from PS.REG reg0_ where reg0_.LSK=?
Reg=1996-03-12 00:00:00.0Check 4
3382 [main] DEBUG org.hibernate.SQL  - select distinct house0_.ID as ID1_1_, house0_.FK_K_LSK as FK_K_LSK2_1_, house0_.FK_STREET as FK_STREET3_1_ from AR.HOUSE house0_ left outer join AR.KW kw1_ on (house0_.ID=kw1_.FK_HOUSE)left outer join AR.KART kart2_ on (kw1_.ID=kart2_.FK_KW)left outer join PS.REG reg3_ on (kart2_.LSK=reg3_.LSK)left outer join PS.REG_STATE regstate4_ on (kart2_.LSK=regstate4_.LSK) where house0_.FK_K_LSK=187804

At point "Check 1" I see SQL with joins and it is good, but why at point "Check 3" I see weird SQLs, which hit the base again?

I forced hibernate to load all childs using "left join fetch" at point "Check 1", but why it need again to retreive childs from the database?

Please help me, may be I need to change approach with getting child entities? May be it is Hibernate N+1 problem I have to solve?

Upvotes: 0

Views: 96

Answers (1)

shankarsh15
shankarsh15

Reputation: 1967

Yes you are experiencing a "N+1" select problem.

To avoid N+1 select problem, just make sure that you join fetch all associations involved with House Entity especially One-One association.

Also HQL should be written in a manner like in example show below ,

 SELECT employee FROM Employee employee  LEFT JOIN FETCH employee.employer

Please go through the HQL docs below:

https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-from

Upvotes: 1

Related Questions