You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Clinton Begin (JIRA)" <ib...@incubator.apache.org> on 2004/12/15 15:36:56 UTC
[jira] Commented: (IBATIS-27) N+1 Select statements
[ http://nagoya.apache.org/jira/browse/IBATIS-27?page=comments#action_56706 ]
Clinton Begin commented on IBATIS-27:
-------------------------------------
Good news, this is already done. It will be released in 2.0.9.
Here's an example from the JPetStore DB (look for groupBy="" in resultMap and resultMap="" in result. This is actually a double repeating group (both category and product repeat for each item). A single repeating group mapping will only need 2 resultMap elements. The cool thing is that it only iterates once over the result set. It's essentially unlimited how many joins and collections you can populate. groupBy="" can be used independently as well.
<resultMap id="categoryResult" class="testdomain.Category" groupBy="categoryId">
<result property="categoryId" column="catid"/>
<result property="name" column="name"/>
<result property="description" column="descn"/>
<result property="productList" resultMap="productResult"/>
</resultMap>
<resultMap id="productResult" class="testdomain.Product" groupBy="productId">
<result property="productId" column="productid"/>
<result property="categoryId" column="category"/>
<result property="name" column="name"/>
<result property="description" column="descn"/>
<result property="itemList" resultMap="itemResult"/>
</resultMap>
<resultMap id="itemResult" class="testdomain.Item">
<result property="itemId" column="itemid"/>
<result property="productId" column="productid"/>
<result property="listPrice" column="listprice"/>
<result property="unitCost" column="unitcost"/>
<result property="supplierId" column="supplier"/>
<result property="status" column="status"/>
<result property="attribute1" column="attr1"/>
<result property="quantity" column="qty"/>
</resultMap>
<select id="getAllCategories" resultMap="categoryResult" >
select *
from category c, product p, item i, inventory v
where c.catid = p.category
and p.productid = i.productid
and i.itemid = v.itemid
</select>
> N+1 Select statements
> ---------------------
>
> Key: IBATIS-27
> URL: http://nagoya.apache.org/jira/browse/IBATIS-27
> Project: iBatis for Java
> Type: Improvement
> Components: SQL Maps
> Versions: 2.0.8
> Reporter: Raymod Zhang
>
> This issue has been brought up a few time before. But since it is such an important one, I have to post over here again to serve as a reminder.
> Original post by Clinton in Sourceforge:
> *******************BEGIN OF QUOTE***************************
> Amidst the many messages of the other two threads, this topic might have become fuzzy. The other two threads were largely dealing with syntax and schema changes to reduce the verbosity around joins that included duplicate column names.
>
> This had nothing to do with the N+1 selects solution.
>
> iBATIS already has an N+1 solution for 1:1 relationships (nested bean prop syntax w/join). It's very simple and not verbose at all. Duplicate column names are easily resolved using SQL aliases.
>
> As for the N+1 solution for 1:M and M:N, these are documented thoroughly in the Developer Guide. It clearly states in the developer guide that a solution for this is coming. 2.0 was designed from the start to handle this and it won't be a difficult change.
>
> Gilles and I have briefly discussed the XML stanzas and/or attributes that we'll need to add for this. It's simply a matter of making a choice. The community will be involved with making that choice. We'll hopefully get the alternatives posted here soon.
> **********************END OF QUOTE************************
> I am willing to help out if you guys need hands. Rather than waiting and complaiting, I'm ok to do it myself :) But first thing I need to confirm is the approach. Maybe clinton can enlighten me here.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://nagoya.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