Suppose class A has a sole member, a Collection of class B, that must be lazy loaded.

I query for a list of instances of A. Two instances are returned. Assume I cannot left join fetch the children.

Is it possible to initialize all children of both instances of A in a single SQL statement?

My impression is that batch-size only works on children of a single parent. Is this correct?

Why dont you just use join fetch?

Hi

Just to clarify - I think that you (Justin) are confusing the initialisation of the parent and the children.

The 'join fetch' phrase allows you to eagerly initialise a 'child' collection in a join. It doesn't matter how many parent instances there are in the resulting cartesian product - all the elements of the collection that you specify to join fetch will be eagerly initialised. These elements may be associated with different parents, but their collection role is the same (see below)

Now the restriction (from the docs)



Note the use of the words 'collection role' - What the guys are saying here is that the eagerly fetched collections may manifest itself in more than a single collection (e.g. Parent instance A has a collection containing elements of class X, and Parent instance B has a collection containing elements of class X) but must have the same 'role' (in this case X). So you can't do:

..assuming cat 1-----* kittens and cat 1------* friends

Note however that you can do (from the docs)

.. as the mate association is not a collection.

Lazy initialisation of Parents happens via proxying - which is another kettle of fish...

Yep, I know how left join fetch works, and I cannot use it because it is already being used for another collection. I simplified the problem and specified these restrictions because of this.
So, the question still stands. Is there any way to emulate left join fetch behavior with a single SQL select after the parent objects have already been retrieved?
Thanks!

yes, you can try using batch fetching.

Thanks,
Any way to force batch fetches to not use the SQRT function and just fetch up to batch-size? For example, given 12 children to fetch, they come in at 5, 5, 1, 1 with batch-size 25. This is four SQL statements rather than one. Is there any way to force it to one?
I know you've mentioned in a previous post that there is a reason for the SQRT functionality. Can you elaborate?
Thanks again.

The easy way I have found is to only retrieve child instance ids in the first query, and then use the IN operator in the second query along with the list of child ids, e.g.:

'IN (?, ?, ?, ?, ?, ?)'

to pull in the child-objects. Hand-assembly is required of the resulting associations/collections (I store child objects in a HashMap by id) but it allows for a single SQL select which was the desired effect.

The reason for the sqrt stuff is better optimized sql generation and better usage of the prepared statement cache. You are most likely better of sticking to it than rolling your own.

In my case, it is definitely not advantageous to use batch-size versus IN (...). I simply cannot have N SQL statements running in my query... as the database is 1000 miles away, and the round-trip time for a database hit is not a minor concern. It is much more optimal in my situation to have 1 SQL statement, regardless of any statement caching.
Can you elaborate on how the SQL generation is better optimized in the batch-size scenario?
Thx

Just switch on batch fetching and look at the generated SQL.