Using advanced query techniques - iBATIS in Action - page 132

detail level, we have three queries (one for the accounts, one for the orders, and
one for the order details), and the application would only create 2,025 objects
(1,000 accounts, 1,000 orders, and 25 order details). This is all done without any
changes to the application code and only minor changes to the XML configura-
tion of i BATIS .
In one totally nonscientific test that we performed, using a non-lazy loading
relationship to load the data took over three times as long to get the first list than
the lazy version. However, to get all of the data, the lazy version took about 20 per-
cent longer than the non-lazy loader. Obviously, this will depend greatly on the
amount of data being loaded, and as with most things, your mileage may vary.
Experience is the best guide.
There are times when you do not want to defer the loading of all the data but
want it all loaded when the data is first requested. In those cases, you can use the
technique described in the next section, which will do just that, and do it all in one
single query instead of several. This next approach avoids the N+1 Selects problem.

6.2.3 Avoiding the N+1 Selects problem

There are two ways to avoid the “N+1 Selects” problem that we will consider. One
is by using the groupBy attribute in i BATIS , and the other is by using a custom com-
ponent called a RowHandler .
Using the groupBy attribute is similar to the previous technique. In short, you
define the relationships using result maps, and then associate the top-level result
map with the mapped statement. The following example builds the same struc-
ture as the lazy loading example earlier, but with only one SQL statement run on
the database server.
Three result maps are involved: one for the accounts, one for the orders, and
one for the order items.
The result map for the accounts has three functions:

It maps the properties for the account objects.

■

It tells i BATIS what property indicates that a new account needs to be created.

■

It tells i BATIS how to map the next set of related objects, which in this case is
the related set of order objects for the account.

■

One very important thing to note here is that the groupBy attribute is referring to
a property name, not a column name.
The result map for the orders has the same three functions: