Ahh - I see. This is a classic example of the N+1 selects problem. iBATIS has a good solution: use an SQL join and the iBATIS groupBy feature. This link should help:
Jeff Butler
On 12/5/05, Jozef Hribik <[EMAIL PROTECTED]> wrote:
Thank you Jeff, i am using your solution already, but i want to get _categories_with_products_ in single step.
Map map = new HashMap();map.put("parameterY", parameterY);map.put("parameterX", parameterX);List results = sqlMap.queryForList("getCategory", map);
<statement id="getCategory" parameterClass="int" resultMap="get-category-result">
select * from CATEGORY where CAT_DESCRIPTION like '%$ parameterY$%'
</statement>
<statement id="getProductsByCatId" parameterClass="int" resultMap="get-product-result">
select * from PRODUCT where PRD_CAT_ID = #value# and PRD_PRICE = #parameterX#
</statement>
Is it possible or is it a new feature request ?
Jeff Butler wrote:
Here's how to do it with a map:Map map = new HashMap();map.put("parameterY", parameterY);map.put("parameterX", parameterX);List results = sqlMap.queryForList("getProductsByCatId", map);<select id="getProductsByCatId" parameterClass="java.util.Map" resultMap="get-product-result">
select * from PRODUCT where PRD_CAT_ID = # parameterY# and PRD_PRICE = #parameterX#
</select>
You could also do it with a POJO as your parameter class (which is preferred) - the properties in the select statement correspond to properties in your POJO.Jeff Butler
On 12/2/05, Jozef Hribik <[EMAIL PROTECTED] > wrote:I would like to pass an additional parameter to filter a collection property.
I will use an example from iBATIS-SqlMaps-2.pdf, page 27.
How can i select a category with products with certain price specified by parameterX?
<resultMap id="get-category-result" class="com.ibatis.example.Category ">
<result property="id" column="CAT_ID"/>
<result property="description" column="CAT_DESCRIPTION"/>
<result property="productList" column="CAT_ID" select="getProductsByCatId"/>
</resultMap>
<statement id="getProductsByCatId" parameterClass="int" resultMap="get-product-result">
select * from PRODUCT where PRD_CAT_ID = #value# and PRD_PRICE = #parameterX#
</statement>
Thanks
Jozef
