Is it supposed to work like this?

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

I'm using Hibernate 2.1.1.
As you can see below, mpReqItems is a "set" of MpRequisicaoSq044. So with the registers shown below the returned list must have one object of MpRequisicaoSq044 and two in it's mpReqItems "set". But that's not happening! It's returning two objects of MpRequisicaoSq044 each one with two in it's mpReqItems "set". Is my method code correct?
Please help.
I need to understand this. It's fundamental for Hibernate knowledge.

The testing code is returning me (which I don't want):

Code:

SIZE: 2
----- MP_REQUISICAO -----
ID: 1 + DATA: 2003-12-14
----- MP_REQ_ITEM -----
ID: 1 + QTD: 220
ID: 1 + QTD: 110
------------------------------------
----- MP_REQUISICAO -----
ID: 1 + DATA: 2003-12-14
----- MP_REQ_ITEM -----
ID: 1 + QTD: 220
ID: 1 + QTD: 110
------------------------------------

instead of returning:

Code:

SIZE: 1
----- MP_REQUISICAO -----
ID: 1 + DATA: 2003-12-14
----- MP_REQ_ITEM -----
ID: 1 + QTD: 220
ID: 1 + QTD: 110
------------------------------------

I have two tables: MP_REQUISICAO_SQ044 and MP_REQ_ITEM.

In my DB I have:

MP_REQUISICAO_SQ044 (1 register)

Code:

id: 1
data_requisicao: 2003-12-14

MP_REQ_ITEM (2 registers)

Code:

mp_requisicao_fk: 1 (FK to above table)
mp_fk: 1
quantidade: 220

mp_requisicao_fk: 1 (FK to above table)
mp_fk: 2
quantidade: 110

---------------------
My mapping files
---------------------

MpRequisicaoSq044

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.MpRequisicaoSq044" 
    table="mp_requisicao_sq044"
>

    <id
        name="id"
        type="long"
        column="id"
    >
        <generator class="increment" />
    </id>

    <property
        name="dataRequisicao"
        type="java.sql.Date"
        column="data_requisicao"
        not-null="true"
        length="4"
    />

    <!-- associations -->
    <!-- bi-directional one-to-many association to MpReqItem -->
    <set
        name="mpReqItems"
        lazy="true"
        inverse="true"
    >
        <key>
            <column name="mp_requisicao_fk" />
        </key>
        <one-to-many 
            class="vo.MpReqItem"
        />
    </set>

</class>
</hibernate-mapping>

MpReqItem

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="vo.MpReqItem" 
    table="mp_req_item"
>

    <composite-id name="comp_id" class="pt.comseal.arsol.vo.MpReqItemPK">
        <!-- bi-directional many-to-one association to MpRequisicaoSq044 -->
        <key-many-to-one
           name="mpRequisicaoSq044"
           class="vo.MpRequisicaoSq044"
       >
           <column name="mp_requisicao_fk" />
       </key-many-to-one>
        <!-- bi-directional many-to-one association to Mp -->
        <key-many-to-one
           name="mp"
           class="vo.Mp"
       >
           <column name="mp_fk" />
       </key-many-to-one>
    </composite-id>    

    <property
        name="quantidade"
        type="int"
        column="quantidade"
        length="4"
    />

    <!-- associations -->

</class>
</hibernate-mapping>

----------------
Method code
----------------

Here is my code to retrieve a list of MpRequisicaoSq044 with the mpReqItems set eagerly loaded:

Code:

public static List consultarMpRequisicao(Date data_ini, Date data_fim) throws MpBOException, BadFactoryException {
   Session session = null;
   List res = null;
      
   try {
   SessionFactory sessionFactory = HibernateFactory.createFactory();   
   session = sessionFactory.openSession();

   if(data_ini != null && data_fim == null) {
      res = session.find(
         "from vo.MpRequisicaoSq044 mr " +
         "left join fetch mr.mpReqItems where mr.dataRequisicao >= ? order by mr.dataRequisicao",
         data_ini,
         Hibernate.DATE          
      );   
   } else if (data_ini == null && data_fim != null) {
      res = session.find(
         "from vo.MpRequisicaoSq044 mr " +
         "left join fetch mr.mpReqItems where mr.dataRequisicao <= ? order by mr.dataRequisicao",
         data_fim,
         Hibernate.DATE   
      );
   } else if (data_ini != null && data_fim != null) {   
      res = session.find(
         "from vo.MpRequisicaoSq044 mr " +
         "left join fetch mr.mpReqItems where mr.dataRequisicao >= ? and mr.dataRequisicao <= ? order by mr.dataRequisicao",
         new Object[] {data_ini, data_fim},
         new Type[] {Hibernate.DATE, Hibernate.DATE}
      );
   } else {
      res = session.find(
         "from vo.MpRequisicaoSq044 mr " +
         "left join fetch mr.mpReqItems order by mr.dataRequisicao"
      );
   }
         
   session.close();
          
   }
   catch(HibernateException e) {
   throw new MpBOException("Error.");   
   }
   catch(BadFactoryException e) {
   throw new BadFactoryException(e.getMessage());
   }   
   finally {
      try {
         if(session != null) session.close();
      }
      catch(HibernateException e) {throw new MpBOException("Error.");}   
   }
   return(res);
}

------------------
Testing code
------------------

Code:

try {
   List res = MpBO.consultarMpRequisicao(Date.valueOf("2003-12-12"), Date.valueOf("2003-12-14"));
   System.out.println("SIZE: " + res.size());
   Iterator it = res.iterator();
   while(it.hasNext()) {
      MpRequisicaoSq044 mp_req = (MpRequisicaoSq044)it.next();
      System.out.println("----- MP_REQUISICAO -----");
      System.out.println("ID: " + mp_req.getId() + " + DATA: " + mp_req.getDataRequisicao());
      Iterator it1 = mp_req.getMpReqItems().iterator();
      System.out.println("----- MP_REQ_ITEM -----");
      while(it1.hasNext()) {
         MpReqItem mp_req_item = new MpReqItem();
         mp_req_item = (MpReqItem)it1.next();
         System.out.println("ID: "+mp_req_item.getComp_id().getMpRequisicaoSq044().getId()+ 
   " + QTD: "+mp_req_item.getQuantidade());
      }
      System.out.println("------------------------------------");
   }
}
catch(MpBOException e) {
   System.out.println(e.getMessage());   
}
catch(BadFactoryException e) {
   System.out.println(e.getMessage());   
}

emmanuel · **Posted:** Mon Dec 29, 2003 6:51 am

http://forum.hibernate.org/viewtopic.php?t=926643

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

epbernard wrote:

http://forum.hibernate.org/viewtopic.php?t=926643

I tried to "distinct" my result like this:

Code:

( ... )
res = session.find(
      "select distinct(mr) from vo.MpRequisicaoSq044 mr " +
      "left join fetch mr.mpReqItems where mr.dataRequisicao >= ? and mr.dataRequisicao <= ? order by mr.dataRequisicao",
      new Object[] {data_ini, data_fim},
      new Type[] {Hibernate.DATE, Hibernate.DATE}                
);
( ... )

But it gives me the following error:

Code:

net.sf.hibernate.QueryException: aggregate function expected before ( in SELECT [select distinct(mr) from vo.MpRequisicaoSq044 mr left join fetch mr.mpReqItems where mr.dataRequisicao >= ? and mr.dataRequisicao <= ? order by mr.dataRequisicao]
   at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:91)
( ... )

Any ideas please?
Am I doing this correctly?

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

nerotnt wrote:

epbernard wrote:

http://forum.hibernate.org/viewtopic.php?t=926643

Another thing.
If I "dinstinct" my results then I will not get the two registers that I want, right!?
It will only give me one of them because of the "distinct". I won't have duplicate parent objects and so I will not have all of its childs.

Please explain.

emmanuel · **Posted:** Mon Dec 29, 2003 9:11 am

You cannot distinct using the HQL keyword, you need to distinct them in your java code manipulating the List returned.

Hibernate is consistent with the SQL generated and will return the cartesian product, thus Hibernate will always return 2 MpRequisicaoSq044 (actually the same 2 times).
One solution is the one dep4b use

Code:

List list =
                list = session.find(
                    "select r.replicate from Reaction r left outer join fetch r.replicate.reactions where r.replicate.reworkF=false and r.workorder.id=?",
                    new Long(workorder.getId()),
                    Hibernate.LONG);

            Set uniqueSet = new HashSet();
            uniqueSet.addAll(list);
            return Arrays.asList(uniqueSet.toArray());

Not the fastest however (depends on you business code for optimization)

nerotnt · **Joined:** Wed Oct 08, 2003 10:31 am **Posts:** 247

epbernard wrote:

You cannot distinct using the HQL keyword, you need to distinct them in your java code manipulating the List returned.

Hibernate is consistent with the SQL generated and will return the cartesian product...

Thanks Emmanuel for the reply.