ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brian Parkinson" <pa...@avaning.com>
Subject Newbie tries to solve the N+1 selects problem and cries for help
Date Thu, 13 Dec 2007 17:14:12 GMT
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...

 

 


Mime
View raw message