James, I had same problem (posted before). seems it is a bug ( i didn't claim it was a bug before since I don't know if it was due to my fault in configuration file). Luckily, little bit slow was not big deal for the project I worked on.
Can some one in the iBATIS group take a look at this issue? btw, I really liked iBATIS. Implemented in one of my project. Try to push other groups to use it too. thx, Tony James Hillyerd wrote on 1/31/2006, 4:59 PM: > I tried to search the archives for any answers to this question, but > for some reason it won't let you search for "N+1" and "complex" didn't > find the answer I'm looking for. > > I have a set of queries that is successfully loading an object graph > that looks like: > > Client: > ContactInfo: > Address > PhoneNumber (Collection) > > Using the following SQL Map: > > <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"; > namespace="ClientExport"> > <resultMap id="clientMap" class="com.activerain.model.Client"> > <result property="clientID" column="client_id"/> > <result property="source" column="source"/> > <result property="folderName" column="folder_name"/> > <result property="insertDate" column="insert_date"/> > <result property="contactInfo.contactInfoID" > column="contact_info_id"/> > <result property="contactInfo.firstName" column="first_name"/> > <result property="contactInfo.lastName" column="last_name"/> > <result property="contactInfo.organization" > column="organization"/> > <result property="contactInfo.emailAddress" > column="email_address"/> > <result property="contactInfo.phoneNumbers" > column="contact_info_id" > select="getClientExportPhoneNumbersByContact"/> > <result property="contactInfo.address.addressID" > column="address_id"/> > <result property="contactInfo.address.addressLine1" > column="address_line1"/> > <result property="contactInfo.address.addressLine2" > column="address_line2"/> > <result property="contactInfo.address.zipCode" > column="zip_code"/> > <result property="contactInfo.address.state" column="state"/> > <result property="contactInfo.address.city" column="city"/> > </resultMap> > > <resultMap id="phoneNumberMap" > class="com.activerain.model.PhoneNumber"> > <result property="phoneNumberID" column="phone_number_id"/> > <result property="phoneNumberType" > column="phone_number_type_id"/> > <result property="areaCode" column="area_code"/> > <result property="phoneNumber" column="phone_number"/> > <result property="extension" column="extension"/> > </resultMap> > > <select id="getClientExportByUserID" resultMap="clientMap"> > select > c.client_id, > c.source, > c.folder_name, > c.insert_date, > ci.contact_info_id, > ci.first_name, > ci.last_name, > ci.organization, > ci.email_address, > a.address_id, > a.address_line1, > a.address_line2, > a.city, > a.state, > a.zip_code > from client c > inner join contact_info ci on c.contact_info_id = > ci.contact_info_id > inner join address a on ci.address_id = a.address_id > where > c.user_id = #value# > and c.is_deleted = false > order by c.client_id > </select> > > <select id="getClientExportPhoneNumbersByContact" > resultMap="phoneNumberMap"> > select > p.phone_number_id, > p.phone_number_type_id, > p.area_code, > p.phone_number, > p.extension > from phone_number p > where > p.contact_info_id = #value# > and p.is_deleted = false > </select> > </sqlMap> > > The above map works fine, but is very slow due to performing an extra > query per client to load the phone numbers. > > When I change the SQL Map to use groupBy (below), my phone number list > always gets set to null on the ContactInfo object (not an empty list, > just null). I cannot find any errors in the log, and when I attach > the debugger, I can see iBATIS creating instances of my PhoneNumber > object, and _sometimes_ calling setPhoneNumberID(), but never any of > the other setters. I've run the database query by hand and can verify > that it is returning the expected results. > > If it helps, I'm running iBATIS 2.1.5 build 582 (which appears to be > the most recent non-beta build at this time). > > Here is my broken SQL Map: > > <sqlMap xmlns:fo="http://www.w3.org/1999/XSL/Format"; > namespace="ClientExport"> > <!-- > We're only going to populate the parts of the Client object > hierarchy that we need, > reducing the amount of work to keep these resultMap's in sync > with > the real Client > class and database tables. > --> > <resultMap id="clientMap" class="com.activerain.model.Client" > groupBy="clientID"> > <result property="clientID" column="client_id"/> > <result property="source" column="source"/> > <result property="folderName" column="folder_name"/> > <result property="insertDate" column="insert_date"/> > <result property="contactInfo.contactInfoID" > column="contact_info_id"/> > <result property="contactInfo.firstName" column="first_name"/> > <result property="contactInfo.lastName" column="last_name"/> > <result property="contactInfo.organization" > column="organization"/> > <result property="contactInfo.emailAddress" > column="email_address"/> > <result property="contactInfo.phoneNumbers" > resultMap="ClientExport.phoneNumberMap"/> > <result property="contactInfo.address.addressID" > column="address_id"/> > <result property="contactInfo.address.addressLine1" > column="address_line1"/> > <result property="contactInfo.address.addressLine2" > column="address_line2"/> > <result property="contactInfo.address.zipCode" > column="zip_code"/> > <result property="contactInfo.address.state" column="state"/> > <result property="contactInfo.address.city" column="city"/> > </resultMap> > > <resultMap id="phoneNumberMap" > class="com.activerain.model.PhoneNumber"> > <result property="phoneNumberID" column="phone_number_id"/> > <result property="phoneNumberType" > column="phone_number_type_id"/> > <result property="areaCode" column="area_code"/> > <result property="phoneNumber" column="phone_number"/> > <result property="extension" column="extension"/> > </resultMap> > > <select id="getClientExportByUserID" resultMap="clientMap"> > select > c.client_id, > c.source, > c.folder_name, > c.insert_date, > ci.contact_info_id, > ci.first_name, > ci.last_name, > ci.organization, > ci.email_address, > a.address_id, > a.address_line1, > a.address_line2, > a.city, > a.state, > a.zip_code, > p.phone_number_id, > p.phone_number_type_id, > p.area_code, > p.phone_number, > p.extension > from client c > inner join contact_info ci on c.contact_info_id = > ci.contact_info_id > inner join address a on ci.address_id = a.address_id > left join phone_number p on ci.contact_info_id = > p.contact_info_id > where > c.user_id = #value# > and c.is_deleted = false > and p.is_deleted = false > order by c.client_id, p.phone_number_id > </select> > </sqlMap> > > Thanks for any help you can provide. > > -james > > -- > James A. Hillyerd <[EMAIL PROTECTED]> > Chief Technical Officer - ActiveRain Corp >
