[jira] Commented: (IBATIS-466) Incorrect behaviour when combining RowHandler with groupBy
[
https://issues.apache.org/jira/browse/IBATIS-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12700236#action_12700236
]
Kai Grabfelder commented on IBATIS-466:
---
here is what I would do:
- make sure the query returns the data sorted correctly (e.g. sort by the
columns used for grouping). In the above example the sorting should look like
ORDER BY person_id asc
- use a rowhandler, but no iBatis groupBy functionality
- do the groupBy functionality in your rowhandler, just you would do in plain
old JDBC (continue to add albumbs to a person as long as the person_id does not
change)
> Incorrect behaviour when combining RowHandler with groupBy
> --
>
> Key: IBATIS-466
> URL: https://issues.apache.org/jira/browse/IBATIS-466
> Project: iBatis for Java
> Issue Type: Bug
> Components: SQL Maps
>Affects Versions: 2.3.0
>Reporter: William Shields
>
> Assuming:
> CREATE TABLE Person {
> person_id NUMBER PRIMARY KEY,
> name VARCHAR2(100)
> )
> CREATE TABLE Album (
> album_id NUMBER PRIMARY KEY,
> owner NUMBER REFERENCES (Person.person_id),
> name VARCHAR2(100)
> )
> and
> public class Person {
> private long personId;
> private String name;
> private List albums;
> ...
> }
> public class Album {
> private long albumId;
> private long owner;
> private String name;
> ...
> }
> with query:
>
>
>
>
>
>
>
>
>
>
>
> SELECT person_id, p.name person_name, album_id, a.name album_name
> FROM Person p, Album a
> WHERE person_id = owner
>
> with data:
> Person:
> 1 John
> 2 Mary
> Album:
> 10 1 "Bat Out Of Hell"
> 11 2 "The Wall"
> 12 2 "Eyes Open"
> 12 2 "White Ladder"
> Now, queryForList() works correctly. Two Person objects are returned. The
> first (John) has 1 album, Mary has 3. If instead you do:
> queryWithRowHandler("selectAlbums", new RowHandler() {
> public void handleRow(Object valueObject) {
> Person p = (Person)valueObject;
> System.out.println(p.getName() + " has " + p.getAlbums().size() + "
> albums");
> }
> }
> It displays 1 for each of the two rows. The RowHandler is being called on
> the first row and then it continues to add to the sub-list. This is a real
> problem if you want to process the fully loaded value object. I've come up
> with this workaround:
> public interface TypedRowHandler {
> void handle(T t);
> }
> public class CleverRowHandler implements RowHandler {
> TypedRowHandler handler;
> private T last;
> public CleverRowHandler(TypedRowHandler handler) {
> this.handler = handler;
> }
> public void handleRow(Object valueObject) {
> flush();
> last = (T)valueObject;
> }
> public void flush() {
> if (last != null) {
> handler.handler(last);
> }
> last = null;
> }
> }
> with DAO code:
> public void processPersons() {
> CleverRowHandler rh = new CleverRowHandler(new
> TypedRowHandler() {
> public void handle(Person p) {
> // do whatever
> }
> };
> try {
> getSqlMapClientTemplate().queryWithRowHandler("selectPersons", rh);
> } finally {
> rh.flush();
> }
> }
> but that's rather messy. I believe that the current behaviour is a bug and
> introduces potential threading issues (in my case my row handler creates a
> Runnable and submits it to a ExecutorService, which is a problem if ibatis is
> still adding objects to the sub-list).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (IBATIS-466) Incorrect behaviour when combining RowHandler with groupBy
[
https://issues.apache.org/jira/browse/IBATIS-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12700095#action_12700095
]
Giovanni Cuccu commented on IBATIS-466:
---
I was using the two features together. My problem was the following.
I have a query mapped with a resultmap with three levels A has a list of B and
B has a list of C.
The query returns a lot fo rows that are serialized to xml.
the resulting xml is the soapbody of a webservice response.
when the query returns a lot of rows the memory usage goes very high and the
OutOfMemory errors arise.
I ended in implementing a sort of streaming webservice where the response is
pushed (via servlet response) to the client as the rows came form the db.
I need the two feature together because the broup by is needed to return the
correct xml structure and the rowhandler is used in order to reduce memory
usage.
An implementation or an hint that allows me to use the group by and the row
handler assuming that i'm responsible for the order of the row would be very
nice.
> Incorrect behaviour when combining RowHandler with groupBy
> --
>
> Key: IBATIS-466
> URL: https://issues.apache.org/jira/browse/IBATIS-466
> Project: iBatis for Java
> Issue Type: Bug
> Components: SQL Maps
>Affects Versions: 2.3.0
>Reporter: William Shields
>
> Assuming:
> CREATE TABLE Person {
> person_id NUMBER PRIMARY KEY,
> name VARCHAR2(100)
> )
> CREATE TABLE Album (
> album_id NUMBER PRIMARY KEY,
> owner NUMBER REFERENCES (Person.person_id),
> name VARCHAR2(100)
> )
> and
> public class Person {
> private long personId;
> private String name;
> private List albums;
> ...
> }
> public class Album {
> private long albumId;
> private long owner;
> private String name;
> ...
> }
> with query:
>
>
>
>
>
>
>
>
>
>
>
> SELECT person_id, p.name person_name, album_id, a.name album_name
> FROM Person p, Album a
> WHERE person_id = owner
>
> with data:
> Person:
> 1 John
> 2 Mary
> Album:
> 10 1 "Bat Out Of Hell"
> 11 2 "The Wall"
> 12 2 "Eyes Open"
> 12 2 "White Ladder"
> Now, queryForList() works correctly. Two Person objects are returned. The
> first (John) has 1 album, Mary has 3. If instead you do:
> queryWithRowHandler("selectAlbums", new RowHandler() {
> public void handleRow(Object valueObject) {
> Person p = (Person)valueObject;
> System.out.println(p.getName() + " has " + p.getAlbums().size() + "
> albums");
> }
> }
> It displays 1 for each of the two rows. The RowHandler is being called on
> the first row and then it continues to add to the sub-list. This is a real
> problem if you want to process the fully loaded value object. I've come up
> with this workaround:
> public interface TypedRowHandler {
> void handle(T t);
> }
> public class CleverRowHandler implements RowHandler {
> TypedRowHandler handler;
> private T last;
> public CleverRowHandler(TypedRowHandler handler) {
> this.handler = handler;
> }
> public void handleRow(Object valueObject) {
> flush();
> last = (T)valueObject;
> }
> public void flush() {
> if (last != null) {
> handler.handler(last);
> }
> last = null;
> }
> }
> with DAO code:
> public void processPersons() {
> CleverRowHandler rh = new CleverRowHandler(new
> TypedRowHandler() {
> public void handle(Person p) {
> // do whatever
> }
> };
> try {
> getSqlMapClientTemplate().queryWithRowHandler("selectPersons", rh);
> } finally {
> rh.flush();
> }
> }
> but that's rather messy. I believe that the current behaviour is a bug and
> introduces potential threading issues (in my case my row handler creates a
> Runnable and submits it to a ExecutorService, which is a problem if ibatis is
> still adding objects to the sub-list).
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (IBATIS-466) Incorrect behaviour when combining RowHandler with groupBy
[
https://issues.apache.org/jira/browse/IBATIS-466?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12567739#action_12567739
]
Etienne Dodat commented on IBATIS-466:
--
Hi,
I have tested combining a rowHandler and a "groupBy" clause and I have the same
behaviour as William :
To be more detailed, I debbuged the iBatis source code and here is the
behaviour I observed :
Assuming William usecase :
Person:
1 John
2 Mary
Album:
10 1 "Bat Out Of Hell"
11 2 "The Wall"
12 2 "Eyes Open"
12 2 "White Ladder"
The result set of the SQL select contains 4 lines :
1 John 10 "Bat Out Of Hell"
2 Mary 11 "The Wall"
2 Mary 12 "Eyes Open"
2 Mary 13 "White Ladder"
- If we run the query without RowHandler, 2 Person objects are created with 1
album for John and 3 albums for Mary. CORRECT
- If we run the query with a RowHandler but without "groupBy" clause, the
rowHandler is called 4 times for each line with incomplete objects : John with
1 album once and Mary with 1 album 3 times. CORRECT.
When I combine a RowHandler with a "groupBy" clause, the Row Handler is called
2 times : John with 1 album and Mary with 1 album. NOT CORRECT. The expected
behaviour would be to have the fully loaded objects.
The explanation can be found in the iBatis implementation :
The method "handleResults()" in SqlExecutor.java handles the result set :
private void handleResults(RequestScope request, ResultSet rs, int
skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {
try {
request.setResultSet(rs);
ResultMap resultMap = request.getResultMap();
if (resultMap != null) {
// Skip Results
if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {
if (skipResults > 0) {
rs.absolute(skipResults);
}
} else {
for (int i = 0; i < skipResults; i++) {
if (!rs.next()) {
return;
}
}
}
// Get Results
int resultsFetched = 0;
while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched
< maxResults) && rs.next()) {
Object[] columnValues = resultMap.resolveSubMap(request,
rs).getResults(request, rs);
callback.handleResultObject(request, columnValues, rs);
resultsFetched++;
}
}
} finally {
request.setResultSet(null);
}
}
The "while" instruction loops on the 4 lines of the result set and calls the
method "handleResultObject()" in RowHandlerCallback.java :
public void handleResultObject(RequestScope request, Object[] results,
ResultSet rs) throws SQLException {
Object object;
request.setCurrentNestedKey(null);
object = resultMap.resolveSubMap(request,
rs).setResultObjectValues(request, resultObject, results);
if (object != ResultMap.NO_VALUE) {
// XML Only special processing. (converts elements to string for easy
insertion).
int stackDepth = request.getSession().getRequestStackDepth();
if (stackDepth == 1) {
Class targetType = request.getResultMap().getResultClass();
if (XmlTypeMarker.class.isAssignableFrom(targetType)
&& object instanceof Document) {
object = documentToString((Document) object);
}
}
rowHandler.handleRow(object);
}
}
We can see at the end of the method that our RowHandler is called only if the
"object" returned by setResultObjectValues() is not "ResultMap.NO_VALUE".
Here is the method setResultObjectValues() in BasicResultMap.java :
public Object setResultObjectValues(RequestScope request, Object
resultObject, Object[] values) {
String ukey = (String)getUniqueKey(request.getCurrentNestedKey(), values);
Map uniqueKeys = request.getUniqueKeys(this);
request.setCurrentNestedKey(ukey);
if (uniqueKeys != null && uniqueKeys.containsKey(ukey)) {
// Unique key is already known, so get the existing result object and
process additional results.
resultObject = uniqueKeys.get(ukey);
applyNestedResultMap(request, resultObject, values);
resultObject = NO_VALUE;
} else if (ukey == null || uniqueKeys == null ||
!uniqueKeys.containsKey(ukey)) {
// Unique key is NOT known, so create a new result object and then
process additional results.
resultObject = dataExchange.setData(request, this, resultObject, values);
// Lazy init key set, only if we're grouped by something (i.e. ukey !=
null)
if (ukey != null) {
if (uniqueKeys == null) {
uniqueKeys = new HashMap();
request.setUniqueKeys(this, uniqueKeys);
}
uniqueKeys.put(ukey, resultObject);
}
applyNestedResultMap(request, resultObject, values);
} else {
// Otherwise, we don't care about these results.
resultObject = NO_VALUE;
}
return resultObject;
}
Now, let's see what happens in our example.
The re
