queryForPaginatedList doesn't play well with groupBy attribute of resultMap
(N+1 selects solution)
---------------------------------------------------------------------------------------------------
Key: IBATIS-274
URL: http://issues.apache.org/jira/browse/IBATIS-274
Project: iBatis for Java
Type: Bug
Components: SQL Maps
Versions: 2.1.7
Environment: WinXP Pro, WebLogic 8.1, JDK 1.4.2_05, Spring 1.2.7
Reporter: Tim Haley
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);
}
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
