Hello,

I've got a problem and maybe you can help me.
I have a table ITEM and a table COLOR.
I have created a xml map for the table Item.
I have created a SQL to call and obtain a list of items with its colors.


Hibernate version: 3.2

Mapping documents: item.hbm.xml

<hibernate-mapping package="xxxxxxxxxxxxxxxxxx.model" default-lazy="false">
<class name="Item" table="S3T_OGG">
<id name="ModelPartId" column="OGG_ID" />
<property name="ModelId" column="OGG_MOD_ID" />
<property name="PartId" column="OGG_PAR_ID" />
<property name="Year" column="OGG_STG_ANNO" />
<property name="Season" column="OGG_STG_COD" />
<property name="Description" column="OGG_DES" />
<property name="Model" column="OGG_COD" insert="false" update="false"/>
<property name="Part" column="OGG_COD" insert="false" update="false"/>
<property name="ColorId" insert="false" update="false"/>
<property name="ColorDescription" insert="false" update="false"/>
</class>


<!-- SEARCH QUERY -->
<sql-query name="itemSearchQuery" >
<return alias="item" class="Item">
<return-property name="ModelPartId" column="ModelPartId"/>
<return-property name="ModelId" column="ModelId"/>
<return-property name="PartId" column="PartId"/>
<return-property name="Year" column="Year"/>
<return-property name="Season" column="Season"/>
<return-property name="Description" column="Description"/>
<return-property name="Model" column="Model"/>
<return-property name="Part" column="Part"/>
<return-property name="ColorId" column="ColorId"/>
<return-property name="ColorDescription" column="ColorDescription"/>
</return>
SELECT
o.ogg_id AS {item.ModelPartId},
o.ogg_mod_id AS {item.ModelId},
o.ogg_par_id AS {item.PartId},
(select ogg_cod
from s3t_ogg
where ogg_id=o.ogg_mod_id) AS {item.Model},
(select ogg_cod
from s3t_ogg
where ogg_id=o.ogg_par_id) AS {item.Part},
c.ogg_col_col_cod AS {item.ColorId},
c.ogg_col_des AS {item.ColorDescription},
o.ogg_des AS {item.Description},
:year AS {item.Year},
'90' AS {item.Season}
FROM s3t_ogg o,
s3t_ogg_col c
WHERE
o.ogg_tipo='5'
and c.ogg_col_ogg_id = o.ogg_id
and o.ogg_soc_cod = 'XX'
and o.ogg_id IN (
select stg_clz_ogg_id from pggat_stg_clz
where stg_clz_clz_soc_cod = o.ogg_soc_cod
and stg_clz_stg_anno = :year
and stg_clz_stg_cod = '90'
and stg_clz_clz_cod = '500000'
)
</sql-query>


Code between sessionFactory.openSession() and session.close():
I have extended HibernateDaoSupport and call the query with:
@SuppressWarnings("unchecked")
public List<Item> getItems() {
return getHibernateTemplate().findByNamedQueryAndNamedParam("itemSearchQuery", "year", "2008");
}

Name and version of the database you are using: Oracle9i

The generated SQL (show_sql=true): the SQL is ok.


I guess there is a mistake on the mapping ColorID and ColorDescription fields, because they belong to a join table.

Let's see what happen in a real example:

Query executed by TOAD:

MODEL COLORID COLORDESC
06H13Q3 E1 RED
06H13Q3 E2 WHITE
06H13Q3 E5 BLUNAVY

Query executed by HbmApp:

MODEL COLORID COLORDESC
06H13Q3 E5 BLUNAVY
06H13Q3 E5 BLUNAVY
06H13Q3 E5 BLUNAVY

As you can see the ColorID has been repeated.

Any help?
thanks