Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hello Everyone:

I have unidirectional many to many association From "Product" table to "Category" table. I have been trying to load eagerly ONLY CERTAIN CATEGORIES [Those categories whose parent category is null] of a product NOT ALL THE CATEGORIES, however, I have been unable to do it so far. Currently I am using it in two steps:

1- Load all the categories of a product
2- loop through all the categories and select those meeting my condition.

The draw back is, I can not return a "Product" object that contains the categories I need, instead I only return a collection of the categories. I was wondering if there is a way that I can write my query and eliminate the loop part.

Here is my code:


public Collection getComponentsByProductId(
long productId,
Session session) {
Collection result = new ArrayList();
try {
Product product =
(Product) session
.createCriteria(Product.class)
.add(Restrictions.eq("id", new Long(productId)))
.setFetchMode("categories", FetchMode.JOIN)
.uniqueResult();

if (product == null)
return result;
Iterator it = product.getCategories().iterator();
while (it.hasNext()) {
Category cat = (Category) it.next();
if (cat.getParentCategory() == null)
result.add(cat);
}
return result;
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}
} //end getVisibleProductByIdWithCategories

I would like to have my method return a "product" while those categories that I need loaded eagerly.

Below you will also find my mapping files.

regards,

Hibernate version: 3.0.5

Mapping documents:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="iadm.readme.model">

<class name="iadm.readme.model.Product" table="README.PRODUCT" lazy="true">

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

<property name="name" type="string" column="PRODUCT_NAME" length="30" not-null="true" unique="true"/>
<property name="shortName" type="string" column="PRODUCT_SHORT_NAME" length="6" not-null="true" unique="true"/>
<property name="visible" type="yes_no" column="PRODUCT_VISIBLE" not-null="true"/>

<!-- Parent can be null for root products. -->
<many-to-one name="parentProduct" cascade="none" column ="PARENT_PRODUCT_ID" not-null="false" fetch="join"/>

<set name="childProducts" cascade="all" inverse="true" fetch="join">
<key column="PARENT_PRODUCT_ID"/>
<one-to-many class="Product"/>
</set>

<!-- Unidirectional many-to-many relation from PRODUCT table to PLATFORM table -->
<set name="platforms" cascade="all-delete-orphan" table="README.PRODUCT_PLATFORM" batch-size="100">
<key column="PRODUCT_ID"/>
<many-to-many column="PLATFORM_ID" class="Platform"></many-to-many>
</set>

<!-- Unidirectional many-to-many relation from PRODUCT table to CATEGORY table -->
<set name="categories" cascade="all-delete-orphan" table="README.PRODUCT_CATEGORY" batch-size="100">
<key column="PRODUCT_ID"/>
<many-to-many column="CATEGORY_ID" class="Category"></many-to-many>
</set>
<sql-delete>delete from readme.product where product_id = ?</sql-delete>
</class>

</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="iadm.readme.model">

<class name="iadm.readme.model.Category" table="README.CATEGORY" >

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

<property name="name" type="string" column="CATEGORY_NAME" length="100" not-null="true"/>

<property name="order" type="long" column="CATEGORY_ORDER" not-null="true"/>

<property name="titleRequired" type="yes_no" column="CATEGORY_TITLE_REQUIRED" not-null="true"/>

<property name="adminOnly" type="yes_no" column="CATEGORY_ADMIN_ONLY" not-null="true"/>

<property name="created" column="CREATED_TIMESTAMP" type="timestamp" update="false" not-null="true"/>

<!-- Parent can be null for root categories. -->
<many-to-one name="parentCategory" cascade="none" column ="PARENT_CATEGORY_ID" not-null="false" fetch="join"/>

<set name="childCategories" cascade="all" inverse="true" fetch="join">
<key column="PARENT_CATEGORY_ID"/>
<one-to-many class="Category"/>
</set>

<set name="comments" cascade="all" inverse="true" batch-size="100">
<key>
<column name="CATEGORY_ID" not-null="true" />
</key>
<one-to-many class="iadm.readme.model.Comment" />
</set>
<sql-delete>delete from readme.category where category_id = ?</sql-delete>

</class>

</hibernate-mapping>




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

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt: