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 PLEASE HELP - self-joins to obtain a tree structure
Date Fri, 19 Oct 2007 07:14:59 GMT
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(BaseSqlMapClientDao.java:166)
         at 
com.pcmsgroup.v21.star2.persistence.productgroup.ProductGroupDAO.getGroup(ProductGroupDAO.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


Mime
View raw message