CODEWITHSUNDEEP
javamysqlsql-serverhibernatejoin
junior walter ramos diaz
junior walter ramos diaz

Reputation: 33

Why hibernate add to my query cross join at the end

I am constructing and running a query via this Hibernate-based code:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = cb.createTupleQuery();
Root<hisaVO> hisa = criteria.from(hisaVO.class);
Root<EstablecVO> establec = criteria.from(EstablecVO.class);
Root<DisaVO> disa = criteria.from(DisaVO.class);
Root<RedVO> red1 = criteria.from(RedVO.class);
Root<MicroredVO> microred = criteria.from(MicroredVO.class);
Root<Unidad_EjecutoraVO> ue1 = criteria.from(Unidad_EjecutoraVO.class);
Join<hisaVO,EstablecVO> j1 = hisa.join("estab");
Join<EstablecVO,DisaVO> j2 = j1.join("disa") ;
Join<EstablecVO,RedVO> j3 = j1.join("red") ;
Join<EstablecVO,MicroredVO> j4 = j1.join("microred") ;
Join<EstablecVO,Unidad_EjecutoraVO> j5 = j1.join("ue") ;

criteria.multiselect(j3.get("red_nombre"), cb.count(hisa))
  .groupBy(red1.get("red_nombre"));

return em.createQuery(criteria).getResultList();

The log shows Hibernate is implementing that via this corresponding SQL:

select
  redvo3_.red_nombre as col_0_0_,
  count(hisavo0_.id) as col_1_0_
from
  hisa hisavo0_
  inner join establec establecvo6_ 
    on hisavo0_.cod_estab=establecvo6_.COD_ESTAB
  inner join disa disavo7_
    on establecvo6_.cod_disa=disavo7_.id
  inner join red redvo8_
    on establecvo6_.cod_red=redvo8_.id
  inner join microred microredvo9_
    on establecvo6_.cod_mic=microredvo9_.id
  inner join unidad_ejecutora unidad_eje10_
    on establecvo6_.cod_ue=unidad_eje10_.id
  cross join establec establecvo1_
  cross join disa disavo2_
  cross join red redvo3_
  cross join microred microredvo4_
  cross join unidad_ejecutora unidad_eje5_
group by redvo3_.red_nombre

It seems to be adding extra, unexpected cross joins at the end of the query. Why is it doing that?

Upvotes: 3

Views: 4731

Answers (1)

John Bollinger
John Bollinger

Reputation: 181714

You give your query multiple roots via multiple invocations of CriteriaQuery.from(). Each one after the first is reflected in the final query via a cross join. That's roughly what it means to be a query root.

You do not need to (and should not) use CriteriaQuery.from() to add entities to the query that you mean to be associated via inner joins corresponding to mapped relationships -- those you connect via a Join used as the Selection when you run your query.

Upvotes: 7

Related Questions