mapped statement would not be the same objects as those returned by the second
mapped statement (meaning more memory use). We can do better than that!
Using a RowHandler , we can meet this requirement with a single SQL state-
ment. That way, we have to process the results only once to get both of the main
lists with the related lists, the Maps, and all of that without any duplicate objects.
Using this approach requires a bit more coding but uses less processing time, less
database I/O , and less memory.
To make this happen, we join the data, then as we look at each row, we add
new items to the lists (of products and accounts) and to the maps. That sounds
simple enough, so let's look at the code.
First, we create the mapped statement. It will join the tables, retrieve the rele-
vant data, and map it into three distinct objects in a composite that has all three
things that we care about:
<result property="account.accountId" column="accountId" />
<result property="product.productId" column="productId" />
<select id="AMPRowHandlerExample" resultMap="AmpRHExample">
p.productId as productId,
o.accountId as accountId,
m.manufacturerId as manufacturerId
from product p
join manufacturer m
on p.manufacturerId = m.manufacturerId
join orderitem oi
on oi.productId = p.productId
join orders o
on oi.orderId = o.orderId
order by 1,2,3
The AccountManufacturerProduct class is simply a class with three properties:
account , manufacturer , and product . The result map populates the properties, just
as we would do if we were going to create a regular flattened view of the data.
Next, the row handler takes these objects as it encounters them, and catego-
rizes them by productId , accountId , and manufacturerId into maps. The first time
it encounters a particular account or product, it also adds that object to the
account List or product List, respectively. If the selected objects have already been