ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ingmar Lötzsch <iloetz...@asci-systemhaus.de>
Subject Re: Recursive Select in SqlMap throws java.sql.SQLException: Exhausted Resultset
Date Fri, 27 Feb 2009 18:29:32 GMT
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