ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ralph, Mark" <Mark.Ra...@dpi.wa.gov.au>
Subject RE: Avoiding N+1 Selects (1:M and M:N)
Date Thu, 10 Feb 2005 00:00:34 GMT
Thanks for that help guys - the missing groupBy was the culprit all right.

it works great!

As you suspected - without the groupBy I was getting back one client for each of the query
results.



-----Original Message-----
From: Chen, Tim [mailto:Tim.Chen@NielsenMedia.com]
Sent: Wednesday, 9 February 2005 11:22 PM
To: ibatis-user-java@incubator.apache.org
Subject: RE: Avoiding N+1 Selects (1:M and M:N)


Not from my experience with it (as you see in the FAQ)
You do, however, need to a groupBy to the parent resultMap (in your case
clientAndDealingsMap).
Move the groupBy that Kris sent to that resultMap and see what happens.
Just out of curiousity though.
If you print on the size of List clients and/or print out the clients
list. You would probably see duplicate clients with the code that you
currently have. At least that was what happened for me until I put the
groupBy in.
(Thanks to Larry and Clinton for their irc help on that one)
irc.darkmyst.org #FunkyCodeMonkey and #iBatis for those interested.


-----Original Message-----
From: Kris Jenkins [mailto:krisajenkins@yahoo.co.uk] 
Sent: Wednesday, February 09, 2005 5:05 AM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Avoiding N+1 Selects (1:M and M:N)

Hey Mark,

You seem to be missing the 'groupBy' attribute.  Try this:

<select id="selectClientAndDealingsWithJoin"
parameterClass="java.lang.Long" resultMap="clientAndDealingsMap"
*groupBy="CRoid"*>

Not 100% sure that will solve your problem, but it should certainly get
you closer :-)

Kris

Ralph, Mark wrote:

>Hi Larry,
>
>You're right  - this does indicate that is should work ... however i
cant get it to.
>
>I am only getting one item back in the sub list - though the query
returns multiple results.
>Maybe I am doing something wrong ?
>
>Here is a quick look at what i've got....
>
>Thanks for any help.
>Mark
>
>-------------------------------------
>	Client.java
>-------------------------------------
>public class Client(
>
>	private Long cRoid; // the pK
>
>	// lots of other fields
>
>	List dealings;
>
>    	public List getDealings() {
>        return dealings;
>    	}
>    	public void setDealings(List dealings) {
>        this.dealings = dealings;
>     	}
>      public void addDealing(Dealing dealing) {
>        if( dealings == null) dealings = new ArrayList();        
>	  dealings.add( dealing );
>    }
>}
>
>-------------------------------------
>	Mappings
>-------------------------------------
>
>	<!-- Query that maps client  and dealings using a join.-->
>	<select id="selectClientAndDealingsWithJoin"
parameterClass="java.lang.Long"
resultMap="clientAndDealingsMap"><![CDATA[
>			select *
>			from CLI_CLIENTS, CLI_DEALINGS
>				where CLI_CLIENTS.C_ROID =
#CRoid:NUMERIC# AND
>				CLI_CLIENTS.C_ROID = CLI_DEALINGS.C_ROID
>			]]></select>
>
>	<!-- Mapping for the client object -->
>	<resultMap id="clientAndDealingsMap" class="client">
>		<result property="CRoid" column="C_ROID"/>
>		<!-- the other fields -->
>		<result property="dealings"
resultMap="client.dealingResult"/>
>	</resultMap>
>
>	<!-- Mapping for the client's dealings -->
>	<resultMap id="dealingResult" class="dealing">
>		<result property="DRoid" column="D_ROID"/>
>		<result property="CRoid" column="C_ROID"/>
>		<result property="DRoidPrimary"
column="D_ROID_PRIMARY"/>
>		<result property="dealingStatus"
column="DEALING_STATUS"/>
>		<result property="dealingTypeCode"
column="DEALING_TYPE_CODE"/>
>		<result property="nrpOnlyFlag" column="NRP_ONLY_FLAG"/>
>		<result property="startDate" column="START_DATE"/>
>		<result property="endDate" column="END_DATE"/>
>		<result property="updateLevel" column="UPDATE_LEVEL"/>
>	</resultMap>
>
>-------------------------------------
>	Client Code
>-------------------------------------
>
>        List clients =
sqlMap.queryForList("selectClientAndDealingsWithJoin", cRoid); 
>        Client inflatedClient = (Client) clients.get(0);        
>        System.out.println("Read client " + inflatedClient);
>        System.out.println("Read client dealings size = " + 
> inflatedClient.getDealings().size());
>
>-------------------------------------
>	Output
>-------------------------------------
>
>DEBUG [main] - Created connection 13121387.
>DEBUG [main] - {conn-100000} Connection
>DEBUG [main] - {pstm-100001} PreparedStatement:     select *    from
CLI_CLIENTS, CLI_DEALINGS     where CLI_CLIENTS.C_ROID = ? AND
CLI_CLIENTS.C_ROID = CLI_DEALINGS.C_ROID    
>DEBUG [main] - {pstm-100001} Parameters: [8344318] DEBUG [main] - 
>{pstm-100001} Types: [java.lang.Long] DEBUG [main] - {rset-100002} 
>ResultSet DEBUG [main] - {rset-100002} Header: [C_ROID, 
>CNI_MAINFRAME_SID, SUPPRESSION_FLAG, POI_PROVIDED_FLAG, CLIENT_NUMBER, 
>NEVDIS_CLIENT_NUMBER, C_ROID_RELATED, SITE_CODE, CLI_POI_TYPE_CODE, 
>WMI_CODE, ORGANISATION_NAME, ACN, ABN, CLI_ORGANISATION_TYPE_CODE, 
>SURNAME, FIRST_GIVEN_NAME, SUBSEQUENT_GIVEN_NAMES, BIRTH_DATE, 
>ORGAN_DONOR_CODE, PLACE_OF_BIRTH, HEIGHT, PENSION_FLAG, PENSION_NUMBER,

>PENSION_END_DATE, CLI_PENSION_TYPE_CODE, DEATH_DATE, BUILD_CODE, 
>SEX_CODE, TITLE_CODE, EYE_COLOUR_CODE, HAIR_COLOUR_CODE, CLIENT_TYPE, 
>CLIENT_CLASS, CLIENT_STATUS, UPDATE_LEVEL, 
>CLI_NOTIFICATION_SOURCE_CODE, D_ROID, C_ROID, D_ROID_PRIMARY, 
>DEALING_STATUS, DEALING_TYPE_CODE, NRP_ONLY_FLAG, START_DATE, END_DATE,
UPDATE_LEVEL] DEBUG [main] - {rset-100002} Result: [8344318, 1124577, N,
Y, 3455104, 34551043, 0, null, null, null, null, 0, 0, null, RALPH,
MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH, 179, N, null, null, null,
null, MED, M, null, BLU, BLD, 1, O, A, 4, null, 451257757, 8344318, 0,
A, 20, N, 1987-05-06 00:00:00.0, null, 4] DEBUG [main] - {rset-100002}
Result: [8344318, 1124577, N, Y, 3455104, 34551043, 0, null, null, null,
null, 0, 0, null, RALPH, MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH,
179, N, null, null, null, null, MED, M, null, BLU, BLD, 1, O, A, 4,
null, 467302557, 8344318, 0, I, 21, N, 1996-07-16 00:00:00.0, 1996-12-07
00:00:00.0, 4] DEBUG [main] - {rset-100002} Result: [8344318, 1124577,
N, Y, 3455104, 34551043, 0, null, null, null, null, 0, 0, null, RALPH,
MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH, 179, N, null, null, null,
null, MED, M, null, BLU, BLD, 1, O, A, 4, null, 407589183, 8344318, 0,
I, 21, N, 1990-05-21 00:00:00.0, 1992-05-08 00:00:00.0, 4] DEBUG [main]
- {rset-100002} Result: [8344318, 1124577, N, Y, 3455104, 34551043, 0,
null, null, null, null, 0, 0, null, RALPH, MARK, JUSTIN, 1970-11-29
00:00:00.0, N, PERTH, 179, N, null, null, null, null, MED, M, null, BLU,
BLD, 1, O, A, 4, null, 274537721, 8344318, 0, I, 21, N, 1986-11-29
00:00:00.0, 1989-07-11 00:00:00.0, 4] DEBUG [main] - {rset-100002}
Result: [8344318, 1124577, N, Y, 3455104, 34551043, 0, null, null, null,
null, 0, 0, null, RALPH, MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH,
179, N, null, null, null, null, MED, M, null, BLU, BLD, 1, O, A, 4,
null, 151173044, 8344318, 0, I, 21, N, 2003-12-09 00:00:00.0, 2004-04-05
00:00:00.0, 4] DEBUG [main] - {rset-100002} Result: [8344318, 1124577,
N, Y, 3455104, 34551043, 0, null, null, null, null, 0, 0, null, RALPH,
MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH, 179, N, null, null, null,
null, MED, M, null, BLU, BLD, 1, O, A, 4, null, 136856494, 8344318, 0,
I, 21, N, 1993-02-18 00:00:00.0, 1996-02-17 00:00:00.0, 4] DEBUG [main]
- {rset-100002} Result: [8344318, 1124577, N, Y, 3455104, 34551043, 0,
null, null, null, null, 0, 0, null, RALPH, MARK, JUSTIN, 1970-11-29
00:00:00.0, N, PERTH, 179, N, null, null, null, null, MED, M, null, BLU,
BLD, 1, O, A, 4, null, 94907911, 8344318, 0, A, 21, N, 1992-05-08
00:00:00.0, 1992-10-19 00:00:00.0, 4] DEBUG [main] - {rset-100002}
Result: [8344318, 1124577, N, Y, 3455104, 34551043, 0, null, null, null,
null, 0, 0, null, RALPH, MARK, JUSTIN, 1970-11-29 00:00:00.0, N, PERTH,
179, N, null, null, null, null, MED, M, null, BLU, BLD, 1, O, A, 4,
null, 764098386, 8344318, 0, A, 21, null, 2004-06-22 00:00:00.0, null,
4] DEBUG [main] - Returned connection 13121387 to pool.
>Read client ClientBean
>   cRoid='8344318'
>...
>Read client dealings size = 1
>Read client dealingDealingBean
>   dRoid='451257757'
>   cRoid='8344318'
>   dRoidPrimary='null'
>   dealingStatus='A'
>   dealingTypeCode='20'
>   nrpOnlyFlag='N'
>   startDate='Wed May 06 00:00:00 GMT+08:00 1987'
>   endDate='null'
>   updateLevel='4'
>
>time was 844ms
>
>
>-----Original Message-----
>From: Larry Meadors [mailto:larry.meadors@gmail.com]
>Sent: Wednesday, 9 February 2005 12:25 PM
>To: ibatis-user-java@incubator.apache.org
>Subject: Re: Avoiding N+1 Selects (1:M and M:N)
>
>
>Check out the FAQ on the wiki:
>
>http://wiki.apache.org/ibatis/How_20do_20I_20get_20around_20the_20n_2b1
>_20selects_20problem_3f
>
>Larry
>
>
>On Wed, 9 Feb 2005 10:48:31 +0800, Ralph, Mark
<Mark.Ralph@dpi.wa.gov.au> wrote:
>  
>
>> 
>> 
>>
>> > 1:N & M:N Solution? Currently the feature that resolves this issue 
>> > not
>>implemented. 
>>    
>>
>>> It will be included in a release
>>>      
>>>
>> 
>>
>>Any ideas when - this is looking like a show stopper for us ...  ? 
>>
>>
>>    
>>
>
>  
>


--
Kris Jenkins
Email:  kris@jenkster.com
Blog:   http://cafe.jenkster.com/
Wiki:   http://wiki.jenkster.com/




Mime
View raw message