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:27:38 UTC
[jira] Created: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
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
[jira] Closed: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ 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
[jira] Commented: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
Posted by "Tim Haley (JIRA)" <ib...@incubator.apache.org>.
[ 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
[jira] Commented: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
Posted by "Sven Boden (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12369315 ]
Sven Boden commented on IBATIS-274:
-----------------------------------
I think that at the moment using queryForPaginatedList together with groupBy is an undefined behaviour. It runs but it's undefined. Using a normal groupBy causes all rows to be read and puts all objects in the right "object bucket" (according to the groupBy), using paginatedList doesn't allow this because not all rows are inspected.
> 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
[jira] Commented: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
Posted by "Dan Syrstad (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12413139 ]
Dan Syrstad commented on IBATIS-274:
------------------------------------
I'm having the same problem with N+1 selects (groupBy). I believe that the desired behavior would be that N (N=pageSize) top-level objects are returned and each object contains the proper number of nested objects regardless of N. I can't see how any other behavior would be acceptable. The object graph is currently inconsistent depending on the page size, which is a UI setting. How the user wishes to view the objects should not affect the proper construction of the objects. I think Sven's comment about the behavoir being "undefined" may be true simply because there's no javadoc on PaginatedDataList (!). Also, Sven describes the technical problem, which should be fixed. This feature is exactly what I need, but since it does not work properly with "groupBy", I'll have to revert to queryForList().
Here's my mapping:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
<sqlMap namespace="Load">
<resultMap id="LoadMap" class="com.kingcompanies.usps.fast.dao.Load" groupBy="loadNum">
<result property="loadNum" column="loadNum" />
<result property="carrierName" column="carrierName" />
<result property="carrierCode" column="carrierCode" />
<result property="departureDate" column="departureDate" />
<result property="trailerNum" column="trailerNum" />
<result property="grossWeight" column="grossWeight" />
<result property="stops" resultMap="Load.StopMap" />
</resultMap>
<resultMap id="StopMap" class="com.kingcompanies.usps.fast.dao.Stop" groupBy="stopId" >
<result property="stopId" column="stopId" />
<result property="stopNum" column="stopNum" />
<result property="stopDate" column="stopDate" />
<result property="stopTime" column="stopTime" />
<result property="consigneeName" column="consigneeName" />
<result property="consigneeEntryType" column="consigneeEntryType" />
<result property="shipments" resultMap="Load.ShipmentMap" />
<result property="appointment.apptNum" column="apptNum" />
<result property="appointment.apptDateTime" column="apptDateTime" />
<result property="appointment.apptStatus" column="apptStatus" />
<result property="appointment.truckArrivedDateTime" column="truckArrivedDateTime" />
<result property="appointment.unloadingStartedDateTime" column="unloadingStartedDateTime" />
<result property="appointment.unloadingCompletedDateTime" column="unloadingCompletedDateTime" />
</resultMap>
<resultMap id="ShipmentMap" class="com.kingcompanies.usps.fast.dao.Shipment">
<result property="proNum" column="proNum" />
<result property="description" column="description" />
<result property="loadClass" column="loadClass" />
<result property="mailType" column="mailType" />
<result property="jobNum" column="jobNum" />
<result property="jobName" column="jobName" />
<result property="quantity" column="quantity" />
<result property="uom" column="uom" />
<result property="shipmentWeight" column="shipmentWeight" />
<result property="pieces" column="pieces" />
<result property="deliveryDate" column="deliveryDate" />
</resultMap>
<select id="getLoads"
parameterClass="com.kingcompanies.usps.fast.dao.LoadQueryParams"
resultMap="LoadMap">
SELECT
Loads.LOAD_NUM loadNum,
Loads.Carrier_Name carrierName,
Loads.CARR_CODE carrierCode,
Loads.Departure_Date departureDate,
tblAvailablePros.fldTrailerNum trailerNum,
Loads.Gross_Weight grossWeight,
tblAvailStopCons.fldLoadStopId stopId,
tblAvailStopCons.fldStopNum stopNum,
tblAvailStopCons.fldApptDate stopDate,
tblAvailStopCons.fldApptTime stopTime,
Consignees.CONSIGNEE consigneeName,
Consignees.fldEntryType consigneeEntryType,
tblAvailablePros.fldProNum proNum,
tblAvailablePros.fldComments description,
tblAvailablePros.fldClass loadClass,
tblAvailablePros.fldMailType mailType,
tblAvailablePros.fldJobNum jobNum,
tblAvailablePros.fldJobName jobName,
tblAvailablePros.fldQuan quantity,
tblAvailablePros.fldUOM uom,
tblAvailablePros.fldWeight shipmentWeight,
tblAvailablePros.fldPieces pieces,
tblAvailablePros.fldDelDate deliveryDate,
tblUSPSAppointments.fldApptNum apptNum,
tblUSPSAppointments.fldApptDateTime apptDateTime,
tblUSPSAppointments.fldStatus apptStatus,
tblUSPSAppointments.fldTruckArrived truckArrivedDateTime,
tblUSPSAppointments.fldUnloadingStarted unloadingStartedDateTime,
tblUSPSAppointments.fldUnloadingCompleted unloadingCompletedDateTime
FROM
Loads
INNER JOIN tblAvailStopCons
ON Loads.LOAD_NUM = tblAvailStopCons.fldLoadNum
LEFT OUTER JOIN tblUSPSAppointments
ON Loads.LOAD_NUM = tblUSPSAppointments.fldLoadNum
AND tblAvailStopCons.fldConsId = tblUSPSAppointments.fldConsId
INNER JOIN tblAvailablePros
ON Loads.LOAD_NUM = tblAvailablePros.fldLoadNum
AND tblAvailStopCons.fldStopNum = tblAvailablePros.fldStopNum
INNER JOIN Consignees
ON tblAvailStopCons.fldConsId = Consignees.Cons_ID
WHERE
(Consignees.fldEntryType = 'SCF'
OR Consignees.fldEntryType = 'BMC'
OR Consignees.fldEntryType = 'ASF')
AND Loads.Gross_Weight BETWEEN #startWeight# AND #endWeight#
AND Loads.Departure_Date BETWEEN #startDate# AND #endDate#
AND Loads.CARR_CODE IS NOT NULL
AND tblAvailStopCons.fldApptDate IS NOT NULL
AND tblAvailStopCons.fldApptTime IS NOT NULL
AND (
(#wantScheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NOT NULL)
OR (#wantUnscheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NULL)
OR (#wantClosedOut#=1 AND tblUSPSAppointments.fldUnloadingCompleted IS NOT NULL)
)
ORDER BY
Loads.Departure_Date, Loads.LOAD_NUM, tblAvailStopCons.fldStopNum
</select>
</sqlMap>
> 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
[jira] Commented: (IBATIS-274) queryForPaginatedList doesn't play
well with groupBy attribute of resultMap (N+1 selects solution)
Posted by "Tim Haley (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-274?page=comments#action_12414036 ]
Tim Haley commented on IBATIS-274:
----------------------------------
Clinton,
Yes, I had a feeling that the result would be something like this. I was hoping that you would be able to do some magic, but I can see your point.
My only remaining comment is that you need to document this limitation in both the javadocs for the PaginatedList and the documentation for join mapping.
Thank you for looking into this.
Tim Haley
> 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