Here is one solution.
這是一個解決方案
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "- "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Calendar">
<resultMap id="quarterMap" class="calendarQuarter" groupBy="quarter">
<result property="quarter" column="quarter"/>
<result property="name" column="name"/>
<result property="description" column="description"/>
<result property="months" resultMap="Calendar.monthMap"/>
</resultMap>
<resultMap id="monthMap" class="calendarMonth">
<result property="name" column="monthName"/>
<result property="description" column="monthDescription"/>
<result property="broadcastStartDate" column="broadcastStartDate"/>
<result property="broadcastEndDate" column="broadcastEndDate"/>
</resultMap>
<select id="getQuartersForServiceYear" resultMap="quarterMap">
select distinct
QuarterNumber as quarter,
QuarterName as name,
QuarterDesc as description,
SeasonYear as year,
MonthName as monthName,
MonthDesc as monthDescription,
min(broadcastDate) as broadcastStartDate,
max(broadcastDate) as broadcastEndDate
from BroadcastDate
where SeasonYear = #year#
and MonthName is not null
group by
QuarterDesc,
QuarterNumber,
QuarterName,
SeasonYear,
MonthName,
MonthDesc
order by broadcastStartDate
</select>
</sqlMap>
When you call
接著你可以調用
List myList = executeQueryForList("Calendar.getQuartersForServiceYear", 2005);
the main query is executed, and the results are stored in the
myList variable as beans of type "calendarQuarter" (an alias). Each object in that List will have a "months" property that is also a List populated from the same query, but using the "monthMap" result map to populate the beans in the child lists. So, you end up with a list containing sub-lists, and only one database query is executed.
主查詢被執行，并且在myList里存儲calendarQuarter為別名的對象。在List里的每個“months”屬性里還有一個初始化的子列表，這個子列表的數據也來自這次查詢。但是用monthMap結果map來渲染子列表。所以，你得到了一個含有子列表的列表，并且只有一次數據庫查詢被執行。
The important items here are the
重要的項在groupby的屬性和months屬性。
attribute and the
<result property="months" resultMap="Calendar.monthMap"/>
property mapping in the "quarterMap" result map. One other important detail is that the result mapping for the months property is namespace aware - had it been simply "monthMap" it would not work.
另一個需要注意的是month屬性的結果映射名是命名空間敏感的－如果配置成“monthMap”,他將不能工作。
Summary: You have a single query that will return results such as
總結：你有一個簡單的查詢，他返回下面這樣的結果
parent1, child1
parent1, child2
parent2, child1
parent3, child1
parent3, child2
parent3, child3
....
The groupby will take care of figuring out that you really want a list of parent objects with their matching child objects as a list under them.
這個groupby將處理你想得到的父對象組成的列表和相應的在父對象之下的子對象組成的列表。