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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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:[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/
