Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 
Author Message
 Post subject: Recursive HQL?
PostPosted: Mon Aug 07, 2006 9:06 am 
Newbie

Joined: Thu Jun 29, 2006 11:49 am
Posts: 13
Hello there. I have an scenario where I have to load (efficiently, with one query) a bunch of objects that contain references to object of their same types, I mean, a tree of objects.

Example:

public Class ClassA {
public int Id;
public string Name;
public ClassA Parent;
public Set<ClassA> MyChildren;
public Set<ClassX> MyList2;
public Set<ClassY> MyList3;
}

I would like to write an HQL for this class to retrieve all the tree of ClassA objects. The first ones should be the ones that have a null Parent (the ones which belong to the root Parent), and the method should specify to retrieve all existent child nodes, but not load the MyList2 neither MyList3 properties (I mean, they should remain -lazy-).

How could I do this? Is there an example of this type of implementation in any tutorial?

Thanks in advance.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 07, 2006 9:13 am 
Senior
Senior

Joined: Wed Jun 15, 2005 4:17 am
Posts: 156
if the collections are marked as lazy they wont be loaded. The problem is that you can't do recursive queries with HQL. If you're using Oracle or SQL Server 2005 their SQL implementation support recursive queries but you have to use native SQL queries from your session object.

cheers,
Radu


Top
 Profile  
 
 Post subject: ugh
PostPosted: Mon Aug 07, 2006 9:38 am 
Newbie

Joined: Thu Jun 29, 2006 11:49 am
Posts: 13
Thanks for your reply radu!

However, it has been somewhat frustrating...

Isn't there a way to do this efficiently without using SQL? I think that before using NHibernate, in this project this transaction was done by a stored procedure that created a temporal table for the purpose.

Isn't there a cross-db way to make recursive queries? :( Is it planned in the roadmap the feature of recursion in HQL?

Regards.

Andrés [ knocte ]

--


Top
 Profile  
 
 Post subject:
PostPosted: Mon Aug 07, 2006 10:03 am 
Senior
Senior

Joined: Wed Jun 15, 2005 4:17 am
Posts: 156
The problem lies int the SQL itself. As far as I know, recursive SQL queries are non standard so vendors implement proprietary extensions. This means that currently is not possible to have a cross-db recursive query implementation in SQL. As HQL translates to SQL this means that you wont have recursive HQL queries either.

If anyone knows better I would appreciate a reply as I'm interested myself in an elegant solution to this problem!

My current solution (under SQL Server 2000) is to store the path and the level of the row in the tree so I'm able to retrieve whole branches (or the whole tree) with only one SQL query. When inserting or updating rows I have a trigger which updates the path info. The solution is not elegant but works. Anyway for big trees which are displayed in the UI, I'm not retrieving whole branches or the whole tree, I preffer to do on-demand population of the treeview which requires only the child collection of an item.

cheers,
Radu


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 21, 2008 6:10 am 
Newbie

Joined: Tue Feb 14, 2006 9:50 am
Posts: 9
Location: Paris - France
This could be implemented by Hibernate in a recursive java code that would perform many selects, couldn't it? Is it already done?

I have the same situation. I don't want to put "lazy=false" in the mapping because I don't want to load all children all the time, but in a specific point I need to write a query or Criteria that could load the entire tree.
Anyone knows how should I proceed?

If Hibernate could do it for me I think it would be more elegant than having to visit the entire tree just to activate the proxies - the tree will be used out of the session scope.

Thanks!


Top
 Profile  
 
 Post subject:
PostPosted: Wed May 21, 2008 6:29 am 
Expert
Expert

Joined: Thu Dec 14, 2006 5:57 am
Posts: 1185
Location: Zurich, Switzerland
If you are using SQL Server, you can do that with a SQLQuery like this:

Code:
string sql = @"With ProjectTree( uid ) as
                   (
                      select p.uid from sx.bus_project p where parentuid = :uid
                      union all
                      select p.uid from sx.bus_project p inner join ProjectTree t on p.parentuid = t.uid
                   )
                    select uid from ProjectTree";

                session.CreateSQLQuery( sql.Replace( ":uid", projectUId.ToString()) )
                    .AddScalar( "uid", NHibernateUtil.Int64 )
                    .List( projectUIds );


In this example, I only retrieve the ids, but entities should work as well. I'm not sure why I replaced the parameter instead of using .SetParameter(), maybe that didn't work here.

_________________
--Wolfgang


Top
 Profile  
 
 Post subject: Recursive HQL?
PostPosted: Wed May 21, 2008 9:18 am 
Senior
Senior

Joined: Thu Jun 21, 2007 8:03 am
Posts: 127
Location: UK
Hi knocte,

This is a common problem in a relational database. Various ways of implementing this have been documented:
http://www.dbazine.com/oracle/or-articles/tropashko4

'Materialized Path' is one I've used before. You can add an extra property to your class, and query using the 'like' operator to get sections of a tree.

Note, these patterns are neither database-specific, nor ORM specific.

Regards,
Richard


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 7 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.