How do I get around the N Plus 1 selects problem

Here is one solution.

nplusone.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "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.

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.

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.

Page Operations

Browse Space