[ http://issues.apache.org/jira/browse/IBATIS-274?page=all ]
Clinton Begin closed IBATIS-274:
--------------------------------
Resolution: Won't Fix
Assign To: Clinton Begin
Unfortunately this will not be possible to fix. This is a case where an ORM
has a significant advantage -- it can detect the request and generate two (or
more) SQL statements instead of one (which is required).
There's NO WAY iBATIS can ever predict which rows to read (or not read). To
solve this problem, we'd end up having to read all of the rows, which
eliminates the value of the range limiting.
This holds true of specifying min/max values as well.
In a nutshell, both pagination and join mapping are performance
enhancements....but they cannot be used together. So your option is to use
either one, but not both. I suggest using lazy loading w/ pagination, or join
mapping without pagination.
Your only other option is to code it yourself to tweak it exactly to your needs.
PS: I've deemed PaginatedList an anti-pattern, mostly because people are using
it too much to solve complex problems. It was meant to be for very simple
usages. iBATIS 3 likely will not support it in favor of encouraging using
explicit ranges (i.e. offset, and count).
I must close this issue, simply because it cannot be fixed -- by design it's a
tradeoff.
> 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
> Assignee: Clinton Begin
>
> 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
