Hi all

I am trying to have this recursive relationship for one of my project. the query that resulting the program is throwing more than one parent.

here is the HSQL

from category c
inner join fetch c.categories as ch
where c.parentId = 0 //to identify it is root
order by c.parentId, c.categoryId, c.categoryName

is there anyway to fetch all the children withoug having multiple parent?

many thanks in advance

here is all the configuration detail

Hibernate version:3.0

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
>
<class
name="com.Category"
table="category"
>

<id
name="categoryId"
column="category_id"
type="java.lang.Long"
>
<generator class="increment">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Category.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<property
name="categoryDescription"
type="java.lang.String"
update="true"
insert="true"
column="category_description"
/>

<property
name="categoryImage"
type="java.lang.String"
update="true"
insert="true"
column="category_image"
/>

<property
name="categoryName"
type="java.lang.String"
update="true"
insert="true"
column="category_name"
/>

<property
name="categoryStatus"
type="int"
update="true"
insert="true"
column="category_status"
/>

<property
name="parentId"
type="int"
update="true"
insert="true"
column="parent_id"
/>

<set
name="childs"
table="category"
lazy="true"
cascade="all"
sort="unsorted"
>

<key
column="parent_id"
>
</key>

<one-to-many
class="com.entity.Category"
/>

</set>

<property
name="version"
type="java.lang.Long"
update="true"
insert="true"
column="version"
/>

<property
name="dateAdded"
type="java.sql.Date"
update="true"
insert="true"
column="date_added"
/>

<property
name="dateModified"
type="java.sql.Date"
update="true"
insert="true"
column="date_modified"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Category.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
N/A using spring

Name and version of the database you are using:
Mysql 5

The generated SQL (show_sql=true):
select category0_.category_id as category1_0_0_, childs1_.category_id as category1_0_1_, category0_.category_description as category2_0_0_, category0_.category_image as category3_0_0_, category0_.category_name as category4_0_0_, category0_.category_status as category5_0_0_, category0_.parent_id as parent6_0_0_, category0_.version as version0_0_, category0_.date_added as date8_0_0_, category0_.date_modified as date9_0_0_, childs1_.category_description as category2_0_1_, childs1_.category_image as category3_0_1_, childs1_.category_name as category4_0_1_, childs1_.category_status as category5_0_1_, childs1_.parent_id as parent6_0_1_, childs1_.version as version0_1_, childs1_.date_added as date8_0_1_, childs1_.date_modified as date9_0_1_, childs1_.parent_id as parent6_0__, childs1_.category_id as category1_0__ from category category0_ right outer join category childs1_ on category0_.category_id=childs1_.parent_id where category0_.parent_id=0 order by childs1_.parent_id, category0_.category_id, category0_.category_name

I'm guessing as to what your question means, but I think that the HQL (not HSQL, that's something different) you need isThe "select c" at the beginning restricts the resulting list to containing only the Categories requested, and not the paired category/contained categories.

If what you're asking is "can I get a version of c where c.getCategories() returns only some of c's categories, as limited by my HQL", then the answer is: no. Each category object must be complete, otherwise it would break its own mapping rules, and it would be only a partial object. That is clearly illegal.

Hi there

yep it's HQL sorry not HSQL the actual problem if you fetch the childs, it seems that hibernate is collecting the information for the child as well in one go.

ie i got
CPU -> INTEL CPU
CPU -> AMD CPU

if you fetch them so both of them is appear as a root while actually i need only one root right

i have work around that group by in fact that grouping them make the child only one instead two

please help

Sorry, still can't parse that. Perhaps you could illustrate this without explanation. Can you provide

• a sample DB table (with just enough rows to show the problem),
• the mapping for the table,
• the query that is producing the wrong results,
• the results that the query is producing, and
• the results that you would like to see.

Hi tenwit

thank you so much for replying again and again. now currently i have this table category reference itself
now i am using this one to many mapping but recursively.
using the hql
getHibernateTemplate().find("select c from category as c inner join fetch c.childs as ch where c.parentId = 0"); //identify the root

not this row returning

example
parent data -> child data
CPU -> Intel CPU
CPU -> AMD CPU

now i want to have this data
CPU ->parent single only
but when i get the childs with c.getChilds()
it's returning
Intel CPU
AMD CPU

currently based on the sql that is generated hibernate getting the child as well in one go, that's why we are having this CPU category two times.

now i trick hibernate a bit by using "Group by" and it's returning single parent and single child only either Intel CPU or AMD CPU

is there anyway that i could get only CPU but when i want to get the childs it;s returning all the childs

cheers

I think I understand. When you execute that query, you want an object in which obj.getChilds() returns nothing? You cannot do that: if that happened, obj wouldn't be obeying its mapping. It's not a problem in hibernate, the problem is that you want a result set row, but hibernate deals in objects, not result sets.

The best that you can do is to not eagerly fetch the children, by changing your query to omit the join fetch: it's unnecessary in your query, and if you leave it out, the children won't be eagerly fetched.

However, there is no way that you can have getChilds() return null, or an empty set, if the parent object has children. If it is important that this happens for you, then you should use JDBC, not an ORM.