ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bostjan Dolenc <bostjan.dol...@marand.si>
Subject Re: subselects design
Date Mon, 03 Jan 2005 15:44:38 GMT
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...


View raw message