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:[EMAIL PROTECTED] 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 <[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 ... ?
-- Kris Jenkins Email: [EMAIL PROTECTED] Blog: http://cafe.jenkster.com/ Wiki: http://wiki.jenkster.com/
