You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Tim Haley (JIRA)" <ib...@incubator.apache.org> on 2006/03/07 16:29:39 UTC
[jira] Commented: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
[ 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