Hibernate version: 3.01

Hello,

The docs and previous posts seem to focus on Criteria queries to facilitate the fetch=join strategy. Though the reference manual suggests that simple object navigation should utilize the stategy if specified in the mapping files. I'm a bit unclear as to what that exactly means - since we cannot get a desireable result.

A 1:m B 1:m C (ugly entity diagram :-))

We've specified in the B mapping to C that fetch="join". This is sterling when we get B by id. B and all of its C's are fetched with a single SQL query.

If we ask A for the set of B's then we get the B's in a single SQL. But invariably "N" more queries are executed to get each C for each B - where N is the number of B's.

As an aside: we want to keep the fetching of the set of B's lazy when navigating from A to the B collection.

I realize we haven't provided actual mapping files yet. But as a first posted question - is what we're attempting achievable? To paraphrase the question:

Is it possible, given a reference to object A, to iterate over its collection of Bs and have that iteration trigger precisely a single SQL query where all the B's and their associated C's (via an outer join) are read from the database? That was a pedantic mouthful.

Regards,

Barry

Assuming that your hibernate.max_fetch_depth is set to 2 or higher, I believe that calling Hibernate.initialize on your A object will do an outer join over both B and C, resulting in the required single select (with a potentially massive result set).

Thanks for the reply.

I'm happy the reply didn't say it was impossible. But we tried the suggestion and it still executed N+1 queries. Also, I was hoping for a solution that didn't include explicit hibernate involvement. We were hoping that just navigating the object tree should trigger the single sql select.


So I guess it's time to show my (trimmed down) mapping files and beg for further assistance.


In the order of A, B, C: FileFolder has Documents which has LinkSetDocuments.

<hibernate-mapping>
<class name="FileFolder" table="T_FILE_FDR">
<id name="id" column="FILE_FDR_ID" type="CharStrType" />
<set name="documents" inverse="true">
<key column="FILE_FDR_ID" />
<one-to-many class="Document" />
</set>
</class>
</hibernate-mapping>

<hibernate-mapping>
<class name="Document" table="T_DOC">
<id name="id" column="DOC_ID" type="CharStrType" />
<many-to-one name="fileFolder" class="FileFolder" column="FILE_FDR_ID" not-null="true" />
<set name="linkSets" inverse="true" fetch="join">
<key column="DOC_ID" />
<one-to-many class="LinkSetDocument" />
</set>
</class>
</hibernate-mapping>


<hibernate-mapping>
<class name="LinkSetDocument" table="T_LSET_DOC">
<id name="id" column="LSET_DOC_ID" type="CharStrType">
<generator class="HibernateKeyGenerator" />
</id>
<many-to-one name="linkSet" class="LinkSet" column="LSET_ID" not-null="true" fetch="join"/>
<many-to-one name="document" class="Document" column="DOC_ID" not-null="true" fetch="join" />
</class>
</hibernate-mapping>

Is the FileFolder to Document association the only one you want to be lazily-loaded? What happens if you put lazy="false" on the other associations, as well as fetch="join"?

Alternatively, if you know at query time which associations you want eagerly loaded, just write several named queries and choose between them:Etc. etc.