I have a many to many relationship between Groups and Products
with additional attributes for hte relationship.

Below is my mapping file.


<class name="Groups" table="groups">
<id name="id" column="group_id" type="long" unsaved-value="null">
<generator class="increment"/>
</id>


<bag name="productData"
table="PRODUCT_GROUPS"
lazy="true" inverse="false" cascade="none">
<key column="GROUP_ID"/>
<composite-element class="ProductGroupData">
<property name="comment" column="COMMENT" type="string" />
<property name="createDate" column="CREATE_DATE" type="date" />
<property name="updateDate" column="UPDATE_DATE" type="date" />
<property name="createdBy" column="CREATED_BY" type="long" />
<property name="updatedBy" column="UPDATED_BY" type="long" />
<many-to-one name="product"
class="Product" column="PRODUCT_ID" cascade="none" outer-join="auto"/>
</composite-element>
</bag>

</class>



In Java -> productData is represented as java.util.List

when I do group.getProductData() -> I get below exception .

"Hibernate: select productdat0_.COMMENT as COMMENT__, productdat0_.CREATE_DATE as CREATE_D3___, productdat0_.UPDATE_DATE as UPDATE_D4___, productdat0_.CREATED_BY as CREATED_BY__, productdat0_.UPDATED_BY as UPDATED_BY__, productdat0_.PRODUCT_ID as PRODUCT_ID__, productdat0_.GROUP_ID as GROUP_ID__, product1_.PRODUCT_ID as PRODUCT_ID0_, product1_.PRODUCT_NO as PRODUCT_NO0_, product1_.PRODUCT_DESCRIPTION as PRODUCT_3_0_, product1_.SD_BUNDLE_TAG as SD_BUNDL4_0_, product1_.SD_BUNDLE_TITLE as SD_BUNDL5_0_, product1_.SD_BUNDLE_DESCRIPTION as SD_BUNDL6_0_, product1_.CATEGORY_ID as CATEGORY7_0_, product1_.PRODUCT_TYPE_ID as PRODUCT_8_0_, product1_.DELIVERY_FLAG as DELIVERY9_0_, product1_.DELIVERY_DESCRIPTION as DELIVER10_0_, product1_.PRODUCT_LINE_ID as PRODUCT11_0_, product1_.CPL_PRODUCT_DESCRIPTION as CPL_PRO12_0_, product1_.CPL_STATUS as CPL_STATUS0_, product1_.CPL_STATUS_DATE as CPL_STA14_0_, product1_.MARKETING_DIVISION as MARKETI15_0_, product1_.ROYALTY_FLAG as ROYALTY16_0_, product1_.STATUS_FLAG as STATUS_17_0_, product1_.STATUS_DATE as STATUS_18_0_, product1_.CREATED_BY as CREATED_BY0_, product1_.CREATE_DATE as CREATE_20_0_, product1_.UPDATED_BY as UPDATED_BY0_, product1_.UPDATE_DATE as UPDATE_22_0_ from PRODUCT_GROUPS productdat0_ left outer join PRODUCTS product1_ on productdat0_.PRODUCT_ID=product1_.PRODUCT_ID where productdat0_.GROUP_ID=?

[WARN:main:][04/15/04 18:58:37] SQL Error: 1747, SQLState: 42000

[ERROR:main:][04/15/04 18:58:37] ORA-01747: invalid user.table.column, table.column, or column specification


[WARN:main:][04/15/04 18:58:37] SQL Error: 1747, SQLState: 42000

[ERROR:main:][04/15/04 18:58:37] ORA-01747: invalid user.table.column, table.column, or column specification

"

Please Help !!

Did you try to execute that SQL statement directly in the Oracle SQL console? It looks good to me.

Great Thanks !! that was it- I had a column name got wrong.
Phew !! What a relief after searching for about 2 hours..

Well, the error message should've been giving you a clear direction :)

I have another question for this particular problem -
currenly my intermediate table has primary key as


A_ID (FK to ID of table A) PK
B_ID (FK to ID of table B) PK

Is it possible to somehow have additional column in this table (joining the mapping table ) as AB_ID (which uniquely identifies the record in this table ?

How would my mapping change to support this ?

Also another question -> how can I make this composite-element bi-directional -Is it the same way -> add same piece of hibernate mapping on another class ?

Please help !!

Thanks in advance.

Use an <idbag> mapping with a collection key. Bidirectional for this isn't really a good idea. Right now, I don't know how you would do that, but probably by creating the same mapping and setting inverse="true".

I bet it was the column with the name "COMMENT" It turns out that Comment is a reserved word in Oracle and cannot be used as a column name. I had exact same problem.