ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sean Barbridge <sean.barbri...@gmail.com>
Subject Re: Recursive Select in SqlMap throws java.sql.SQLException: Exhausted Resultset
Date Sat, 28 Feb 2009 15:46:37 GMT
Hi dude,
I decided to load all nodes (records that satisfies the criteria) using a
single select statement and from the results, I loop each record in the list
and assign the parent, root and children searching from the list if the
related record is there.  If the related record is not in the result list, I
just re-invoke the sqlmap to get the related record.

Thanks for spending time replying to my queries.
Best Regards,

Sean

On Sat, Feb 28, 2009 at 2:29 AM, Ingmar Lötzsch <
iloetzsch@asci-systemhaus.de> wrote:

> Hello Sean,
>
> sorry for answering late. I was too busy today.
>
> >  Do you mean that Ibatis cannot handle Circular dependencies? If that's
> > the case then its not possible to call a single recursive statement from
> > a sqlmap?
>
> I'm not an expert for your case, because I'm not using the select
> attribute. The fact, that the order of the result elements influences
> the result of the execution, could mean, that iBATIS is not designed for
> your requirement and your method works accidentally. I would be cautious.
>
> It could be usefull, to walk through the iBATIS sources with the
> debugger to understand how the nested result maps work.
>
> > It' really weird coz when i interchange the order of <select
> > id="getMyObjectById" parameterClass="java.lang.String"
> > resultMap="result"> and <select id="getChildren"
> > parameterClass="java.lang.String" resultMap="result"> being
> > getMyObjectId first, I get the same error, but when I put getChildren
> > first, it works fine.  then when i add the line getting the root same
> > error is thrown.
>
> Maybe iBATIS works somehow like the following:
>
> The first row is mapped to instance A of MyObject. When creating the
> children of A the statement
>
> MyObject.getMyObjectById
>
> is called for the parent node of each child B_i. But this is the
> statement which was called before. That's why the existing instance of
> MyObject is used for the parent node, because iBATIS caches the results
> of the statements.
>
> What could happen, if you change the order of the result elements
> "parent" and "children":
>
> The first row is mapped to instance A. Now
>
> MyObject.getMyObjectById
>
> is called for the parent node P_1. The ResultSet is mapped to instance
> P_1. MyObject.getMyObjectById is called for the parent P_2 of P_1. P_i
> is the root. MyObject.getMyObjectById returns null, because the root
> node has no parent. Now
>
> getChildren
>
> is called. One of the children is P_{i-1}. A new instance for P_{i-1} is
> created, because the statement was called first time.
>
> This should confirm or negate an real iBATIS expert.
>
> > Currently I'm using the circular dependency for getting children and
> > parent only, since everytime i add the root, it throws an error, I
> > handled assigning the root in my java code.  The drawback is, it
> > requires lots of resources that affects the performance of the program
> > coz i have to loop each record in my list and invoke a method in sqlmap
> > to get the root, and i have to do it as well to every children in my
> > list, and to every children of the children .... so on.   This class is
> > modeled for an infinite tree.
>
> I dont't think, that there is a performance impact. The traversal over
> the tree takes O(n). iBATIS has to call many statements (read about the
> 1+n query problem in the manual) or check the cache. A secure and clear
> way would be to query for the root nodes and the children and after that
> join each node with his parent and the root.
>
> SELECT ID, ROOT_ID, PARENT_ID
> FROM MY_OBJECT
> WHERE PARENT_ID IS NULL
> AND ...
>
> <resultMap id="result" class="MyObject">
>   <result property="id" column="ID" />
>    <result property="children" column="ID" select="MyObject.getChildren"
> </resultMap>
>
> Faster could be to load all nodes with 1 (!) statement and build the
> tree after this programmatically.
>
> I always try to avoid tricks and traps in the data layer.
>
> Ingmar
>

Mime
View raw message