parent-children relationship, tree, outer-join

robcos · **Posted:** Thu Apr 01, 2004 2:03 pm

How do I configure a parent-children relationship so that whe I load the root of the tree the whole tree gets loaded using an outer-join?
What do I use for the queries? Query, find, or Criteria?
Can't seem to make it work properly
tx, Rob

robcos · **Posted:** Fri Apr 02, 2004 4:25 am

This is the mapping of my Category class:

Code:

<?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.nmt.iws.entity.generated.Category"
    table="category"
    dynamic-update="true"
>
    <id
        name="id"
        type="long"
    >
        <generator class="native" />
    </id>

    <property
        name="name"
        type="java.lang.String">

        <column name="name"
                not-null="true"
                length="40"
                index="parent_store_name_index"
                unique-key="parent_store_name_key" />
    </property>

    <property name="visible" column="isvisible" not-null="true" type="boolean"/>
    <many-to-one name="parent"
        class="com.nmt.iws.entity.generated.Category"  cascade="none"
        outer-join="true"
        >
        <column name="parentid"
            unique-key="parent_store_name_key"
            index="parent_store_name_idx"
            not-null="false"/>
    </many-to-one>

    <!-- bi-directional many-to-one association to Store -->
    <many-to-one
        name="store"
        class="com.nmt.iws.entity.generated.Store"
        outer-join = "false"
    >
        <meta attribute="finder">findByStore</meta>
        <column name="storeid"
            unique-key="parent_store_name_key"
            index="parent_store_name_idx"
            not-null="true"
        />
    </many-to-one>

    <set
        name="children"
        lazy="false"
        inverse="true"
        outer-join="true"
    >
        <key>
            <column name="parentid" />
        </key>
        <one-to-many
            class="com.nmt.iws.entity.generated.Category"
        />
    </set>

</class>
</hibernate-mapping>

In brief: it has a recursive parent child one-to-many relationship.
I need to perform queries like:

Code:

select cat from Category as cat where cat.parent.id = :my_id

I want the query to eager fetch the children, recursively, in order to fetch the whole tree in one big but fast select.

I tried with Query(session, "from Category as category left join fetch category.children where category.parent.id = :my_id") and no luck.

I tried with List cats = session.createCriteria(Category.class)
.createCriteria("parent")
.setFetchMode("children", FetchMode.EAGER)
.add( Expression.eq("id", new Long(parentId)))
.list();
return cats;
and no luck.

I always obtain something like:

Quote:

3929 Query select category0_.id as id2_, category0_.name as name2_, category0_.isvisible as isvisible2_, category0_.storeid as storeid2_, category0_.parentid as parentid2_, category1_.id as id0_, category1_.name as name0_, category1_.isvisible as isvisible0_, category1_.storeid as storeid0_, category1_.parentid as parentid0_, children2_.id as id__, children2_.parentid as parentid__, children2_.id as id1_, children2_.name as name1_, children2_.isvisible as isvisible1_, children2_.storeid as storeid1_, children2_.parentid as parentid1_ from category category0_ left outer join category category1_ on category0_.parentid=category1_.id left outer join category children2_ on category1_.id=children2_.parentid where category0_.id=4104
3929 Query select children0_.id as id__, children0_.parentid as parentid__, children0_.id as id0_, children0_.name as name0_, children0_.isvisible as isvisible0_, children0_.storeid as storeid0_, children0_.parentid as parentid0_ from category children0_ where children0_.parentid=6912
3929 Query select children0_.id as id__, children0_.parentid as parentid__, children0_.id as id0_, children0_.name as name0_, children0_.isvisible as isvisible0_, children0_.storeid as storeid0_, children0_.parentid as parentid0_ from category children0_ where children0_.parentid=6919
3929 Query select children0_.id as id__, children0_.parentid as parentid__, children0_.id as id0_, children0_.name as name0_, children0_.isvisible as isvisible0_, children0_.storeid as storeid0_, children0_.parentid as parentid0_ from category children0_ where children0_.parentid=6927
3929 Query select children0_.id as id__, children0_.parentid as parentid__, children0_.id as id0_, children0_.name as name0_, children0_.isvisible as isvisible0_, children0_.storeid as storeid0_, children0_.parentid as parentid0_ from category children0_ where children0_.parentid=6924
3929 Query select children0_.id as id__, children0_.parentid as parentid__, children0_.id as id0_, children0_.name as name0_, children0_.isvisible as isvisible0_, children0_.storeid as storeid0_, children0_.parentid as parentid0_ from category children0_ where children0_.parentid=6926

Some extracts from the hibernate log

Quote:

2004-04-02 10:15:56,063 INFO [net.sf.hibernate.cfg.Environment] - Hibernate 2.1 final
2004-04-02 10:15:56,064 INFO [net.sf.hibernate.cfg.Environment] - hibernate.properties not found
2004-04-02 10:15:56,066 INFO [net.sf.hibernate.cfg.Environment] - using CGLIB reflection optimizer
2004-04-02 10:15:57,378 INFO [net.sf.hibernate.dialect.Dialect] - Using dialect: net.sf.hibernate.dialect.MySQLDialect
2004-04-02 10:15:57,378 INFO [net.sf.hibernate.cfg.SettingsFactory] - Use outer join fetching: true
2004-04-02 10:19:07,154 DEBUG [net.sf.hibernate.impl.SessionImpl] - find: from Category as category left join fetch category.children where category.parent.id = :my_id
2004-04-02 10:19:07,154 DEBUG [net.sf.hibernate.engine.QueryParameters] - named parameters: {my_id=4103}
2004-04-02 10:19:07,154 DEBUG [net.sf.hibernate.hql.QueryTranslator] - compiling query
2004-04-02 10:19:07,163 DEBUG [net.sf.hibernate.cache.QueryCache] - checking cached query results in region: net.sf.hibernate.cache.QueryCache
2004-04-02 10:19:07,163 DEBUG [net.sf.ehcache.hibernate.Plugin] - key: net.sf.hibernate.cache.QueryKey@fed522aa
2004-04-02 10:19:07,164 DEBUG [net.sf.ehcache.store.MemoryStore] - net.sf.hibernate.cache.QueryCacheCache: MemoryStore miss for net.sf.hibernate.cache.QueryKey@fed522aa
2004-04-02 10:19:07,164 DEBUG [net.sf.ehcache.Cache] - net.sf.hibernate.cache.QueryCache cache - Miss
2004-04-02 10:19:07,164 DEBUG [net.sf.ehcache.hibernate.Plugin] - Element for net.sf.hibernate.cache.QueryKey@fed522aa is null
2004-04-02 10:19:07,164 DEBUG [net.sf.hibernate.cache.QueryCache] - query results were not found in cache
2004-04-02 10:19:07,164 DEBUG [net.sf.hibernate.hql.QueryTranslator] - HQL: from com.nmt.iws.entity.generated.Category as category left join fetch category.children where category.parent.id = :my_id
2004-04-02 10:19:07,164 DEBUG [net.sf.hibernate.hql.QueryTranslator] - SQL: select category0_.id as id0_, children1_.id as id1_, category0_.name as name0_, category0_.isvisible as isvisible0_, category0_.storeid as storeid0_, category0_.parentid as parentid0_, children1_.name as name1_, children1_.isvisible as isvisible1_, children1_.storeid as storeid1_, children1_.parentid as parentid1_, children1_.id as id__, children1_.parentid as parentid__ from category category0_ left outer join category children1_ on category0_.id=children1_.parentid where (category0_.parentid=? )
2004-04-02 10:19:07,164 DEBUG [net.sf.hibernate.impl.BatcherImpl] - about to open: 0 open PreparedStatements, 0 open ResultSets
2004-04-02 10:19:07,164 DEBUG [net.sf.hibernate.impl.BatcherImpl] - prepared statement get: select category0_.id as id0_, children1_.id as id1_, category0_.name as name0_, category0_.isvisible as isvisible0_, category0_.storeid as storeid0_, category0_.parentid as parentid0_, children1_.name as name1_, children1_.isvisible as isvisible1_, children1_.storeid as storeid1_, children1_.parentid as parentid1_, children1_.id as id__, children1_.parentid as parentid__ from category category0_ left outer join category children1_ on category0_.id=children1_.parentid where (category0_.parentid=? )

The query is extemely poor in performance.

Do you have any ideas?

emmanuel · **Posted:** Fri Apr 02, 2004 1:38 pm

This is not doable in regular SQL AFAIK.

joib · **Joined:** Wed Mar 03, 2004 9:38 am **Posts:** 70

If you use Oracle you can use the CONNECT BY feature. Hibernate does have some support for writing native SQL queries, although I have never used it so I can't comment on whether it can map results of CONNECT BY queries.

Unfortunately, something like CONNECT BY is AFAIK not in the SQL standard, so if you use another database or want portability you're forced to use one query for every level in the hierarchy.

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

When this recursive parent-child query set is run is it run

1) With each SELECT statement making a full round trip between the APP server and the Database, and HIBERNATE programmatically assembling the next query before sending it off for another round trip

OR

2) Hibernate somehow (magic?) instructing the database to construct a recursive query set, letting it run, then hibernate somehow (sorcery?) turning that one recursive result set into a hierarchy.

It seems like it can only be #1, but I seem to remember that Oracle's CONNECT BY syntax is merely a wrapper for recursive syntax that is part of a SQL standard (SQL92 maybe?).

So wishful thinking makes me ask: is Hibernate perhaps using that (mythical?) standard recursive syntax?

Preposterously Yours,
DW

PS: If the answer is that only #1 works in Hibernate, I am going to try to figure out a way to usefully map a hierarchical table into hibernate... such that you give it acts like other Hibernate Objects, but gives you back hierarchical data. Anyone have any ideas or want to help?

christian · **Posted:** Fri May 07, 2004 1:49 pm

It's #1 and we can't use #2, to be portable. You don't have to invent something, search Google for "nested set model".

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

Thanks for the pointer to the Nested Set Model. I am reading through this stuff now. Do you know where I might find a practical example of how to use this model in Hibernate to end up with Node objects which contain java.util.Sets Node objects, etc? A mapping file and code for add, update, delete, would be great, if such a thing exists.

Thanks again

christian · **Posted:** Fri May 07, 2004 3:37 pm

I don't think there is any implementation of nested set for Hibernate (but it would be great and Gavin and me already started doing it half a year ago, but we don't have time to finish it...). You can try yourself: Use an Interceptor to calculate the UPDATE of the nested node numbers.

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

I am implementing the Nested Set Model in Hibernate, so I am first doing it as a simple Java program to make sure it works, then moving it into an Interceptor.

I have the selects done, which create hierarchical data sets using recursion, and now I am trying to do the updates.

What I would like to do is something like this:

Code:

String rightHql = "update NSFolder " +
                "set right = right+2 " +
                "where right >= :rightMostSibling " +
                "and right > :rightMostSibling ";
Query rightQ = s.createQuery(leftHql);
rightQ.setLong("rightMostSibling", rightMostSibling.longValue());
rightQ.uniqueResult();

but of course, that does not work, because the Query interface can only be used for SELECT statements.

To do the UPDATE statement, am I going to have to get the JDBC connection and run this statement as SQL? Should I use the net.sf.hibernate.sql.Update object to help construct this statement?

This seems like the long way around to do this, since it is fairly simple to write a SQL statement to do it, so I am wondering if there is a better way.

The other way I could do this, I guess, is to do a Criteria.list(), update all the right columns programmatically, and then update() all the things in the list, but this seems horribly inefficient.

In the end, I guess what I am asking is "Is there a way to update a bunch of rows at the same time in Hibernate/HQL?" I think the answer is "No" but I want to make sure before I go down this road.

Thanks again,
DW

christian · **Posted:** Tue May 11, 2004 7:08 pm

Updates _are_ inefficient using the Nested Set model. Sorry, I don't have enough time right now to support you with that, but I'm interested in any results you might have.

dannwebster · **Joined:** Wed Mar 24, 2004 7:40 pm **Posts:** 18

I recognize that Nested Set Model is optimized for reads, and that is not really a problem in my case. The thing I was concerned with was not so much "Is there a faster way than updating all the rows?" because I realize that all rows need updated.

What I was really looking for is "Does Hibernate have a way to allow me to update multiple rows at the same time without grabbing the JDBC connection?"

It looks like hibernate only allows updates to a single row at a time, using session.update(), and any multi-row updates would need to be done through the JDBC connection. Is this the correct assumption?

If not, can you point me to the docs or code that would give me an idea how to do this properly in Hibernate?

Thanks,
dw

christian · **Posted:** Tue May 11, 2004 7:15 pm

You will not only have to update all rows that are part of the updated subgraph (the "nested" set) in the database, but also all currently loaded "rows" (objects) of that set in memory :)

rnv · **Joined:** Mon May 03, 2004 1:08 am **Posts:** 18

Do I have to write for loop and recursive calls? or is there a technique to
make hibernate load all children nodes(full graph for all descendents)

any example will be greatly appreciated
thanks

christian · **Posted:** Fri May 14, 2004 10:26 am

You can't use "CONNECT BY", unless you fall back to direct SQL calls (this would be Oracle syntax). There is no tree magic in Hibernate.