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"

<sqlMap namespace="custom1">

   <resultMap id="PhonesResult"
     <result property="phones" resultMap="custom1.PhoneResult"/>

   <resultMap id="PhoneResult"
     <result column="ID" property="id" jdbcType="INTEGER"/>
     <result column="Phone" property="phone" jdbcType="VARCHAR"/>

   <resultMap id="StoreGeneralResult"
     <result column="StoreID" property="storeid" jdbcType="INTEGER"/>
     <result column="StoreName" property="storename"  

   <resultMap id="StoreResult"
     extends="PhonesResult" <!-- I believe this just pulls in the one  
result element in the PhonesResult resultMap. -->
     <result property="storeGeneral"

   <select id="selectStoreByNumber"

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



View raw message