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

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

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]