Ah, then why have it in your select -- select
p.person_id,p.first_name,p.last_name,a.address,a.city,a.country ?? I
think, based on the documentation I've seen, the groupBy only works if
the groupBy column referenced is a result property column in the
resultMap, which may be your problem.
from the ibatis documentation...
Avoiding N+1 Selects (1:M and M:N)
This is similar to the 1:1 situation above, but is of even greater
concern due to the potentially large amount
of data involved. The problem with the solution above is that whenever
you load a Category, two SQL
statements are actually being run (one for the Category and one for the
list of associated Products). This
problem seems trivial when loading a single Category, but if you were to
run a query that loaded ten (10)
Categories, a separate query would be run for each Category to load its
associated list of Products. This
results in eleven (11) queries total: one for the list of Categories and
one for each Category returned to load
each related list of Products (N+1 or in this case 10+1=11). To make
this situation worse, we’re dealing
with potentially large lists of data.
1:N & M:N Solution
iBATIS fully solves the N+1 selects solution. Here is an example:
<sqlMap namespace="ProductCategory">
<resultMap id=”categoryResult” class=”com.ibatis.example.Category”
groupBy=”id”>
<result property=”id” column=”CAT_ID”/>
<result property=”description” column=”CAT_DESCRIPTION”/>
<result property=”productList” resultMap=”ProductCategory.productResult”/>
</resultMap>
<resultMap id=”productResult” class=”com.ibatis.example.Product”>
<result property=”id” column=”PRD_ID”/>
<result property=”description” column=”PRD_DESCRIPTION”/>
</resultMap>
<select id=”getCategory” parameterClass=”int” resultMap=”categoryResult”>
select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
from CATEGORY C
left outer join PRODUCT P
on C.CAT_ID = P.PRD_CAT_ID
where CAT_ID = #value#
</select>
</sqlMap>
Fábio Pisaruk wrote:
Okay Lisa tks
But remember that the bean Person doesn´t have personId property and i
don´t think adding it to be a good idea because person_id is an idea
concerning data base not my bean or the bussinesses rules.
regards
On 9/14/07, *Lisa Jenkins* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
person_id column is not referenced is not in the resultMap, if you add
that, it should work....
<resultMap id="resulMap-person" class="Person" groupBy="person_id">
<result property="personId" column="person_id"/>
<result property="firstName" column="first_name"/>
<result property="lasttName" column="last_name"/>
<result property="addresses" resultMap="resultMap-adress"/>
</resultMap>
Fábio Pisaruk wrote:
> Hi,
>
> Is there a way to use groupBy with a column name?
> I need to do so 'cause there is no property that uniquely
identify the
> bean
> and i am not able to change it do add one.
> For example:
> Suppose i´ve got two tables: Person and Address.
>
> Person:
> person_id
> first_name
> last_name
>
> Address:
> person_id
> address_id
> address
> country
> city
>
> And two beans:
> Person
> firstName
> lastName
> addresses(Address[])
> Address
> address
> country
> city
>
> My maps:
>
> <resultMap id="resulMap-person" class="Person" groupBy="person_id">
> <result property="firstName" column="first_name"/>
> <result property="lasttName" column="last_name"/>
> <result property="addresses" resultMap="resultMap-adress"/>
> </resultMap>
>
> <resultMap id="resulMap-address" class="Address">
> <result property="address" column="address"/>
> <result property="country" column="country"/>
> <result property="city" column="city"/>
> </resultMap>
>
> My sql:
>
> <select id="get-person-by-id" parameterClass="int"
> resultMap="resulMap-person">
> select
p.person_id,p.first_name,p.last_name,a.address,a.city,a.country
> from Person p ,Address a
> where p.person_id=#value# and p.person_id=a.person_id
> </select>
>
> In doing so i am not getting the desired result.
> Person information are replicated for each address it contains.
>
> PS: I know two workarounds that i don´t consider good solutions:
> 1-) Creating a wrapperPerson with a person_id attribute and having
> Ibatis grouping result on it or
> 2-) using a nested select to get address for each person:
> <result property="addresses"
select="get-addresses-by-person_id"
> column="person_id"/>
> <select id="get-addresses-by-person_id" parameterClass="int"
> resultMap="resultMap-adress">
> select * from Address where person_id=#value#
> </select>
>
> Thanks in advance
>
> --
> Visto como se não executa logo a sentença sobre a má obra, o coração
> dos filhos dos homens está inteiramente disposto a praticar o mal.
>
>
> --Nerd´s sign
>
> If you have four classes, Everybody, Somebody, Anybody, and
Nobody, if
> Somebody has a bug, it could be Anybody 's fault but Nobody really
> knows, while Everybody shares responsibility.
>
> "Programming today is a race between software engineers striving to
> build bigger and better idiot-proof programs, and the universe
trying
> to build bigger and better idiots. So far, the universe is
winning." -
> Rick Cook
--
Visto como se não executa logo a sentença sobre a má obra, o coração
dos filhos dos homens está inteiramente disposto a praticar o mal.
--Nerd´s sign
If you have four classes, Everybody, Somebody, Anybody, and Nobody, if
Somebody has a bug, it could be Anybody 's fault but Nobody really
knows, while Everybody shares responsibility.
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs, and the universe trying
to build bigger and better idiots. So far, the universe is winning." -
Rick Cook
