Collections and join problems

TarikS · **Posted:** Fri Feb 11, 2005 6:45 am

Hello

I am having difficulties with collections. My issue is that when I execute the named query down below I get both: Services with the initialised abo collections and for each abo a service object. Or explained differently, I have 1000 abo objects which are valid for a certian period of time and 100 services. When I fetch all services I get 100 and the abos distribute in the collections.

If I use join as shown down below I get 1000 services, but each service has its proper collection, but this is definetely not what I need.

So as it seems that I must misunderstand something I would greatly appreciate any help or hints.

Regards
Tarik

Hibernate version:
2

Mapping documents:

Code:

<hibernate-mapping>
   
   <class name="com.someCompany.MMSCAdmin.beans.Service" table="SERVICE" lazy="true" batch-size="20" >
      <cache usage="read-write"/>
      <id name="serviceID" type="long" column="SERVICE_ID">
         <generator class="assigned"/>
      </id>
      <property name="name" type="java.lang.String" column="NAME" not-null="true" length="100"/>
      <property name="serviceTable" type="java.lang.String" column="SERVICETABLE" not-null="true" length="100"/>
      <!-- Associations -->
      <map name="keywords" lazy="true" sort="natural" order-by="KEYWORD">
         <key column="SERVICE_ID"/>
         <index column="KEYWORD" type="string"/>
         <one-to-many class="com.someCompany.MMSCAdmin.beans.Keyword"/>
      </map>
      <map name="abos"  lazy="true" sort="natural" order-by="SENDDATESTART">
         <key column="SERVICE_ID"/>
         <index column="MMSABO_ID" type="int"/>
         <one-to-many class="com.someCompany.MMSCAdmin.beans.MMSAbo"/>
      </map>
   </class>
   
   <query name="com.someCompany.MMSCAdmin.beans.Service.getAllServicesByAboDates"><![CDATA[
         from com.mobilelogix.MMSCAdmin.beans.Service as service
         left join fetch service.abos as abo   
         where abo.senddateStart >= :senddateStart
         and abo.senddateEnd <= :senddateEnd         
         ]]></query>   
</hibernate-mapping>

hibernate-mapping>
   
   <class name="com.someCompany.MMSCAdmin.beans.MMSAbo" table="MMSABO" lazy="true" batch-size="20" >
      <cache usage="read-write"/>
      <id name="mmsaboID" type="long" column="MMSABO_ID">
         <generator class="assigned"/>
      </id>
      <property name="senddateStart" type="java.util.Date" column="SENDDATESTART" length="19"/>
      <property name="senddateEnd" type="java.util.Date" column="SENDDATEEND" length="19"/>
      <property name="serviceID" type="long" column="SERVICE_ID" not-null="true" length="20"/>
      <property name="composedID" type="long" column="COMPOSED_ID" not-null="true" length="20"/>
      <!-- Associations -->
   </class>   
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

/**
     * @param abo
     * @return @throws
     *         HibernateException
     */
    public static List getAllServicesByAboDates(MMSAbo abo)
            throws HibernateException {
        Session session = HibernateUtil.currentSession();
        Query query = session
                .getNamedQuery("com.someCompany.MMSCAdmin.beans.Service.getAllServicesByAboDates");
        query.setString("senddateEnd", abo.getSenddateEndAsString());
        query.setString("senddateStart", abo.getSenddateStartAsString());
        query.setCacheable(true);
        return query.list();
    }

Name and version of the database you are using:
MySQL 4.0.16

[/b]

anthony · **Posted:** Fri Feb 11, 2005 7:47 am

fetch return distinct root instances

join has the same meaning as SQL join, so just use
Set results = new HashSet(query.list()) if you don't want duplicate results.

TarikS · **Posted:** Fri Feb 11, 2005 8:18 am

Hello Anthony

Thank you very much for your help. It works :-)

Even though I do not understand why this is implemented like that. Could you give me a hint on which sources I could look up to have a better understandig about this "mechanic"?

Thanks again
Regards
Tarik

TarikS · **Posted:** Fri Feb 11, 2005 8:35 am

There is one problem which occurs now, ordering (ORDER BY) is lost :-(

TarikS · **Posted:** Fri Feb 11, 2005 8:50 am

With the source code to understand it better, what I posted earlier, it must be around the Query implementation I guess :-)

Eliseo · **Posted:** Wed Aug 10, 2005 1:06 am

TarikS wrote:

There is one problem which occurs now, ordering (ORDER BY) is lost :-(

TarikS you should check this out:
http://forum.hibernate.org/viewtopic.ph ... 86#2255186

The sorting is preserved using that approach.