Not sure if I followed exactly what you are trying to do but if you are
trying to screen out categories with no items why not doing it in your
select statement as follow:
<select
id="getAllCategoriesWithNPlusOne"
resultMap="categoryWithNPlusOne">
select
c.category_code,
c.category_name,
i.item_id,
i.item_name
from
categories c
left join items i on c.category_code = i.category_code
where
i.item_id is not NULL
order by
c.category_code;
</select>
I added the WHERE clause
HTH
Pascal
On Tue, 2005-02-08 at 20:58, Mark Nabours wrote:
>
> Hello,
>
> We have a situation where it would be advantageous for performance reasons
> to avoid lazy loading and utilize the recently implemented (N + 1)
> solution. Unfortunately, when utilizing a left join the results are not
> the same as the lazy loading solution. (Depending on the objects being
> stored in the list, it's possible that the (N+1) solution will throw an
> exception where the lazy loading solution will not.)
>
> To illustrate the problem I've put together a very simple example.
>
> Please consider the following simple tables of data, categories and items:
>
> +---------------+---------------+
> | category_code | category_name |
> +---------------+---------------+
> | cat1 | Category 1 |
> | cat2 | Category 2 |
> | cat3 | Category 3 |
> +---------------+---------------+
>
> +---------+-----------+---------------+
> | item_id | item_name | category_code |
> +---------+-----------+---------------+
> | item1 | Item 1 | cat1 |
> | item2 | Item 2 | cat1 |
> | item3 | Item 3 | cat1 |
> | item4 | Item 4 | cat1 |
> | item5 | Item 5 | cat2 |
> | item6 | Item 6 | cat2 |
> +---------+-----------+---------------+
>
> The challenge is to load a List of items for each category.
>
> Here are the two extremely simple data objects used to illustrate the
> issue:
>
> //==============================================================
> // Category.java
> //==============================================================
> package com.alliancesys.ibatisdemo.domain;
>
> import java.util.Iterator;
> import java.util.List;
>
> public class Category {
>
> private String code;
> private String name;
> private List items;
>
> public Category() {
> super();
> }
>
> public String getCode() {
> return code;
> }
>
> public List getItems() {
> return items;
> }
>
> public String getName() {
> return name;
> }
>
> public void setCode(String string) {
> code = string;
> }
>
> public void setItems(List list) {
> items = list;
> }
>
> public void setName(String string) {
> name = string;
> }
>
> /**
> * @see java.lang.Object#toString()
> */
> public String toString() {
> StringBuffer buffer = new StringBuffer();
> buffer.append(this.getClass().getName());
> buffer.append("-");
> buffer.append("code:");
> buffer.append(code);
> buffer.append(",name:");
> buffer.append(name);
> buffer.append(",items:[");
> List items = getItems();
> if (items != null) {
> for (Iterator iter = items.iterator(); iter.hasNext();) {
> buffer.append(iter.next());
> buffer.append(";");
> }
> }
> buffer.append("]");
> return buffer.toString();
> }
>
> }
>
>
> //==============================================================
> // Item.java
> //==============================================================
> package com.alliancesys.ibatisdemo.domain;
>
> public class Item {
>
> private String id;
> private String name;
> private String categoryCode;
>
> public Item() {
> super();
> }
>
> public String getCategoryCode() {
> return categoryCode;
> }
>
> public String getId() {
> return id;
> }
>
> public String getName() {
> return name;
> }
>
> public void setCategoryCode(String categoryCode) {
> this.categoryCode = categoryCode;
> }
>
> public void setId(String id) {
> this.id = id;
> }
>
> public void setName(String name) {
> this.name = name;
> }
>
> /**
> * @see java.lang.Object#toString()
> */
> public String toString() {
> StringBuffer buffer = new StringBuffer();
> buffer.append(this.getClass().getName());
> buffer.append("-");
> buffer.append("id:");
> buffer.append(id);
> buffer.append(",name:");
> buffer.append(name);
> buffer.append(",categoryCode:");
> buffer.append(categoryCode);
> return buffer.toString();
> }
>
> }
>
> Here is the sqlMap file that contains both the lazy loading solution (
> getAllCategoriesWithLazyLoad) and the N + 1 solution (
> getAllCategoriesWithNPlusOne):
>
> <?xml version="1.0" encoding="UTF-8" standalone="no"?>
> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
> "http://www.ibatis.com/dtd/sql-map-2.dtd";>
>
> <sqlMap namespace="Item">
>
> <typeAlias
> alias="item"
> type="com.alliancesys.ibatisdemo.domain.Item" />
>
> <typeAlias
> alias="category"
> type="com.alliancesys.ibatisdemo.domain.Category" />
>
> <resultMap
> id="itemMap"
> class="item">
> <result
> property="id"
> column="item_id" />
> <result
> property="name"
> column="item_name" />
> <result
> property="categoryCode"
> column="category_code" />
>
> </resultMap>
>
> <resultMap
> id="categoryWithLazyLoad"
> class="category">
> <result
> property="code"
> column="category_code"/>
> <result
> property="name"
> column="category_name"/>
> <result
> property="items"
> column="category_code"
> select="getItemsByCategoryCode" />
> </resultMap>
>
> <resultMap
> id="categoryWithNPlusOne"
> class="category"
> groupBy="code">
> <result
> property="code"
> column="category_code"/>
> <result
> property="name"
> column="category_name" />
> <result
> property="items"
> resultMap="Item.itemMap" />
> </resultMap>
>
> <select
> id="getItemsByCategoryCode"
> parameterClass="java.lang.String"
> resultMap="itemMap">
> select
> item_id,
> item_name,
> category_code
> from
> items
> where
> category_code = #value#;
> </select>
>
> <select
> id="getAllCategoriesWithLazyLoad"
> resultMap="categoryWithLazyLoad">
> select
> category_code,
> category_name
> from
> categories;
> </select>
>
> <select
> id="getAllCategoriesWithNPlusOne"
> resultMap="categoryWithNPlusOne">
> select
> c.category_code,
> c.category_name,
> i.item_id,
> i.item_name
> from
> categories c
> left join items i on c.category_code = i.category_code
> order by
> c.category_code;
> </select>
>
> </sqlMap>
>
> The following test case exercises the two approaches:
>
> package com.alliancesys.ibatis.testing;
>
> import java.io.Reader;
> import java.sql.SQLException;
> import java.util.Iterator;
> import java.util.List;
>
> import com.alliancesys.ibatisdemo.domain.Category;
> import com.ibatis.common.resources.Resources;
> import com.ibatis.sqlmap.client.SqlMapClient;
> import com.ibatis.sqlmap.client.SqlMapClientBuilder;
>
> import junit.framework.TestCase;
>
> public class IBATISAggregateObjectTest extends TestCase {
>
> /**
> * Constructor for IBATISAggregateObjectTest.
> * @param arg0
> */
> public IBATISAggregateObjectTest(String arg0) {
> super(arg0);
> }
>
> public static void main(String[] args) {
> junit.textui.TestRunner.run(IBATISAggregateObjectTest.class);
> }
>
> public void testGetCategories() throws Exception {
> Reader reader =
> Resources.getResourceAsReader(
> getClass().getClassLoader(),
> "sql-map-config.xml");
> SqlMapClient sqlMapClient =
> SqlMapClientBuilder.buildSqlMapClient(reader);
>
> try {
>
> sqlMapClient.startTransaction();
>
> //Fetch a list of categories with lazy load of items.
> List categoriesWithLazyLoad =
> sqlMapClient.queryForList(
> "getAllCategoriesWithLazyLoad", null);
>
> for (Iterator iter = categoriesWithLazyLoad.iterator();
> iter.hasNext();
> ) {
> //force lazy load
> ((Category)iter.next()).getItems().iterator();
>
> }
> //print each category
> for (Iterator iter = categoriesWithLazyLoad.iterator();
> iter.hasNext();
> ) {
> System.out.println(iter.next());
> }
>
> // Fetch a list of categories with N+1 solution
> List categoriesWithNPlusOne = sqlMapClient.queryForList(
> "getAllCategoriesWithNPlusOne", null);
> // print categories expect same output as before
> for (Iterator iter = categoriesWithNPlusOne.iterator();
> iter.hasNext();
> ) {
> System.out.println(iter.next());
> }
> } catch (SQLException e) {
> throw e;
> } finally {
> sqlMapClient.endTransaction();
> }
>
> }
>
> }
>
> We expect both approaches to yield the same output. But for the cat3
> category under the lazy loading approach the following is outputted:
> com.alliancesys.ibatisdemo.domain.Category-code:cat3,name:Category
> 3,items:[]
> Indicating that the items property was loaded with an empty List. This is
> exactly what I expect would happen since there are no items for that
> category. All is well except that performance stinks for our real world
> needs because of the N + 1 issue.
>
> Looking at cat3 for the N + 1 solution approach, the following is
> outputted:
> com.alliancesys.ibatisdemo.domain.Category-code:cat3,name:Category
> 3,items:[com.alliancesys.ibatisdemo.domain.Item-id:null,name:null,categoryCode:cat3;]
> Indicating that a nonexistent item was loaded into the list for the items
> property.
>
> I believe the issue is that the (N + 1) solution approach does not handle
> left joins. (It's not acceptable for me to switch the join to an inner
> join or I would basically lose cat3 from the collection.) The results from
> the left join are as follows:
>
> +---------------+---------------+---------+-----------+
> | category_code | category_name | item_id | item_name |
> +---------------+---------------+---------+-----------+
> | cat1 | Category 1 | item3 | Item 3 |
> | cat1 | Category 1 | item4 | Item 4 |
> | cat1 | Category 1 | item1 | Item 1 |
> | cat1 | Category 1 | item2 | Item 2 |
> | cat2 | Category 2 | item5 | Item 5 |
> | cat2 | Category 2 | item6 | Item 6 |
> | cat3 | Category 3 | NULL | NULL |
> +---------------+---------------+---------+-----------+
>
> The cat3 row seems to be problematic unless there are additional
> configuration settings in the SQLMap file that address this.
>
> So in summary, the lazy loading approach yields the correct results, but we
> need to utilize the (N + 1) solution for performance but it yields bogus
> results. Is there already a solution for this? Or should this be logged
> in JIRA?
>
> We would appreciate a quick turnaround on this because the (N + 1) support
> is such a great feature that we want to be able to utilize to simplify our
> code.
>
> Thanks,
> Mark
>
> (See attached file: Item.xml)(See attached file: Item.java)(See attached
> file: Category.java)(See attached file: IBATISAggregateObjectTest.java)
>
>
> -----------------------------------------
> E-mail Disclaimer: The information contained in this e-mail, and in any
> accompanying documents, may constitute confidential and/or legally
> privileged information. The information is intended only for use by the
> designated recipient. If you are not the intended recipient (or
> responsible for the delivery of the message to the intended recipient),
> you are hereby notified that any dissemination, distribution, copying, or
> other use of, or taking of any action in reliance on this e-mail is
> strictly prohibited. If you have received this email communication in
> error, please notify the sender immediately and delete the message from
> your system.
