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:[EMAIL PROTECTED]
Sent: Wednesday, 9 February 2005 12:25 PM
To: [email protected]
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 <[EMAIL PROTECTED]> 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 ... ?
>
>
