I have a question regarding hierarchical query..Blogged it with a class diagram at

http://www.jroller.com/page/mert?entry=a_hibernate_question_hierarchical_query

any comments & suggestions are welcome.

Perhaps you could summarize and repost it here? Your server is reporting a 500 out of heap space error.

I managed to post it here.

Assume that you've modelled your population centers with one class, country --> state --> city --> county modelled with the PopulationCenter. You have also a CarAccident class that has association with PopulationCenter and AccidentSeverity.



Whats the best practice to fetch the result for a query like "gimme the sum of the car accidents occured in states grouped by accident severity" (a cross-table result - states/severity).

That's a report-style query, in which no mapped object is required. Your objects are mapped normally, but you only get a result set of scalars back from your query.

You can use HQL or criteria projections for this. The HQL is almost exactly the same as the equivalent SQL.

The criteria uses Projections:Is this what you wanted to know?

Thanks for the reply but that's not a hierarchical query. You get the results for each PopulationCenter but i need something like, the sum of car accidents in states. It means the sum for the cities and counties will be added to the sum of their parent state.

I fetched the results with a query like,

select ID from T_POPULATION_CENTER
start with UPPER_POPULATION_CENTER = :myPopulationCenter
connect by prior ID = UPPER_POPULATION_CENTER

But a statement like this cannot overcome the load of the queries that will be executed. So I think a key column like hierarchicalKey for each PopulationCenter which will represent the hierarchical parents of itself can be a good solution. Executing a query with 'like' for the hierarchicalKeys can lead to a result.

That could become nasty, with lots of subqueries, if any level of nesting was allowed. I see why you're canvassing opinions. Coincidentally, we've just added a very similar relational structure in the largest project that I work on, so I will be thinking more about this over the next week or two.

However I don't think that hibernate has any built-in support for anything like this: it's quite likely that the best I'll be able to come up with will be a couple of queries to determine hierarchy depth, then a grouping query to calculate the results. Our DBA has suggested that cubes might be a better way to implement this sort of thing, but I know almost nothing about cubes, other than that they're not supported by hibernate. Still, I'm not above using plain old SQL if it improves the performance of complex queries like this.

I'll let you know what I turn up, if it's useful.

Thanks for the reply tenwit.

I looked over the dimensions and I think they are good for speeding up the queries but not nothing more..

What do you say about the hierarchical key idea? A column for PopulationCenter that holds the ids of its parents like "@34@23@12@4@".
The value could be generated by triggers while inserting PopulationCenters.

The results for a state can be fetched with a 'like' query on hierarchical key. Because the parents for all the cities and counties will be stated within this key.

Writing queries with plain old SQL is a solution but will be specific to the CarAccident class for this case. Hence we have a class diagram with a number of 1000+ class and 900 tables in dbase, a generic solution is desperately in need.

Regards...

What you suggest will, of course, work. My preference has always been to keep the data model exactly that: a model for data. I prefer not to put business logic hints into the DB, and as such, I'd prefer to take the hit in search speed, so I won't use that solution. I might consider building an equivalent in-memory key, probably as a red-black tree (because TreeMap comes in java.util, but there's no equivalent B-tree in java.util). This would mean that the first couple of searches would take as long as they would without any "hierarchical key", but performance would rapidly improve, and without the database having to know anything about how I do my searches.

The idea would be to build up a singleton tree of population centres, then when you want to query by centre + nested centres, grab all ids from the relevant node and all child nodes. Your resulting SQL will have lots of "or (PopulationCenterID = :anotherID)" bits, but seeing as that's a key'ed column, the query should still be as fast as possible.