Criteria API to build SQL with inner joins, when HSQL works

young_matthewd · **Joined:** Sun Oct 09, 2005 3:21 pm **Posts:** 40

Hibernate version: 3.1.rc2

Mapping documents:
Resurs (resource)

Code:

<class name="Resurs" table="RESURS">
   <id name="id">
      <column name="RESURSID" sql-type="INTEGER"/>
      <generator class="identity">
      </generator>
   </id>
....
                <set name="egnaresurser" lazy="true" inverse="true" cascade="save-update" where="TOMTSTAMP is null">
      <key column="RESURSID1" on-delete="noaction" not-null="true"/>
      <one-to-many class="ResursRelation"/>
   </set>
</class>

ResursRelation (relationship between resources)

Code:

<hibernate-mapping package="matrix.v.dataaccess.pojo">
<class name="ResursRelation" table="RESURSRELATION">
   <composite-id>
      <key-many-to-one lazy="false" name="resurs1" class="Resurs" foreign-key="FK_RESREL_RES1">
         <column name="RESURSID1" sql-type="INTEGER"/>
      </key-many-to-one>
      <key-many-to-one lazy="false" name="resurs2" class="Resurs" foreign-key="FK_RESREL_RES2">
         <column name="RESURSID2" sql-type="INTEGER"/>
      </key-many-to-one>
      <key-many-to-one lazy="false" name="typ" class="ResursRelationtyp" foreign-key="FK_RESRELT_RESREL">
         <column name="RESRELTYPID" sql-type="INTEGER"/>
      </key-many-to-one>
      <key-property name="frantstamp" column="FRANTSTAMP" type="timestamp"/>
   </composite-id>
....
</class>

Problem
Have seen similar questions on the forum about traversing collection association with multiple inner joins that work fine with HSQL but give undefined table aliases with the Criteria API. The following HSQL works great against the mapping documents:

Code:

select version Version version join version.egnaresurser relation join relation.typ relationtyp join relation.resurs2 relationresurs where relationtyp.beteckning='instans' and relationresurs.beteckning='SAE'

whereas with the Criteria API:

Code:

Criteria versionCriteria = session.createCriteria(Version.class)
                 .createAlias("egnaresurser","egnaresurs");

Criteria egnaresursTyp = versionCriteria.createCriteria("egnaresurs.typ");
                 egnaresursTyp.add( Expression.eq("beteckning","instans") );
                 
                 Criteria egnaresursResurs2 = versionCriteria.createCriteria("egnaresurs.resurs2");
                 egnaresursResurs2.add( Expression.eq("beteckning",kriteria.getSubdomänBeteckning()) );

generates the first inner join to the collection (egenresurser) fine but the subsequent inner joins to the ResursRelationtyp table or back to the Resurs table. However, the where condition has unmapped aliases (to the missing table joins) correctly defining the expression declarations.

Question
What to do? Would love to use Criteria API rather than building dynamic HSQL strings.

young_matthewd · **Joined:** Sun Oct 09, 2005 3:21 pm **Posts:** 40

With HQL moving through an association (collection) does not work with implicit joins. Trying something line:

Code:

from Resurs where resurs.egenresurser.typ.beteckning='miljö'

Errors out saying that "typ" is unresolved. However, a simple join to the Resurstyp table solves the problem. Wonder if this and the original question are related? / matthew

zzyborg · **Posted:** Tue Jan 17, 2006 5:29 am

there is a concept of nested criteria . i dont know if it is helpful in your case . if so plz do inform :)

young_matthewd · **Joined:** Sun Oct 09, 2005 3:21 pm **Posts:** 40

Luckly in my project I have the ability to alter the database model. Thus I just added an unique column (via id generic generator) thereby allowing Hibernate to keep track of my relation table objects (i.e. the composite key disappeared).