Criteria API joins

Hoffy · **Joined:** Fri Nov 25, 2005 9:01 am **Posts:** 4

Class A contains an instance of Class B.

Class A data:

Code:

id      colA        bId
1         x           1
2         y           2
3         z          <null>

Class B data:

Code:

id      colB
1         a
2         b

I am trying to use the Criteria API to get a list of all Class A objects, which is sorted by colB in ClassB. The problem is I'm finding that the join to the Class B table is inner and therefore I'm only getting the first 2 ClassA objects NOT ALL 3 as desired!

Code:
Criteria crit = session.createCriteria(ClassA.class)
crit.createCriteria( bId );
crit.setFetchMode( bId, FetchMode.JOIN );

Mapping File ClassA:

Code:

<many-to-one name="bId" class="ClassB" not-null="false" not-found="exception" optimistic-lock="true" update="false" unique="false" insert="false" lazy="no-proxy" embed-xml="false">
    <column name="B_FK" length="16" not-null="false"/>
</many-to-one>

I know this topic has been discussed before but i cant seem to find a 100% definitive answer. Although it sounds like it should be possible for such a simple example.

Should it be possible to fetch ALL the Class A objects using the criteria API having specified createCriteria() to ClassB? If so am I missing something really simple

thanks in advance!

anandbn · **Posted:** Fri Nov 25, 2005 4:08 pm

You can use a "nameQuery" using "left outer join". here's a working example:

Hibernate mapping XMLs

Code:

<hibernate-mapping>
    <class name="com.hibernate.forums.Table1" 
           table="TABLE_1" >
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="assigned" />
        </id>
        <property name="colA" type="string">
            <column name="COLA"/>
        </property>   
        
      <many-to-one name="colBID" class="com.hibernate.forums.Table2"  not-null="false" not-found="exception"  optimistic-lock="true"  update="false" unique="false"  insert="false" lazy="false"  embed-xml="false"> 
          <column name="B_ID" not-null="false"/> 
      </many-to-one>             
    </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="com.hibernate.forums.Table2" 
           table="TABLE_2">
        <id name="id" type="integer">
            <column name="ID" />
            <generator class="assigned" />
        </id>
        <property name="colB" type="string">
            <column name="COLB"/>
        </property>        
    </class>
</hibernate-mapping>

Code to fetch all three rows:

Code:

      Session tSession = HibernateUtil.currentSession();
      Criteria crit = tSession.createCriteria(Table1.class);
      Query tQuery = tSession.createQuery("select tab1 from Table1 tab1 left join tab1.colBID");
      
      List tList = tQuery.list();
      System.out.println(tList);\

Data in tables

Code:

Table_1
Id  ColA  B_ID
1   x   1
2   y   2
3   z   (null)

Table_2
Id   ColB
1   a
2   b

Results:
1,x,com.hibernate.forums.Table2@6bade9
2,y,com.hibernate.forums.Table2@12d96f2
3,z,null

Hoffy · **Joined:** Fri Nov 25, 2005 9:01 am **Posts:** 4

Thanks for the reply

Granted his is one possible solution, but its not strictly using the Criteria API. And since I already have a lot of code which builds up queiries based on the Criteria API, implementing this would be a large change!

hhuber · **Posted:** Mon Nov 28, 2005 4:12 am

Perhaps Criteria.setFetchMode with FetchMode.JOIN might help.
According to the docs it should result in an outer join.

Hoffy · **Joined:** Fri Nov 25, 2005 9:01 am **Posts:** 4

I have already tried that (see above).

This is what i tought the solution should be but it doesnt seem to work i still loose the Class A object with a null Class foreign key!

Should this be the solution? Can anyone suggest why it might not be working??

thanks