Which I have just finished. ;-)
iBATIS is very cool - a row handler is completely simple to implement. Wicked. parki... ________________________________ From: Poitras Christian [mailto:[EMAIL PROTECTED] Sent: Thursday, December 13, 2007 3:02 PM To: [email protected] Subject: RE: Newbie tries to solve the N+1 selects problem and cries for help In this case, you have to implement a row handler. Christian ________________________________ From: Brian Parkinson [mailto:[EMAIL PROTECTED] Sent: Thursday, December 13, 2007 12:14 PM To: [email protected] Subject: Newbie tries to solve the N+1 selects problem and cries for help Hello: I am having a problem with the ol' N+1 selects problem - wondering if someone can help. I have a Status object, which contains a list of StatusEquipment objects, as well as a list of StatusZone objects. I want, in one select statement, to retrieve the one Status object, and populate the N StatusEquipment and M StatusZone objects in one call. What I am finding is that I'm getting some sort of cross product, and so returning way more instances than I want. Here are my result maps: <resultMap id="StatusEquipmentResultMap" class="statusEquipment" groupBy="id"> <result property="id" column="StatusEquipmentID" /> <result property="statusId" column="StatusID" /> <!-- other columns --> </resultMap> <resultMap id="StatusZoneResultMap" class="statusZone" groupBy="id"> <result property="id" column="StatusZoneID" /> <result property="statusId" column="StatusID" /> <!-- other columns --> </resultMap> <resultMap id="StatusResultMap" class="status" groupBy="id"> <result property="id" column="StatusID" /> <result property="thermostatIdentifier" column="ThermostatIdentifier" /> <!-- other columns --> <result property="equipment" resultMap="StatusEquipmentResultMap" /> <result property="zones" resultMap="StatusZoneResultMap" /> </resultMap> I don't think the groupBy in StatusEquipmentResultMap and StatusZoneResultMap do anything, but I've been trying everything. The select statement: <select id="Status.selectByThermostat" parameterClass="string" resultMap="StatusResultMap"> SELECT s.StatusID, s.ThermostatIdentifier, -- other Status columns se.StatusEquipmentID, -- other StatusEquipment columns sz.StatusZoneID, -- other StatusZone columns FROM status s JOIN StatusEquipment se ON s.StatusID = se.StatusID JOIN StatusZone sz ON s.StatusID = sz.StatusID WHERE s.ThermostatIdentifier = #value#; </select> So, if I have a Status object with 2 StatusEquipment children and 3 StatusZone children, when I select the Status object, I'm getting back 2 StatusEquipment (correct) but 6 StatusZone objects (yikes!), as I guess a cross product is happening. I'm no SQL guru, and I'm stumped. Am I stuck having to implement a RowHandler (or using 3 select statements)? Any help is appreciated - I'd love to have this all work in one select statement. Cheers, parki...
