Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Incorrect SQL join generated in some cases
PostPosted: Thu Jul 05, 2007 12:36 pm 
Newbie

Joined: Thu Jul 05, 2007 12:04 pm
Posts: 4
Hibernate version:
3.0.2
Mapping documents:
There is a bidirectional one-many mapping from Chromosome to Gene.

Chromosome.hbm.xml:
<hibernate-mapping package="edu.wustl.fe">
<class name="Chromosome" table="CHROMOSOME" lazy="true" polymorphism="explicit">
<cache usage="read-write" />
<id name="id" type="java.lang.Long" column="CHR_ID">
<generator class="assigned" />
</id>
<property name="name" type="java.lang.String" column="CHROMOSOME" />
<set name="geneCollection" lazy="true" inverse="true">
<cache usage="read-write" />
<key column="EGE_CHR_ID" />
<one-to-many class="edu.wustl.fe.Gene" />
</set>

</class>
</hibernate-mapping>

Gene.hbm.xml:
<hibernate-mapping package="edu.wustl.fe">
<class name="Gene" table="FE_ENTREZGENE" lazy="true" polymorphism="explicit">
<cache usage="read-write" />
<id name="id" type="java.lang.Long" column="EGE_GENEID">
<generator class="assigned" />
</id>
<property name="symbol" type="java.lang.String" column="EGE_SYMBOL" />
<property name="name" type="java.lang.String" column="EGE_GENE_NAME" />
<property name="summary" type="java.lang.String" column="EGE_SUMMARY" />
<property name="pubmedCount" type="java.lang.Long" column="EGE_PUBMED_CT" />
<property name="chromosomeMap" type="java.lang.String" column="EGE_CHROMOSOME_MAP" />
<set name="unigeneCollection" table="FE_UNIGENE_ENTREZGENE" lazy="true">
<cache usage="read-write" />
<key column="FE_GENEID" />
<many-to-many class="edu.wustl.fe.Unigene" column="FE_ID" />
</set>
<set name="orthologusGeneCollection" table="ENTREZGENE_HOMOLOGENE" lazy="true" inverse="true">
<cache usage="read-write" />
<key column="HGE_GENEID" />
<many-to-many class="edu.wustl.fe.OrthologusGene" column="HML_ID" />
</set>
<set name="stsCollection" table="GENE_STSDATA" lazy="true">
<cache usage="read-write" />
<key column="GST_GENEID" />
<many-to-many class="edu.wustl.fe.STS" column="GST_STSID" />
</set>
<set name="literatureRelationshipCollection" table="GENE_LITERATURE" lazy="true">
<cache usage="read-write" />
<key column="GLT_EGE_GENEID" />
<many-to-many class="edu.wustl.fe.LiteratureRelationship" column="GLT_LIT_ID" />
</set>
<one-to-one name="geneVersion" class="edu.wustl.fe.GeneVersion" property-ref="gene" />
<set name="unigeneTissueSourceCollection" table="TISSUE_EXPRESSION" lazy="true">
<cache usage="read-write" />
<key column="GTE_GENEID" />
<many-to-many class="edu.wustl.fe.UnigeneTissueSource" column="GTE_STD_TERMID" />
</set>
<set name="snpCollection" lazy="true" inverse="true">
<cache usage="read-write" />
<key column="SNP_GENEID" />
<one-to-many class="edu.wustl.fe.SNP" />
</set>
<set name="geneOntologyCollection" table="GENE_GOTERM" lazy="true">
<cache usage="read-write" />
<key column="GOT_GENEID" />
<many-to-many class="edu.wustl.fe.GeneOntology" column="GOT_STD_TERMID" />
</set>
<many-to-one name="organism" class="edu.wustl.fe.Organism" column="EGE_LOCAL_TAXID" lazy="true" />
<many-to-one name="chromosome" class="edu.wustl.fe.Chromosome" column="EGE_CHR_ID" lazy="true" />
<set name="probesetCollection" table="GENE_PROBESET" lazy="true">
<cache usage="read-write" />
<key column="GPB_GENEID" />
<many-to-many class="edu.wustl.fe.Probeset" column="GPB_PRB_ID" />
</set>
<set name="phenoTypeCollection" table="GENE_PHENOTYPE" lazy="true">
<cache usage="read-write" />
<key column="GPH_GENEID" />
<many-to-many class="edu.wustl.fe.PhenoType" column="GPH_PHE_ID" />
</set>
<set name="omimCollection" table="GENE_OMIM" lazy="true" inverse="true">
<cache usage="read-write" />
<key column="GOM_GENEID" />
<many-to-many class="edu.wustl.fe.OMIM" column="GOM_OMIM_ID" />
</set>
</class>
</hibernate-mapping>

Following is an HQL for which correct SQL is generated for a join from Gene to Chromosome.
HQL:
From edu.wustl.fe.Gene as xxTargetAliasxxwhere xxTargetAliasxx.symbol LIKE '%GFER%' AND xxTargetAliasxx.chromosome.id in(select id From edu.wustl.fe.Chromosome where name = '16')

SQL generated (correct):
select * from ( select gene0_.EGE_GENEID as EGE1_, gene0_.EGE_SYMBOL as EGE2_19_, gene0_.EGE_GENE_NAME as EGE3_19_, gene0_.EGE_SUMMARY as EGE4_19_, gene0_.EGE_PUBMED_CT as EGE5_19_, gene0_.EGE_CHROMOSOME_MAP as EGE6_19_, gene0_.EGE_LOCAL_TAXID as EGE7_19_, gene0_.EGE_CHR_ID as EGE8_19_ from FE_ENTREZGENE gene0_ where(gene0_.EGE_SYMBOL like '%GFER%') and (gene0_.EGE_CHR_ID in (select chromosome1_.CHR_ID from CHROMOSOME chromosome1_ where chromosome1_.CHROMOSOME='16')) ) where rownum <= ?


But for following HQL, a similar join from Gene to Chromosome is generated incorrectly in the SQL.

HQL:
From edu.wustl.fe.Chromosome as xxTargetAliasxx where xxTargetAliasxx.name = '16' AND xxTargetAliasxx.geneCollection.id in (select id From edu.wustl.fe.Gene where symbol LIKE '%GFER%' AND chromosome.id in (select id From edu.wustl.fe.Chromosome where name = '16'))

Incorrect SQL generated:
select * from ( select chromosome0_.CHR_ID as CHR1_, chromosome0_.CHROMOSOME as CHROMOSOME25_ from CHROMOSOME chromosome0_, FE_ENTREZGENE genecollec1_ where chromosome0_.CHR_ID=genecollec1_.EGE_CHR_ID and chromosome0_.CHROMOSOME='16' and (genecollec1_.EGE_GENEID in (select gene2_.EGE_GENEID from FE_ENTREZGENE gene2_ where (gene2_.EGE_SYMBOL like '%GFER%') and (gene2_.EGE_GENEID in (select chromosome3_.CHR_ID from CHROMOSOME chromosome3_ where chromosome3_.CHROMOSOME='16')))) ) where rownum <= ?


Name and version of the database:
oracle9


Last edited by srinath on Sun Jul 08, 2007 2:57 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Tuning fetch strategy
PostPosted: Fri Jul 06, 2007 5:50 pm 
Newbie

Joined: Tue Jun 19, 2007 3:47 am
Posts: 4
You may want to try all fetch strategies (ie. "join", "select" and "subselect") to see which of them gives you the best results.

For more information refer to Hibernate reference (http://www.hibernate.org/hib_docs/refer ... mance.html) section 19.1.2

Please rate this post if it was helpful to you.

Dusan


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jul 07, 2007 3:20 am 
Regular
Regular

Joined: Mon Apr 02, 2007 3:54 am
Posts: 67
Location: Hyderabad
try ur hql like this
From edu.wustl.fe.Gene as xxTargetAliasxx where xxTargetAliasxx.symbol LIKE '%GFER%' AND xxTargetAliasxx.chromosome.id in(select id From xxTargetAliasxx.chromosome where xxTargetAliasxx.chromosome.name = '16')

Thanks,
Gopal
[please rate, if helpful]


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.