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 Wed, 09 Feb 2005 05:43:29 GMT
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 ...  ? 
> 
>

Mime
View raw message