ibatis-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin (JIRA)" <ibatis-...@incubator.apache.org>
Subject [jira] Commented: (IBATIS-27) N+1 Select statements
Date Wed, 15 Dec 2004 14:36:56 GMT
     [ 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 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 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"/>

  <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


> 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:
If you want more information on JIRA, or have a bug to report see:

View raw message