[IBATIS-274] queryForPaginatedList doesn't play well with groupBy attribute of resultMap (N+1 selects solution)

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 2.1.7
• Component/s: Core
• Environment: WinXP Pro, WebLogic 8.1, JDK 1.4.2_05, Spring 1.2.7
• Assignee: Clinton Begin
• Reporter: Tim Haley
• Created: 07/Mar/06 23:26
• Updated: 31/May/06 19:02
• Resolved: 31/May/06 10:58

Description

I have implemented the N+1 solution using the groupBy attribute of the resultMap.

When this is called using queryForList() it works as expected; I get two BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 19 ProcessingResult objects.

When called with queryForPaginatedList() and a page size of 10, I only get the first BatchResults object, which does contain the list of 20 ProcessingResult objects.

When called with queryForPaginatedList() and a page size of 19, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 18 ProcessingResult objects. (total of 38, 2x the page size)

When called with queryForPaginatedList() and a page size of 18, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 16 ProcessingResult objects. (total of 36, 2x the page size)

When called with queryForPaginatedList() and a page size of 17, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 14 ProcessingResult objects. (total of 34, 2x the page size)

See details of mapping and method calls below:

<resultMap id="BatchResultMap" class="BatchResult" groupBy="processingSchedule.processingKey">
<result property="processingSchedule.processingKey" column="PROCESSING_KEY" javaType="int"/>
<result property="processingSchedule.processingDate" column="PROCESS_DATE" javaType="date"/>
<result property="processingSchedule.printDate" column="PRINT_DATE" javaType="date"/>
<result property="processingSchedule.processType.processTypeKey" column="PROCESS_TYPE_KEY" javaType="int"/>
<result property="processingSchedule.processType.description" column="PROCESS_TYPE_DESCRIPTION" javaType="string"/>
<result property="processingResults" resultMap="Processing.ProcessResultMap"/>
</resultMap>

<resultMap id="ProcessResultMap" class="ProcessingResult">
<!-<result property="processingKey" column="PR_PROCESSING_KEY"/>->
<result property="applicationKey" column="APPLICATION_KEY" javaType="int"/>
<result property="applicationStatusKey" column="APPLICATION_STATUS_KEY" javaType="int"/>
<result property="applicationStatusString" column="APPLICATION_STATUS_DESCRIPTION" javaType="string"/>
<result property="testResults" column="

{applicationKey=APPLICATION_KEY, processingKey=PR_PROCESSING_KEY}

"
select="getTestResultsForProcessingResult"/>
<result property="application" column="APPLICATION_KEY" javaType="int"
select="loadApplicationByPrimaryKey"/>
<result property="processing" column="PR_PROCESSING_KEY" javaType="int"
select="loadProcessingSchedule"/>
</resultMap>
<select id="loadBatchResultsForDateRange" parameterClass="DateRange" resultMap="BatchResultMap">
select
PRO.PROCESSING_KEY,
PRO.PROCESS_DATE,
PRO.PRINT_DATE,
PRO.PROCESS_TYPE_KEY,
PT.DESCRIPTION as PROCESS_TYPE_DESCRIPTION,
PR.APPLICATION_KEY,
ASCD.APPLICATION_STATUS_KEY,
ASCD.DESCRIPTION as APPLICATION_STATUS_DESCRIPTION,
PR.PROCESSING_KEY as PR_PROCESSING_KEY
from
WM.USTP_PROCESSING PRO
join WM.USTP_PROCESS_TYPE_CD PT on PRO.PROCESS_TYPE_KEY = PT.PROCESS_TYPE_KEY
left outer join WM.USTP_PROCESSING_RESULT PR on PR.PROCESSING_KEY = PRO.PROCESSING_KEY
left outer join WM.USTP_APPLICATION_STATUS_CD ASCD on ASCD.APPLICATION_STATUS_KEY = PR.APPLICATION_STATUS_KEY
where
PRO.PROCESS_DATE between #start# and (#end# + 1)
order by
PRO.PROCESSING_KEY desc

</select>

public List loadByProcessingDateRange(DateRange aDateRange)
throws DataAccessException

{ return getSqlMapClientTemplate().queryForList("loadBatchResultsForDateRange", aDateRange); }

public PaginatedList loadByProcessingDateRange(DateRange aDateRange,
int aPageSize)
throws DataAccessException

{ return getSqlMapClientTemplate().queryForPaginatedList("loadBatchResultsForDateRange", aDateRange, aPageSize); }