[ http://issues.apache.org/jira/browse/IBATIS-69?page=comments#action_58831 ] Mark Nabours commented on IBATIS-69: ------------------------------------
Alright -- it's verified. The following SQL Map file yields the same results under lazy loading and the (N + 1) solution: <?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="item_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 as item_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, i.category_code as item_category_code from categories c left join items i on c.category_code = i.category_code order by c.category_code; </select> </sqlMap> The only changes are that the alias item_category_code is used in the two selects and that alias is mapped to the categoryCode property on Item via : <result property="categoryCode" column="item_category_code" /> > (N + 1) solution does not yield same results as lazy loading solution > --------------------------------------------------------------------- > > Key: IBATIS-69 > URL: http://issues.apache.org/jira/browse/IBATIS-69 > Project: iBatis for Java > Type: Bug > Components: SQL Maps > Versions: 2.0.9 > Environment: XP Pro, WSAD 5.1.2 > Reporter: Mark Nabours > Attachments: Category.java, Item.java, Item.xml, category_item.sql > > 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. > 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
