You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Emiliano Armellin <ea...@ateikon.com> on 2005/01/03 15:42:26 UTC
subselects design
Hello
I'm a bit confused about the right pattern to implement this kind of
selects with ibatis.
This is the problem: I've got a select to search products in db. I'm
using queryForList(java.lang.String id, java.lang.Object
parameterObject, int skip, int max) to extract the first 100 records.
For every product extracted by this select I have to do other selects to
get price/promotions/etc
This subselects take the product.cd as parameters and others parameters
not in the column of the parent select but passed to the method
searchProducts(Map pars), so I can't use construct like:
<result property="product" column="{id=PROD_CD}" select="getProdPrice"/>
because getProdPrice needs PROD_CD but also user.cdlist,
user.cdcompany... am I wrong?
Is rowHandler implementations the correct way to do this?
--
Emiliano Armellin
*
W_
www.ateikon.com <http://www.ateikon.com>
*
@_
earmellin@ateikon.com <ma...@ateikon.com>
*
T_
*+39 0422 452101*
Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=15653&t=86>
Get Thunderbird <http://www.mozilla.org/products/thunderbird/>
Re: subselects design
Posted by Emiliano Armellin <ea...@ateikon.com>.
Clinton Begin ha scritto:
>Hi Emiliano,
>
>Couple of things:
>
>1) It looks like product and/or price are 1:1 Or M:1 with your parent
>object, so why not just join the data? I would avoid the second
>select, as you're creating an N+1 problem (1 primary select + 100
>--one for each record). Even if it's a collection, version 2.0.9 can
>handle joined data for populating complex collections.
>
>
well, it's not so simple
the getPrice method, for example, retrieve the first value of a list of
values returned by subselect for price
so I can't convert it in a direct join
I'm looking at the new features of 2.0.9...
--
Emiliano Armellin
*
W_
www.ateikon.com <http://www.ateikon.com>
*
@_
earmellin@ateikon.com <ma...@ateikon.com>
*
T_
*+39 0422 452101*
Get Firefox! <http://www.spreadfirefox.com/?q=affiliates&id=15653&t=86>
Get Thunderbird <http://www.mozilla.org/products/thunderbird/>
Re: subselects design
Posted by Clinton Begin <cl...@gmail.com>.
Hi Emiliano,
Couple of things:
1) It looks like product and/or price are 1:1 Or M:1 with your parent
object, so why not just join the data? I would avoid the second
select, as you're creating an N+1 problem (1 primary select + 100
--one for each record). Even if it's a collection, version 2.0.9 can
handle joined data for populating complex collections.
2) RowHandler is a fine way to do anything where you only need/want to
process a single row at a time. If you're trying to create a list of
objects in memory, don't use RowHandler, just use queryForList().
Cheers,
Clinton
On Mon, 03 Jan 2005 15:42:26 +0100, Emiliano Armellin
<ea...@ateikon.com> wrote:
> Hello
>
> I'm a bit confused about the right pattern to implement this kind of
> selects with ibatis.
>
> This is the problem: I've got a select to search products in db. I'm using
> queryForList(java.lang.String id, java.lang.Object parameterObject, int
> skip, int max) to extract the first 100 records.
> For every product extracted by this select I have to do other selects to
> get price/promotions/etc
> This subselects take the product.cd as parameters and others parameters not
> in the column of the parent select but passed to the method
> searchProducts(Map pars), so I can't use construct like:
>
> <result property="product" column="{id=PROD_CD}" select="getProdPrice"/>
>
> because getProdPrice needs PROD_CD but also user.cdlist, user.cdcompany...
> am I wrong?
>
> Is rowHandler implementations the correct way to do this?
>
>
>
>
> --
>
> Emiliano Armellin
>
> W_ www.ateikon.com
>
> @_ earmellin@ateikon.com
>
> T_
> +39 0422 452101
>
>
Re: subselects design
Posted by Bostjan Dolenc <bo...@marand.si>.
Emiliano Armellin wrote:
> Hello
>
> I'm a bit confused about the right pattern to implement this kind of
> selects with ibatis.
>
> This is the problem: I've got a select to search products in db. I'm
> using queryForList(java.lang.String id, java.lang.Object
> parameterObject, int skip, int max) to extract the first 100 records.
> For every product extracted by this select I have to do other selects to
> get price/promotions/etc
> This subselects take the product.cd as parameters and others parameters
> not in the column of the parent select but passed to the method
> searchProducts(Map pars), so I can't use construct like:
>
> <result property="product" column="{id=PROD_CD}" select="getProdPrice"/>
>
> because getProdPrice needs PROD_CD but also user.cdlist,
> user.cdcompany... am I wrong?
If I understand SqlMaps documentation correcty, you can pass any number
of columns from the "parent" ResultSet to the "child" selects. See
chapter "Composite Keys or Multiple Complex Parameters Properties".
But in your case where you load 100 products (Always? If not, how
often?), this lead to a severe case of the "1+N selects" pattern. The
performance will most likely be unacceptable, as you will make 300 queries.
A better way is to do outer join to get related promotions and prices in
the same result set. The mapping might be a bit tricky, though.
An even better way (33% faster than outer join in one rather
unrepresentative test, but straight from production code) is to hash
products by their key (I presume that's PROD_CD) with a queryForMap.
Then load each class of related records with a queryForList, either by
putting all key into the select (select * from promotion <iterate
open="(" close=")" conjunction=", " prepend="where prod_cd
in">#prodCdList[]#</iterate>) or by searching for the original products
again (select * from protion where prod_cd in (select prod_cd from
product where ...). This way you will make only 3 queries, but (unlike
inner join) the driver won't have to transfer NULLs and duplicate
"cells" for products.
> Is rowHandler implementations the correct way to do this?
I suspect not, even though you gave no details about it...
Regards,
Bostjan