ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kris Jenkins <krisajenk...@yahoo.co.uk>
Subject Re: Avoiding N+1 Selects (1:M and M:N)
Date Wed, 09 Feb 2005 10:04:48 GMT
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