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
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
