ibatis-user-java mailing list archives

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