I have a legacy (badly modelled) table structure where tables are aranged as in mapping below. My problem is as follows.

Assuming that you have identified a row in SERVICE_BENEFIT_HIERARCHY and I want to return the children of this row from the same table, then to do this, I select from SERVICE_BENEFIT_HIERARCHY joined with SERVICE_BENEFIT_CATEGORY where SERVICE_BENEFIT_CATEGORY.TYP_IND = "C" and relationship of child is like the first 2 characters of relationship of parent.

I know it's the worst possible design but I didn't design it......

So my question is... Is there a way to map this short of writing HQL in a dao accessor?

Also is there a way to put a HQL mapping in hibernate i.e something like.

You could most likely do this in Hibernate3, where you can map nearly every kind of crazy database schema by defining your own custom SQL for CRUD operations and associations. Can't think of an easy way for H2 though.

is it possible to start using a version hibernate 3 that would implement this functionality?
If so which version? If not why not?

Is my only recourse otherwise to access my service layer through a getter on my domain object. My particular issue is I have a domain object whose children are also the same typed domain object but there is a complicated HQL join to retreive the results so I just can't create a mapping like the one I mentioned. I have a need to iterate through a list of these domain objects with the domain object having access to it's children so I can iterate through them.