ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Parkinson" <pa...@avaning.com>
Subject RE: Newbie tries to solve the N+1 selects problem and cries for help
Date Thu, 13 Dec 2007 21:04:30 GMT
Which I have just finished. ;-)


iBATIS is very cool - a row handler is completely simple to implement.








From: Poitras Christian [mailto:Christian.Poitras@ircm.qc.ca] 
Sent: Thursday, December 13, 2007 3:02 PM
To: user-java@ibatis.apache.org
Subject: RE: Newbie tries to solve the N+1 selects problem and cries for


In this case, you have to implement a row handler.





From: Brian Parkinson [mailto:parki@avaning.com] 
Sent: Thursday, December 13, 2007 12:14 PM
To: user-java@ibatis.apache.org
Subject: Newbie tries to solve the N+1 selects problem and cries for



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"

            <result property="id" column="StatusEquipmentID" />

            <result property="statusId" column="StatusID" />

            <!-- other columns -->



      <resultMap id="StatusZoneResultMap" class="statusZone"

            <result property="id" column="StatusZoneID" />

            <result property="statusId" column="StatusID" />

            <!-- other columns -->



      <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" />



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"


                  s.StatusID, s.ThermostatIdentifier, -- other Status

                  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#;



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


Any help is appreciated - I'd love to have this all work in one select







View raw message