See the solution below.
On Apr 13, 2007, at 10:14 AM, Mark Volkmann wrote:
I'm using groupBy to avoid a 1:N select. I've simplified what I'm
doing to isolate the problem.
The relationships between my object model classes are as follows.
A Store has
- a storeGeneral property that is a StoreGeneralModel object
- a phones property that is a List of PhoneModel objects
A StoreGeneralModel has
- a storeid property that is an int
- a storename property that is a String
A PhoneModel has
- an id property that is an int
- a phone property that is a String
When I run the selectStoreByNumber select, I get back two Store
objects that each contain one phone number, but I should get back
one Store object that contains two phone numbers.
Here is my fairly simple SqlMap file. Can you spot what I'm doing
wrong? I suspect the problem is my groupBy attribute.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd";>
<sqlMap namespace="custom1">
<resultMap id="PhonesResult"
class="com.mycompany.myapp.model.Phones">
<result property="phones" resultMap="custom1.PhoneResult"/>
</resultMap>
<resultMap id="PhoneResult"
class="com.mycompany.myapp.model.PhoneModel">
<result column="ID" property="id" jdbcType="INTEGER"/>
<result column="Phone" property="phone" jdbcType="VARCHAR"/>
</resultMap>
<resultMap id="StoreGeneralResult"
class="com.mycompany.myapp.model.StoreGeneralModel">
<result column="StoreID" property="storeid" jdbcType="INTEGER"/>
<result column="StoreName" property="storename"
jdbcType="VARCHAR"/>
</resultMap>
<resultMap id="StoreResult"
class="com.mycompany.myapp.model.Store"
extends="PhonesResult" <!-- I believe this just pulls in the
one result element in the PhonesResult resultMap. -->
groupBy="storeGeneral.storeid">
Change the previous line to
groupBy="storeGeneral">
Note that you can "group by" a property that isn't a primitive type.
<result property="storeGeneral"
resultMap="custom1.StoreGeneralResult"/>
</resultMap>
<select id="selectStoreByNumber"
resultMap="custom1.StoreResult"
parameterClass="java.lang.Integer">
<!-- Don't worry about the table tblPhoneAssignments.
I've verified by running this query through another tool
that I get back two rows
that have the same StoreID and different Phone values. -->
select sg.StoreID, sg.StoreName, ph.ID, ph.Phone
from stores..tblStoreGeneral sg
inner join stores..tblPhoneAssignments pa on
pa.ID = sg.StoreID and pa.AreaID = 1
inner join stores..tblPhone ph on pa.PhoneID = ph.ID
where sg.StoreID = #storeid#
order by sg.StoreID, ph.ID
</select>
</sqlMap>
