RE: Recursive Select in SqlMap throws java.sql.SQLException: Exhausted Resultset

[meindert]

How about using a ‘modified preorder tree traversal algorithm’  (not my
definition J) instead of the parented.

This link gives you the CRUD queries and explains it quite nicely

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

 

From: Sean Barbridge [mailto:sean.barbri...@gmail.com] 
Sent: 27 February 2009 05:21 AM
To: user-java@ibatis.apache.org
Subject: Re: Recursive Select in SqlMap throws java.sql.SQLException:
Exhausted Resultset

 

Thanks for your immediate reply.  Hope you could still answer my follow-up
queries after each  reply you made.

 

 

 

On Fri, Feb 27, 2009 at 12:52 AM, Ingmar Lötzsch
<iloetz...@asci-systemhaus.de> wrote:

This is the first time I used Ibatis in a project and I'm stucked on this
error. "Exhausted Resultset".
My problem started when i designed my model class like the following:

class MyObject {
  private String id;
  private String parentId;
  private String rootId;
  private MyObject root;
  private MyObject parent;
  private List<MyObject> children;

 ---  Getters and Setters Here for all attributes.--- }

 

Why do you need parentId and rootId? Can't you use root and parent instead?

 

Thanks for correcting that, actually i also realized the redundancy,
currently the design of the class dont have the parentId and the rootId

class MyObject {
  private String id;

  private MyObject root;
  private MyObject parent;
  private List<MyObject> children;
}

My SQL Map Looks like this:

<resultMap id="result" class="MyObject">
  <result property="id" column="ID" />
  <result property="parent" column="ID" select="MyObject.getChildren" />

 

property="parent"? Is this a typo?

Sorry for that, should be "children"

  <result property="parent" column="PARENT_ID"
select="MyObject.getMyObjectById" />
  <result property="root" column="ROOT_ID" select="MyObject.getMyObjectById"
/>
</resultMap>

<select id="getChildren" parameterClass="java.lang.String"
resultMap="result">
  SELECT ID, ROOT_ID, PARENT_ID FROM MY_OBJECT WHERE PARENT_ID = #value#
</select>

<select id="getMyObjectById" parameterClass="java.lang.String"
resultMap="result">
  SELECT ID, ROOT_ID, PARENT_ID FROM MY_OBJECT WHERE  ID = #value#
</select>

==============================================================

In the stacktrace it says

javax.servlet.ServletException: SqlMapClient operation; uncategorized
SQLException for SQL []; SQL state [null]; error code [17011];
.
.
.

java.sql.SQLException: Exhausted Resultset


When I remove this line "<result property="root" column="ROOT_ID"
select="MyObject.getMyObjectById" />" from the resultmap, the code works
fine.

 

This is already the solution. You must avoid the circular dependencies. Odd,
that your code works without removing

 

 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? 

 

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.



<result property="parent" column="PARENT_ID"
select="MyObject.getMyObjectById" />

I assume, you want to build a tree. After loading the tree you can iterate
recursivly over the child nodes and join them with the root node and the
parent node.

 

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.

 

 

By the way, the method i invoke from my DAO class to start the circular
dependency looks like the following:

 

<select id="getAllMyObject" parameterClass="MyObjectCriteria"
resultMap="result">
  SELECT ID, ROOT_ID, PARENT_ID FROM MY_OBJECT 

 WHERE  <Conditions here depending on the value of MyObjectCriteria
parameter>
</select>

 

This method returns a list that may contain one or more records.

 

 

Thanks

 

-s E a N-

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.0.237 / Virus Database: 270.11.3/1971 - Release Date: 02/25/09
06:40:00