Greetings all... Please ignore my previous post. It had been so long since I had used the old sub-select method of populating complex collection properties that I mad a silly error. Of course, one can mix and match sub-selects and nested result maps (but only one nested result map) in a parent result map.
I will try to use better aim next time before posting... :-( Tim Tim Hammar/HQ/McNichols 03/09/2007 10:50 AM To [email protected] cc Subject Re: Problem with several one to many relationships in breadth We are in the process of rewriting all of our queries / mappings that used several one to many relationships in breadth. We have a lot of them - guess we have lots of dumb luck :-) because they have been in production for quite some time (under 2.1.7 and earlier - from the time the nested submaps feature was introduced). We have chosen this approach vs a custom RowHandler due to the scope of the problem and the desire to not change stable production code (the performance hit will be minimal in our application). Anyways, my question is this: Is it legal to mix collections populated by "resultMap=..." and "select=..." in the same resultMap (which has a groupBy...)? I'm asking because I am getting an error during result mapping when mapping to the collection populated by the "select=..." Here's an abridged mapping: ------------------------------------------------------------------------------------------ <resultMap id="specialInquiryDtoResult" class="com.mcnichols.mserve.domain.dto.specials.SpecialInquiryDto" groupBy="specialInquiry.primaryKey" > <result property="specialInquiry.primaryKey" column="INQY_RECORDKEY"/> .... <result property="specialInquiryScenarioDtos" resultMap="Specials.SpecialInquiryScenarioDtoResult"/> </resultMap> <resultMap id="SpecialInquiryScenarioDtoResult" class="SpecialInquiryScenarioDto" groupBy="specialInquiryScenario.primaryKey"> <result property="specialInquiryScenario.primaryKey" column="INQYSCNR_RECORDKEY"/> ...... <result property="itemDtos" resultMap="Specials.SpecialItemDtoResult"/> </resultMap> <resultMap id="SpecialItemDtoResult" class="specialItemDto" groupBy="specialItem.primaryKey"> <result property="specialItem.primaryKey" column="SPCLITM_RECORDKEY"/> ..... <result property="specialItemCharacteristics" select="getSpecialItemCharacteristicValuesResult"/> <result property="optionDtos" select="getSpecialItemOptionDtoResults"/> <result property="supplierRFQs" select="getSupplierRFQResult"/> </resultMap> ------------------------------------------------------------------------------------------ The last 3 result elements above (now selects...) were nested resultMaps. Partial error output below.... com.ibatis.common.jdbc.exception.NestedSQLException: --- The error occurred in com/mcnichols/mserve/persistence/sqlmap/sql/Specials.xml. --- The error occurred while applying a result map. --- Check the Specials.SpecialItemDtoResult. --- Check the result mapping for the 'specialItemCharacteristics' property. --- Cause: com.ibatis.sqlmap.client.SqlMapException: Error getting nested result map values for 'itemDtos'. Cause: java.sql.SQLException: An undefined column name was detected. at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:188) I have vaildated the query columns to bean properties mapping and all looks fine. Regards, Tim "Jeff Butler" <[EMAIL PROTECTED]> 02/20/2007 01:01 PM Please respond to [email protected] To [email protected] cc Subject Re: Problem with several one to many relationships in breadth Tim - I'd take a very close look at whether it's really working in iBATIS 2.1.7. If it is, then it's just dumb luck. Take a look at the test case I added to the JIRA ticket: http://issues.apache.org/jira/browse/IBATIS-396 This test fails under both 2.1.7 and 2.3.0, but in different ways. Of course, I'd be happy to be proven wrong about this :) Jeff Butler On 2/20/07, [EMAIL PROTECTED] < [EMAIL PROTECTED] > wrote: Hi Jeff, Claudio and I are able to use 2.1.7 successfully - it stopped working in 2.2 versions. Bummer about there maybe not being a fix going forward - I've got a ton of queries that use this feature and of course I don't want to change any long-time stable code which I'd have to do to use a RowHandler. Pending a fix, I guess we'll have to go back to using nested selects. Regards, Tim "Jeff Butler" <[EMAIL PROTECTED] > 02/20/2007 12:11 PM Please respond to [email protected] To [email protected] cc Subject Re: Problem with several one to many relationships in breadth Interesting.... In my tests, it fails in BOTH versions (2.1.7 and 2.3.0), but in different ways. So something definitely changed. In my test, 2.3.0 is closer to correct than 2.1.7 is. But the important truth is that it does fail in both versions. For now, I think a row handler is the best way to go forward with this. I'll open a JIRA ticket with my test case, so everyone can see it. No guarantees about a fix, but at least it will be logged. Jeff Butler On 2/20/07, Jeff Butler < [EMAIL PROTECTED]> wrote: I''m surprised by this (obviously). I'll take a look. It probably has to do with the new support for multiple result sets. Jeff Butler On 2/20/07, Claudio Iacovozzi < [EMAIL PROTECTED] > wrote: I tested my given example with iBatis 2.1.7.597 and it works! Ibatis creates the expected bean: Agent1 --> List<Customer> {1:Customer1, 3:Customer2} --> List<Event> {1:housebreaking, 2:damage, 4:theft} Further tests with more depth (e.g. Custommer containing adresses) or breadth (Agent containing additional List of addresses) were also successful. So Tim ist right and something in iBatis must have been changed. We currently use version 2.2.0.638 / 2.3.0.677. => so what's the best solution if we still want to use the newest version? Should we implement our own row handler and how can that be done? Or is it possible that the behaviour of 2.1.7.597 will be restored? 2007/2/19, Jeff Butler < [EMAIL PROTECTED]>: That's a good solution - then you could deal with object identity yourself. Jeff Butler On 2/19/07, Larry Meadors < [EMAIL PROTECTED] > wrote: ..or you could use a rowhandler. Larry On 2/19/07, Jeff Butler < [EMAIL PROTECTED] > wrote: iBATIS groupBy support does not support two independant lists. This would be a very difficult problem to solve and would require us to deal with object identity - which we don't deal with now. So with groupBy, you can have one and only one list that is automatically populated. Other lists will need to be populated with a nested select. Jeff Butler On 2/19/07, [EMAIL PROTECTED] < [EMAIL PROTECTED] > wrote: Greetings Claudio, We too have this problem with the latest two releases. If you use version 2.1.7.597 you should have no problems - hopefully you have not implemented any new features like we had (and had to scramble to undo). Versions 2.2 and 2.3 will not handle what you are trying to do. I have been so swamped that I have been unable to generate a test case and submit a bug report. Maybe your example will suffice. Regards, Tim Hammar "Claudio Iacovozzi" < [EMAIL PROTECTED] > 02/19/2007 06:51 AM Please respond to [email protected] To [email protected] cc Subject Problem with several one to many relationships in breadth We have a problem filling objects with several '1 to many relationships' in breadth from one single SQL statement. On page 40 of the iBatis Data Mapper Developer Guide the 'groupBy' construct is described as follows: "Using this approach, you can solve any N+1 problem of any depth or breadth." This just seems to work for any depth but NOT for any *breadth*. Example: An insurance agent contains a list of customers as well as a list of insured events. <resultMap class="agent" id="agent" groupBy="id" > <result property="id" column="agentId" /> <result property="firstName" column="agent_firstname" /> <result property="lastName" column="agent_lastname" /> <result property="events" resultMap="event" /> <result property="customers" resultMap="customer" /> </resultMap> <resultMap class="event" id="event" groupBy="id" > <result property="id" column="eventId" /> <result property="title" column="title" /> </resultMap> <resultMap class="customer" id="customer" groupBy="id" > <result property="id" column="customerId" /> <result property="firstName" column="customer_firstname" /> <result property="lastName" column="customer_lastname" /> <result property="street" column="street" /> <result property="city" column="city" /> </resultMap> - The used SQL statement: <select id="getAgent" resultMap="agent" parameterClass="int"> SELECT a.agentId, a.FirstName as agent_firstname, a.LastName as agent_lastname, b.eventId, b.title, c.customerId, c.FirstName as customer_firstname, c.LastName as customer_lastname, c.street, c.city FROM insurance_agent a, agenteventlink ab, insured_event b, customer c WHERE a.agentId = ab.agentId AND ab.eventId = b.eventId AND b.customerId = c.customerId AND a.agentId = #id# ORDER BY a.agentId, c.customerId </select> - The returned result set: agentId,agent_firstname,agent_lastname,eventId,title,customerId,customer_firstname,customer_lastname {1,Agent1,Smith,1,housebreaking,1,Customer1,Johnson} {1,Agent1,Smith,4,theft,1,Customer1,Johnson} {1,Agent1,Smith,2,damage,3,Customer2,Miller} What we would like to achieve is getting a bean of type Agent, which contains two lists of all its customers and all its associated insured events: Agent1 --> List<Customer> {1:Customer1, 3:Customer2} --> List<Event> {1:housebreaking, 2:damage, 4:theft} But iBatis creates a bean like this: Agent1 --> List<Customer> {1: Customer1, 1:Customer1, 3: Customer2} --> List<Event> {1:housebreaking, 2:damage, 4:theft} We want to avoid several SQL statements to get the necessary data (one to get all events, and one to get all customers). Any solution ?
