Uploaded image for project: 'iBatis for Java [READ ONLY]'
  1. iBatis for Java [READ ONLY]
  2. IBATIS-503

Optimised groupBy implementation with row handler support

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.3.0
    • Fix Version/s: None
    • Component/s: Core
    • Labels:
      None

      Description

      I previously raised a bug against groupBy usage with row handlers. Basically if you had data like this:

      USER_NUMBER,ACCOUNT_NUMBER,SECURITY_CODE,UNITS
      1,123,GOOG,100
      1,123,MSFT,200
      1,123,ORCL,300
      1,456,AAPL,150
      2,789,GOOG,120

      and an abbreviated result map of:

      <resultMap id="holding" resultClass="com.myclass.Holding">
      <result proeprty="securityCode" column="SECURITY_CODE"/>
      <result property="units" column="UNITS"/>
      </resultMap>

      <resultMap id="account" resultClass="com.myclass.Account" groupBy="userNumber, accountNumber">
      <result property="userNumber" column="USER_NUMBER"/>
      <result property="accountNumber" column="ACCOUNT_NUMBER"/>
      <result property="holdings" resultMap="holding"/>
      </resultMap>

      And yes this could be nested 3 levels (or more). In the original bug report if you used queryWithRowHandler() this was the result:

      1,123,GOOG,100
      --> Fire for (1,123) Note: at this point holdings is of size 1 with only GOOG in there.
      1,123,MSFT,200
      1,123,ORCL,300
      1,456,AAPL,150
      --> Fire for (1,456)
      2,789,GOOG,120
      --> Fire for (2,789)

      Now eventually it was decided this wasn't a bug because ibatis groupBy does implicit ordering (in fact I believe it stores the entire result set in a map using an identity key or something similar based on the groupBy expression). The reason for this is of course so the query still returns the same result no matter the ordering of the result set. This has several problems:

      1. The entire result set must be loaded before you can safely return any results (which goes against why you're using row handlers in the first place);

      2. The row handler behaviour is misleading and arguably wrong; and

      3. It is probably more efficient to sort the result with SQL (assuming its appropriately indexed, which it should be anyway). This makes the internal sorting unnecessary.

      My first proposal is to make the use of queryWithRowHandler() for a groupBy query to throw an error.

      My second proposal is to have some kind of optimised groupBy alternative that does no internal sorting. Perhaps this could take the form of another attribute (eg groupBySort="false"). The groupBy expression basically forms an identity key. For each unique groupBy tuple there will be one row in the result set. The optimised groupBy will basically "break" (in the DB/reporting sense) on a change in the identity expression. Those of you who have done reporting languages like Natural, Jasper/Crystal Reports and the like will be familiar with the concept of break conditions.

      The optimised/unsorted gorupBy alternative will allow queryWithRowHandler() and it will behave "correctly". By this i Mean that if, as in the above dataset, the row handler for the first account (3 holdings) will be called after the third holding is added to the Account object.

      I can (and have) coded a row handler that basically does this but its tedious and it has the disadvantage that the sort is still happening and the entire data set is getting loaded into memory. IMHO ibatis needs a better solution.

        Attachments

        1. RollupRowHandler.java
          9 kB
          Derek Hulley

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              cletus William Shields
            • Votes:
              1 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: