[Apache iBATIS Wiki] New: How do I get around the n+1 selects problem?

[ib...@incubator.apache.org]

   Date: 2005-01-05T20:28:52
   Editor: LarryMeadors
   Wiki: Apache iBATIS Wiki
   Page: How do I get around the n+1 selects problem?
   URL: http://wiki.apache.org/ibatis/How do I get around the n+1 selects problem?

   no comment

New Page:

Here is one solution.
{{{
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/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.

The important items here are the {{{groupBy}}} 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.