Before acting on this e-mail or opening any attachments you are advised to read The Caudwell Holdings group of companies' disclaimer at the end of this e-mail. =======================================================
I have two tables, one containing Receipts and the other containing receipt items. I also have java objects Receipt and ReceiptLine where a Receipt contains a List of ReceiptLine objects. I have followed the instructions on the wiki to try to get around the N+1 selects problem, but instead of returning a List of unique Receipts with a list of ReceiptLine objects, I get a list of duplicate receipts, each with one ReceiptLine objects. It seems as if the group by part of the solution isn't doing anything - I;ve tried running the sql in a bare sql client, and the records seem to be ordered correctly, but iBatis isn;t collating them? My (simplified) sqlmap looks like this: <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"; > <sqlMap namespace="VarianceSummaryReceipt"> <typeAlias alias="Receipt" type="com.phones4u.datamanagement.datamodel.varianceSummary.impl.VarianceSummaryReceiptImpl"/> <typeAlias alias="ReceiptLine" type="com.phones4u.datamanagement.datamodel.varianceSummary.impl.VarianceSummaryReceiptLineImpl"/> <resultMap class="ReceiptLine" id="ReceiptLinesResult"> <result property="consignmentNumber" column="consignment_no"/> </resultMap> <resultMap class="Receipt" id="ReceiptResult"> <result property="deliveryId" column="delivery_id"/> <result property="lines" resultMap="VarianceSummaryReceipt.ReceiptLinesResult"/> </resultMap> <select id="getVarianceSummaryReceipt" parameterClass="map" resultMap="ReceiptResult"> select distinct head.delivery_id, lines.consignment_no from variancesummaryreceipthead head, variancesummaryreceiptlines lines where head.delivery_id=lines.delivery_id <dynamic prepend="and"> <isParameterPresent> <isPropertyAvailable property="deliveryId"> head.delivery_id=#deliveryId# </isPropertyAvailable> </isParameterPresent> </dynamic> group by head.delivery_id, lines.consignment_no </select> </sqlMap> ======================================================= Confidentiality Notice This e-mail is confidential and intended for the use of the named recipient only. If you are not the intended recipient please notify us by telephone immediately on +44(0)1782 600600 or return it to us by e-mail. Please then delete it from your system and note that any use, dissemination, forwarding, printing or copying is strictly prohibited. Any views or opinions are solely those of the author and do not necessarily represent those of The Caudwell Holdings group of companies. Encryptions and Viruses Please note that this e-mail and any attachments have not been encrypted. They may therefore be liable to be compromised. Please also note that it is your responsibility to scan this e-mail and any attachments for viruses. We do not, to the extent permitted by law, accept any liability (whether in contract, negligence or otherwise) for any virus infection and/or external compromise of security and/or confidentiality in relation to transmissions sent by e-mail. Monitoring Activity and use of The Caudwell Holdings group of companies' systems is monitored to secure its effective use and operation and for other lawful business purposes. Communications using these systems will also be monitored and may be recorded to secure effective use and operation and for other lawful business purposes.
