[ http://issues.apache.org/jira/browse/IBATIS-162?page=all ] Clinton Begin closed IBATIS-162: --------------------------------
> Unexpected behavior in RowHandler method when doing a nested query causing > closed connection objects > ---------------------------------------------------------------------------------------------------- > > Key: IBATIS-162 > URL: http://issues.apache.org/jira/browse/IBATIS-162 > Project: iBatis for Java > Type: Bug > Components: SQL Maps > Versions: 2.1.0 > Reporter: Jayson Minard > Assignee: Clinton Begin > > as explained in a message between our engineering staff, when you perform a > nested query in iBatis during a handleRow() method, the closure of the > connection object terminates the outer query. It only works in the default > iBatis connection pool due to what appears to be dangerous behavior. > Misunderstanding, or real bug? See full explaination below: > --- snip --- > If you just want the summary, here it is: Don't use RowHandler's with your > iBATIS select queries. If you want to know why, feel free to read on. > > When using iBATIS, you have the option of providing a RowHandler to the query > selection method, which will apply the same code to every row returned in the > result set. To describe it better, here's a sample of how the code normally > works and then how it might work with a row handler. > > NO ROW HANDLER: > 1. Submit select query > 2. Get a connection from the pool, if you have not explicitly started a > transaction > 3. A prepared statement and result set are created implicitly > 4. Iterate through result set > a) Place each object produced into a list > 5. Close the previously created prepared statement and result set > 6. Return the connection to the pool, if you have not explicitly started a > transaction > 7. Return the list to the calling method > 8. Iterate through the resulting list > a) Apply processing to each object > > > WITH ROW HANDLER > 1. Submit select query > 2. Get a connection from the pool and start an implicit transaction, if > you have not explicitly started a transaction > 3. A prepared statement and result set are created implicitly > 4. Iterate through result set > a) Apply processing to each object as the result set is read > -- The handler accepts the result object and the result list. You > can do any processing you wish and add (or not add) anything you want to the > List > -- This may include another database query > 5. Close the previously created prepared statement and result set > 6. Commit the implicit transaction and return the connection to the pool, > if you have not explicitly started a transaction > 7. Return the list to the calling method > 8. Use or ignore the list, depending on the process > > > Alot of people have varying opinions as to which is the better methodology. > Some people might be prone to use RowHandler's because they give more of an > OOP approach. This is all fine, diversity is a good thing. Now here's the > problem with RowHandler approach used in iBATIS: > > Let's say you haven't explicitly started a transaction and your > RowHandler.handleRow() method performs a query of its own. Here's what will > happen with the first record: > > 1. iBATIS calls next() on the result set > 2. The result object is passed to the rowHandler > 3. The row handler submits a query > 4. An implicit transaction is started > 5. The query is processed > 6. The implicit transaction is commited and the connection returned to the > pool > 7. iBATIS calls next() on the result set, which is no longer valid because > the connection has been return to the pool > > > You cannot continue to use a result set or statement belonging to a > connection that has been returned to the pool, this is bad form. In the case > of the Abebooks connection pool, an exception will actually be thrown, > because the connection is treated as having been closed by the connection > pool data source. > > So why didn't this issue make itself known when we were using the connection > pool? The iBATIS connection pool behaves a bit differently, as they just > have a flag external to the connection indicating that it is returned to the > pool. This prevents any new statements from being created against the > connection. However, since close() is not called, the result set doesn't > know that the connection is invalid. For this reason, the code was able to > sort of get away with this type of behaviour, though it did provide a risk of > unusial behaviour. > > So, in summary, the moral of the story is to avoid using RowHandlers with > iBATIS. I'll add something to our confluence pages on this. > -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
