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...
