Hibernate version: 2.1

Newbie here ... really love Hibernate and it's been brilliant for stand-alone tables (MySQL 5.0).

My problem lies in retrieving joined data from 2 or 3 (legacy) tables which define a Shopping Item within a Category and define Categories within a ParentCategory and uses CategoryXref to relate a Category to one (or more) Parent Category:

1. ParentCategory
field1 PARENTCATEGORY_ID integer
field2 name string
field3 countproducts integer

2. CategoryXref
field1 pcategory_id integer
field2 categorycode string

3. Category
field1 CATEGORY_ID integer
field2 categorycode string
field3 categoryname string
field4 countproducts integer

Currently I have an automated java process to populate the Category table from a xml feed.
I would like to retrieve a list of all categories for a given parent category id.

Mappings
<?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>

<class name="com.dathorne.houseparts.models.ParentCategory" table="PARENTCATEGORY">
<meta attribute="class-description">
Represents a single parent category.
</meta>

<id name="id" type="int" column="PARENTCATEGORY_ID">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>

<property name="name" type="string" not-null="true">
<meta attribute="field-description">Category name</meta>
</property>

<property name="countproducts" type="int" not-null="true">
<meta attribute="field-description">Count of Products</meta>
</property>

</class>
</hibernate-mapping>

<?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>

<class name="com.dathorne.houseparts.models.CategoryXref" table="CATEGORYXREF">
<meta attribute="class-description">
Represents a single category.
@author Mark Dathorne (with help from Hibernate)
</meta>

<id name="id" type="int" column="PARENTCATEGORY_ID">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>

<property name="categoryid" type="string" not-null="true">
<meta attribute="field-description">Category id</meta>
</property>

<!-- Use a standard parent/child relationship for categories. -->
<!-- <bag name="categories"
cascade="all"
inverse="true"
order-by="categoryname desc"
lazy="true"
access="org.hibernate.auction.persistence.DirectSetAccessor">
<key>
<column name="categorycode" not-null="true"/>
</key>
<one-to-many class="com.dathorne.houseparts.models.Category"/>
</bag>

<set name="categories"
lazy="true"
table="categories"
order-by="categoryname asc">
<key column="categorycode"/>
<composite-element class="com.dathorne.houseparts.models.Category"/>
</set>
-->
</class>
</hibernate-mapping>

<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>

<class name="com.dathorne.houseparts.models.Category" table="CATEGORY">
<meta attribute="class-description">
Represents a single category.
@author Mark Dathorne (with help from Hibernate)
</meta>

<id name="id" type="int" column="CATEGORY_ID">
<meta attribute="scope-set">protected</meta>
<generator class="native"/>
</id>

<property name="categorycode" type="string" not-null="true">
<meta attribute="field-description">Category code</meta>
</property>

<property name="categoryname" type="string" not-null="true">
<meta attribute="field-description">Category name</meta>
</property>

<property name="countproducts" type="int" not-null="true">
<meta attribute="field-description">Count of Products</meta>
</property>

<property name="status" type="string" not-null="true"/>

<!-- The other side of this bidirectional one-to-many association to item.
<many-to-one
name="categoryxref"
class="com.dathorne.houseparts.models.CategoryXref"
column="categoryid"
update="false"
insert="false"
cascade="none"
not-null="true"
outer-join="false"
access="org.hibernate.auction.persistence.DirectSetAccessor"
foreign-key="categoryid"/> -->

</class>
</hibernate-mapping>


I would like to see a java method similar to what follows here to retrieve a List of Categories for a given Parent Category id:

public List getCategoryList(int pcategoryid)
throws HibernateException
{
List categories = null;

System.out.println("List to be obtained for " + pcategoryid);
List categoryxrefs = session.createQuery("from CategoryXref catx "
+ "where catx.id = :pcategoryid ")
.setInteger("pcategoryid", pcategoryid)
.list();
System.out.println("List obtained " + categoryxrefs.size());

// Process each category and load category xref
ListIterator iter = categoryxrefs.listIterator();
if (iter.hasNext()) {
categoryxref = (CategoryXref)iter.next();

categories = categoryxref.getCategories();
ListIterator iter2 = categories.listIterator();
while (iter2.hasNext()) {
category = (Category)iter.next();
System.out.println(category.getCategorycode() + ": "
+ category.getCategoryname());
}
}

return categories;
}

Any (constructive) suggestions appreciated.

Regards,

Mark D