[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12369247 ]
Tim Haley commented on IBATIS-274: ---------------------------------- I left out of the Environment section: Oracle 9i using the 10g driver. > 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
