ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mark McKenzie" <mark.mcken...@pcmsgroup.com>
Subject Re: PLEASE HELP - self-joins to obtain a tree structure
Date Fri, 19 Oct 2007 14:46:37 GMT
Thanks Clinton,

I agree, if the tree is arbitrarily sized then this would 
certainly be a very inefficient way of getting the data. 
For this particular object structure i don't expect to 
have to go up more than about 5 levels (parent objects) 
but i can't hard code it to 5. 

I have since got around this problem in Java by removing 
the self-join in the sqlmap and simply loading the parent 
object onto the actual object when a call is made to 
getParent() on the actual object (a form of lazy loading i 
suppose). It would be nice if IBatis could do this work 
for me but i guess its not imperative, i just wondered if 
i had discovered an issue that IBatis won't allow you to 
perform n-level self-joins? or as you suggest some config 
issue. I am using MSDE at present so i will investigate 
the cursor issue as you suggested first.

Thanks, Mark.

On Fri, 19 Oct 2007 08:15:26 -0600
  "Clinton Begin" <clinton.begin@gmail.com> wrote:
>Your JDBC driver may only allow a certain number of 
>result sets open at a
>time (per connection).  If your tree is a fixed depth, 
>you can see if you
>can try to configure it such that you have enough (in ms 
>sql server, make
>sure your select mode is "cursor").
>
>If your tree is arbitrarily sized or simply very deep, 
>then this whole
>approach may be very inefficient and you'd be better off 
>trying something
>else.
>
>You could try doing an actual SQL self join, or just load 
>the flat structure
>into an array of objects and use a more efficient tree 
>mapping algorithm to
>link it all up.  
>
>It's not a super easy or clean thing to do...but my first 
>question to you
>would be...do you actually need the whole tree?  And does 
>it need to be a
>tree?
>
>Regards,
>Clinton
>
>
>-----Original Message-----
>From: Mark McKenzie [mailto:mark.mckenzie@pcmsgroup.com] 
>Sent: October-19-07 1:15 AM
>To: user-java@ibatis.apache.org
>Subject: PLEASE HELP - self-joins to obtain a tree 
>structure
>
>Hi, I am trying to use the following IBatis SQL map to 
>retrieve a single object (ProductGroup) which will 
>contain 
>a parent object of the same type. The parent object 
>itself 
>could also potentially have a parent object ?.. and so on 
>and so on until we reach the top of the tree:
>
>  
>
><?xml version="1.0" encoding="UTF-8"?>
>
><!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 
>2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
>
>  
>
><sqlMap namespace="ProductGroup">
>
>  
>
>   <resultMap id="result" class="ProductGroup">
>
>     <result property="primaryKey"    column="PRGP_ID"/>
>
>     <result property="code" 
>         column="PRGP_CODE"/>
>
>     <result property="name" 
>         column="PRGP_NAME"/>
>
>     <result property="parentGroup" 
>  column="groupId=PARENT_ID" 
>select="ProductGroup.getById"/>    
>
>   </resultMap> 
>
>  
>
>   <!-- fetch a complex product group object containing 
>all parent groups -->
>
>   <select id="getById" resultMap="result">
>
>     SELECT prgp.*
>
>           ,pgsl.prgp_id AS PARENT_ID
>
>      FROM product_group prgp
>
>              INNER JOIN prodgp_str_link pgsl  ON 
>prgp.prgp_id = pgsl.prgp_child_id
>
>       WHERE prgp.prgp_id = #groupId#
>
>   </select>
>
>   
>
></sqlMap>
>
>  
>
>Unfortunately this does not appear to work when I use it 
>to retrieve a product group with more than 1 parent 
>object. Here is a partial stack trace of the problem I 
>get:
>
>  
>
>   <!--
>     com.pcmsgroup.v21.star2.inf.dao.exception.PersistenceException: 
>SqlMapClient operation; uncategorized SQLException for 
>SQL 
>[]; SQL state [null]; error code [0];   
>--- The error occurred in conf/ibatis/ProductGroup.xml.  
>--- The error occurred while applying a result map.  
>--- Check the ProductGroup.result.  
>--- Check the result mapping for the 'parentGroup' 
>property.  
>--- Cause: 
>com.ibatis.common.jdbc.exception.NestedSQLException:   
>--- The error occurred in conf/ibatis/ProductGroup.xml.  
>--- The error occurred while applying a result map.  
>--- Check the ProductGroup.result.  
>--- The error happened while setting a property on the 
>result object.  
>--- Cause: 
>com.microsoft.sqlserver.jdbc.SQLServerException: The 
>result set is closed.; nested exception is 
>com.ibatis.common.jdbc.exception.NestedSQLException:   
>--- The error occurred in conf/ibatis/ProductGroup.xml.  
>--- The error occurred while applying a result map.  
>--- Check the ProductGroup.result.  
>--- Check the result mapping for the 'parentGroup' 
>property.  
>--- Cause: 
>com.ibatis.common.jdbc.exception.NestedSQLException:   
>--- The error occurred in conf/ibatis/ProductGroup.xml.  
>--- The error occurred while applying a result map.  
>--- Check the ProductGroup.result.  
>--- The error happened while setting a property on the 
>result object.  
>--- Cause: 
>com.microsoft.sqlserver.jdbc.SQLServerException: The 
>result set is closed.
>         at 
>com.pcmsgroup.v21.star2.inf.dao.BaseSqlMapClientDao.queryForObject(BaseSqlMa
>pClientDao.java:166)
>         at 
>com.pcmsgroup.v21.star2.persistence.productgroup.ProductGroupDAO.getGroup(Pr
>oductGroupDAO.java:46)
>  
>Does anyone have any idea what the issue is and possibly 
>how to resolve it?
>  
>Many Thanks, Mark
>
>The information contained in this e-mail is intended only 
>for the person or
>entity to which it is addressed and may contain 
>confidential and/or
>privileged material.  If you are not the intended 
>recipient of this e-mail,
>the use of this information or any disclosure, copying or 
>distribution is
>prohibited and may be unlawful.  If you received this in 
>error, please
>contact the sender and delete the material from any 
>computer.  The views
>expressed in this e-mail may not necessarily be the views 
>of the PCMS Group
>plc and should not be taken as authority to carry out any 
>instruction
>contained. The PCMS Group reserves the right to monitor 
>and examine the
>content of all e-mails.
>
>The PCMS Group plc is a company registered in England and 
>Wales with company
>number 1459419 whose registered office is at PCMS House, 
>Torwood Close,
>Westwood Business Park, Coventry CV4 8HX, United Kingdom. 
>VAT No: GB
>705338743
>

Thanks

Mark

The information contained in this e-mail is intended only for the person or entity to which
it is addressed and may contain confidential and/or privileged material.  If you are not the
intended recipient of this e-mail, the use of this information or any disclosure, copying
or distribution is prohibited and may be unlawful.  If you received this in error, please
contact the sender and delete the material from any computer.  The views expressed in this
e-mail may not necessarily be the views of the PCMS Group plc and should not be taken as authority
to carry out any instruction contained. The PCMS Group reserves the right to monitor and examine
the content of all e-mails.

The PCMS Group plc is a company registered in England and Wales with company number 1459419
whose registered office is at PCMS House, Torwood Close, Westwood Business Park, Coventry
CV4 8HX, United Kingdom. VAT No: GB 705338743


Mime
View raw message