iBatis how to solve a more complex N+1 problem

Question

I have a database that is similar to the following:

create table Store(storeId)
create table Staff(storeId_fk, staff_id, staffName)
create table Item(storeId_fk, itme_id, itemName)

The Store table is large.

And I have create the following java bean

public class Store
{
    List<Staff> myStaff
    List<Item> myItem

   ....
}

public class Staff
{
    ...
}

public class Item
{
   ...
}

My question is how can I use iBatis's result map to EFFICIENTLY map from the tables to the java object?

I tried:

<resultMap id="storemap" class="my.example.Store">
  <result property="myStaff" resultMap="staffMap"/>
  <result property="myItem" result="itemMap"/>
</resultMap>

(other maps omitted)

But it's way too slow since the Store table is VERY VERY large.

I tried to follow the example in Clinton's developer guide for the N+1 solution, but I cannot warp my mind around how to use the "groupBy" for an object with 2 list...

Any help is appreciated!

Answer 1

When you start wanting to load a graph of objects, you begin to understand why iBatis is not an ORM, just an object mapper. You are right that the recipe for loading a relation in a mapping (avoiding the N+1 problem) with iBatis's groupBy feature is difficult to generalize for your scenario. I think it could be done, but I wouldn't bother.

Usually one implements either some lazy loading here, or load explicitly the related objects. Anyway, I'm not sure what is your goal. You say "the Store table is VERY VERY large". Now, do you intend to load many Store objects? Do you really need the related objects? Ask yourself (when designing with iBatis) what would be the ideal SQL to be executed.

And don't forget that there can be different scenarios for dealing with your objects, and not necesarily all of them must use the same mappings.

For example, frequently one has two types of use cases related to the Store objects: in the first type, one needs to load the full "object graph" but only for one (or a few) root object (one Store); in the other type one must load many "Stores" (with related data) but just for some listing or report. Then, one can do different things you each kind of scenario: in the first, one loads the full object with the related objects (perhaps with lazy loading, without much worrying about N+1 problem); in the second, one does not really load full Store object graph but just some dummy StoreWithExtraData DTO object (perhaps even a plain HashMap!) which correspond to a row of the listing - and one can accomplish this, in iBatis, with an ad-hoc SQL query and mapping.