Hello
Context and problem
I have a problem with a "complex" query with hql. I have two classes Detection and Melody.
Class DeteccionMusica represents events about music detections of music hits in radios.
and Melodia is the information related of the melody itself like de author, interpreter, rhythm etc.
I want to load a count of detection for each melody in one single query, i mean... i want hibernate to generate only one sql
Hibernate version: 3.2.6ga
database: MYSQL 5
Mapping documents:
Code:
<class name="DeteccionMusica"
table="evento_nuevo" lazy="false">
<cache usage="read-only"/>
<id name="id" column="Posicion"/>
<property name="medio"></property>
<property name="momento" column="momento"></property>
<many-to-one name="melodia" class="Melodia"
column="melodia"
cascade="none"
update="false"
lazy="false"
insert="false">
</many-to-one>
</class>
<class name="Melodia" table="melodia" lazy="false" batch-size="20">
<id name="codigo" column="codigo"/>
<property name="nombre"></property>
<many-to-one name="variante" class="Melodia"
column="variante"
fetch="join"
cascade="none"
outer-join="true">
</many-to-one>
<many-to-one name="interprete" class="Interprete"
column="interprete"
fetch="join"
cascade="none"
outer-join="true">
</many-to-one>
....
Code:
select distinct new model.DeteccionesPorSemana(m, count(*))
from DeteccionMusica h join h.melodia as m
where (h.momento >= :inicio and h.momento <= :fin)
group by m
order by count(*) desc
Code to execute the query:Code:
Query q = this.queryDao.createQuery(hql);
q.setMaxResults(10);
q.setDate("inicio", semanas[0][0]);
q.setDate("fin", semanas[0][1]);
List<DeteccionesPorSemana> detecs =
List<DeteccionesPorSemana>)q.list();
The generated SQL (show_sql=true):Code:
Hibernate: select distinct top 10 melodia1_.codigo as col_0_0_, count(*) as col_1_0_ from evento_nuevo deteccionm0_ inner join melodia melodia1_ on deteccionm0_.melodia=melodia1_.codigo where deteccionm0_.momento>=? and deteccionm0_.momento<=? group by melodia1_.codigo order by count(*) desc
Hibernate: select melodia0_.codigo as codigo7_5_, melodia0_.nombre as nombre7_5_, melodia0_.variante as variante7_5_, melodia0_.interprete as interprete7_5_, melodia0_.casa as casa7_5_, melodia0_.categoria as categoria7_5_, melodia1_.codigo as codigo7_0_, melodia1_.nombre as nombre7_0_, melodia1_.variante as variante7_0_, melodia1_.interprete as interprete7_0_, melodia1_.casa as casa7_0_, melodia1_.categoria as categoria7_0_, interprete2_.codigo as codigo9_1_, interprete2_.nombre as nombre9_1_, casa3_.codigo as codigo10_2_, casa3_.nombre as nombre10_2_, categoriam4_.codigo as codigo11_3_, categoriam4_.nombre as nombre11_3_, autores5_.Melodia as Melodia7_, autor6_.codigo as Autor7_, autor6_.codigo as codigo12_4_, autor6_.Nombre as Nombre12_4_ from melodia melodia0_ left outer join melodia melodia1_ on melodia0_.variante=melodia1_.codigo left outer join interprete interprete2_ on melodia1_.interprete=interprete2_.codigo left outer join Casa casa3_ on melodia1_.casa=casa3_.codigo left outer join Categoria categoriam4_ on melodia1_.categoria=categoriam4_.codigo left outer join AutorMelodia autores5_ on melodia1_.codigo=autores5_.Melodia left outer join autor autor6_ on autores5_.Autor=autor6_.codigo where melodia0_.codigo=?
Hibernate: select melodia0_.codigo as codigo7_5_, melodia0_.nombre as nombre7_5_, melodia0_.variante as variante7_5_, melodia0_.interprete as interprete7_5_, melodia0_.casa as casa7_5_, melodia0_.categoria as categoria7_5_, melodia1_.codigo as codigo7_0_, melodia1_.nombre as nombre7_0_, melodia1_.variante as variante7_0_, melodia1_.interprete as interprete7_0_, melodia1_.casa as casa7_0_, melodia1_.categoria as categoria7_0_, interprete2_.codigo as codigo9_1_, interprete2_.nombre as nombre9_1_, casa3_.codigo as codigo10_2_, casa3_.nombre as nombre10_2_, categoriam4_.codigo as codigo11_3_, categoriam4_.nombre as nombre11_3_, autores5_.Melodia as Melodia7_, autor6_.codigo as Autor7_, autor6_.codigo as codigo12_4_, autor6_.Nombre as Nombre12_4_ from melodia melodia0_ left outer join melodia melodia1_ on melodia0_.variante=melodia1_.codigo left outer join interprete interprete2_ on melodia1_.interprete=interprete2_.codigo left outer join Casa casa3_ on melodia1_.casa=casa3_.codigo left outer join Categoria categoriam4_ on melodia1_.categoria=categoriam4_.codigo left outer join AutorMelodia autores5_ on melodia1_.codigo=autores5_.Melodia left outer join autor autor6_ on autores5_.Autor=autor6_.codigo where melodia0_.codigo=?
Hibernate: select melodia0_.codigo as codigo7_5_, melodia0_.nombre as nombre7_5_, melodia0_.variante as variante7_5_, melodia0_.interprete as interprete7_5_, melodia0_.casa as casa7_5_, melodia0_.categoria as categoria7_5_, melodia1_.codigo as codigo7_0_, melodia1_.nombre as nombre7_0_, melodia1_.variante as variante7_0_, melodia1_.interprete as interprete7_0_, melodia1_.casa as casa7_0_, melodia1_.categoria as categoria7_0_, interprete2_.codigo as codigo9_1_, interprete2_.nombre as nombre9_1_, casa3_.codigo as codigo10_2_, casa3_.nombre as nombre10_2_, categoriam4_.codigo as codigo11_3_, categoriam4_.nombre as nombre11_3_, autores5_.Melodia as Melodia7_, autor6_.codigo as Autor7_, autor6_.codigo as codigo12_4_, autor6_.Nombre as Nombre12_4_ from melodia melodia0_ left outer join melodia melodia1_ on melodia0_.variante=melodia1_.codigo left outer join interprete interprete2_ on melodia1_.interprete=interprete2_.codigo left outer join Casa casa3_ on melodia1_.casa=casa3_.codigo left outer join Categoria categoriam4_ on melodia1_.categoria=categoriam4_.codigo left outer join AutorMelodia autores5_ on melodia1_.codigo=autores5_.Melodia left outer join autor autor6_ on autores5_.Autor=autor6_.codigo where melodia0_.codigo=?
....
and continue with one query for each Melody
Question How can i froce the join fetch between DeteccionMusica and Melodia, to make hibernate generate only one SQL Query?
i cant put a feth in the form clause becasue hibernate throw me this error
Code:
from DeteccionMusica h join fetch h.melodia as m
Error
Code:
org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=m,role=null,tableName=melodia,tableAlias=melodia1_,origin=evento_nuevo deteccionm0_,colums={deteccionm0_.melodia ,className=ar.com.gpi.monitec.model.musica.Melodia}}] [ select distinct new ar.com.gpi.monitec.server.report.model.DeteccionesPorSemana(m, count(*)) from ar.com.gpi.monitec.model.musica.DeteccionMusica h join fetch h.melodia as m where (h.momento >= :inicio and h.momento <= :fin) group by m order by count(*) desc ]
Login
FAQ
Search