ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Volkmann <m...@ociweb.com>
Subject Re: 1:N select issue
Date Fri, 13 Apr 2007 18:52:15 GMT
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>


Mime
View raw message